Consulting

Results 1 to 7 of 7

Thread: Solved: Call a macro stored in Personal.xls

  1. #1
    VBAX Mentor Marcster's Avatar
    Joined
    Jun 2005
    Posts
    434
    Location

    Solved: Call a macro stored in Personal.xls

    Hi people,

    How do I call a macro via VBA which is held in Personal.xls?

    Tried the usual:
    Call ThisIsInPersonal
    but errors with:
    Sub or Function not defined.

    Thanks,

    Marcster.

  2. #2
    VBAX Mentor Marcster's Avatar
    Joined
    Jun 2005
    Posts
    434
    Location
    I've sorted it now.

    Run ("PERSONAL.XLS!ThisIsInPersonal")

    Thanks,

    Marcster.

  3. #3
    Hey, Marcster, I don't quite understand what you are trying to do. Referring to a macro should result in an automatic search in all your references including Personal.xls. You shouldn't have to qualify it in any way. That's why Personal is such a handy place to put common macros.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by Cyberdude
    Hey, Marcster, I don't quite understand what you are trying to do. Referring to a macro should result in an automatic search in all your references including Personal.xls. You shouldn't have to qualify it in any way. That's why Personal is such a handy place to put common macros.
    That is not correct. You either have to use Run to call a macro in another workbook, or you need to set a reference to that project.

  5. #5
    VBAX Mentor Marcster's Avatar
    Joined
    Jun 2005
    Posts
    434
    Location
    Hi Guys,

    Sorry ,

    I should of posted:
    [VBA] Run "PERSONAL.XLS!ThisIsInPersonal", msg[/VBA]
    As in (in workbook
    [VBA]
    Sub testDisplayMessageInPersonal()
    Dim msg As String
    msg = "Heres some text"
    Run "PERSONAL.XLS!ThisIsInPersonal", msg
    End Sub
    [/VBA]
    In PERSONAL.XLS:
    [VBA]
    Sub ThisIsInPersonal(strMsg As String)
    MsgBox strMsg
    End Sub
    [/VBA]

    Marcster.

  6. #6
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Fwiw, I generally recommend staying away from setting a reference to another workbook. I think it's bad habit and it can create an error-prone environment which can be hassling to the user(s).

  7. #7
    xld and maracster, sorry for the bum recommendation. I've had Personal referenced to all my workbooks for so long that I forgot I did it. I was thinking that you get that reference for free, so to speak. I guess it isn't automatic after all.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •