Is it possible to load a VBA code from a file located in a network folder?

Question

I have a .vba file located in a network folder let's call it helloworld.vba inside this file I have a sub function HelloFromTheOtherSide().

I'm trying to programmatically load this file in a way that I can execute the functions HelloFromTheOtherSide() in my excel instance.

Imagine that this is the content for simplicity:

Sub HelloFromTheOtherSide()
MsgBox ("hello there!")
End Sub

I've tried to follow these instructions on how to dynamically add and run a VBA macro from Visual Basic but that is not what I'm trying to do here as I want to be able to run calling HelloFromTheOtherSide().

I'm trying to understand if its possible to load a .vba from a folder to an excel instance programmatically in a way I could run the functions in my instance.

That would be quite usefull if possible as I would be able to store all my vba code in a single folder and load it from there everytime that I want to run something specific.

Answer 1

You can use both VBA or VBScript to call a Function or a Sub from another Workbook:

VBA

Sub callExternalFunction()
    Dim xlApp As Object
    Dim xlBook As Object

    'Define Excel App
    Set xlApp = CreateObject("Excel.Application")
    'Open Workbook
    Set xlBook = xlApp.Workbooks.Open("\\server\my\path\to\My library.xlsm", 0, True)

    'Call my Sub, with eventual parameters (if you don't have any, just call the Routine)
    xlApp.Run "Myfunction", "param_1", "param_2"

    'Quit and clean
    xlApp.Quit
    Set xlBook = Nothing
    Set xlApp = Nothing
End Sub

VBScript

Sub ExcelMacroExample() 
    Dim xlApp
    Dim xlBook

    'Define Excel App
    Set xlApp = CreateObject("Excel.Application")
    'Open Workbook
    Set xlBook = xlApp.Workbooks.Open("R:\my\network\path\My Workbook.xlsm", 0, True) 

    'Call my Sub, with eventual parameters (if you don't have any, just call the Routine)
    xlApp.Run "myRoutine"

    'Quit and clean
    xlApp.Quit

    Set xlBook = Nothing 
    Set xlApp = Nothing 

End Sub

Edit

You can omit the Excel App initialization and directly call the Macro you need with this command (thanks to @TimWilliams):

Application.Run "'MyBook.xls'!MyMacroName"

Note: As you can see they are pretty similar. Both codes are tested and working on my Excel.

Hope this helps.