Excel VBA - Calling a Sub int a Workbook from my Personal Macro Workbook

Question

I maintain a personal workbook of Subs that I use via a Custom toolbar. I am working on a project in which there is a sub that I want to call regularly from the toolbar. But I save the workbook under development several times a day to maintain a go-back capability. Since the workbook name changes each time, the toolbar is tied to the Sub in an out of date workbook.

I want to keep a Sub in my personal macro workbook that calls a sub in the currently open workbook - any ideas how? I made it public but that didn't do it. I tried transferring the code across but it refers to variables (even global) that aren't accessible to the personal macro workbook.

does this answer your question: stackoverflow.com/questions/28741828/… — MGP
The macro in your personal workbook must refer to the workbook it's supposed to work on by name. Change the name to ActiveWorkbook and you wouldn't need a second macro. However, referring to the ActiveWorkbook holds its own dangers. Consider building checks into your macro that prevent it from changing a wrong workbook. For example, if the name change only involves a date/time change, look for the core name in the macro. Post it here for modification suggestions. — Variatus

FaneDuru FaneDuru · Accepted Answer · 2020-08-17T06:49:11

Try the next approach, plese:

Create the macro to be called in the workbook which will be the active one:

Public Sub myMacroOtherWorkbook()
   MsgBox "Hello from other workbook!"
 End Sub

Call it from your Personal Macro Workbook, in this way:

Sub testCallMacroOtherWb()
  Application.Run ("'" & ActiveWorkbook.Name & "'!myMacroOtherWorkbook")
End Sub

Version 2, in case you need to pass a parameter:

Transform the above Sub in:

Public Sub myMacroOtherWorkbook(Optional strCall As String)
   MsgBox "I will bring a " & IIf(strCall = "", "", strCall) & " horse."
End Sub

Call it in this way:

Sub testCallMacroOtherWb()
  Application.Run "'" & ActiveWorkbook.Name & "'!myMacroOtherWorkbook", "black"
End Sub

Note: The first call will still work, but the horse will be of no color... :)

Excel VBA - Calling a Sub int a Workbook from my Personal Macro Workbook

1 Answers