Call a function from an Add-in to a Macro?

[frabulator]

I have an add-in (example of 'ADDIN') that is installed in Excel. I have a custom macro that accesses the add-in through VBA to execute subroutines like so (example):

Dim oApp As Object
Set oApp = Application
Dim Var As Integer
Var = 1
Call oApp.Run("ADDIN.xlam!SUBNAME", Var)

My problem is that I want to run a function from the macro that is contained inside of ADDIN. The function is also accessible through the cell functions once the ADDIN is installed.

My thought was to do something like this, calling it directly, but this did not work saying there needs to be an object.

Dim Val As Integer
Dim oApp As Object
Set oApp = Application
Dim Var As Integer
Var = 1

Val = Call oApp.Run("ADDIN.xlam!FUNCTIONNAME", Var)

Then my thought was to just call the function through the WorksheetFunction command, but then I realized that the WorksheetFunction command only applies to functions that are default by Excel, not custom.

Dim Val As Integer
Dim oApp As Object
Set oApp = Application
Dim Var As Integer
Var = 1

Val = WorksheetFunction.FUNCTIONNAME(Var)

I know of a workaround where I can custom code in a subroutine into the ADDIN to change a value of a cell to the function return value, read that value from the MACRO and then change the value back before the screen is updated, but that seems janky and unnecessary.

Does anyone know how I can achieve this more... elegantly?

 

[Domenic]

Dim Val As Integer
Dim oApp As Object
Set oApp = Application
Dim Var As Integer
Var = 1

Val = Call oApp.Run("ADDIN.xlam!FUNCTIONNAME", Var)

In the above code, first get rid of the keyword Call. Then, if the actual name of your add-in is different than the one you posted, and if the name contains one or more spaces, you should add single quotes around the filename.

Val = oApp.Run("'ADDIN.xlam'!FUNCTIONNAME", Var)

Hope this helps!

 

[frabulator]

In the above code, first get rid of the keyword Call. Then, if the actual name of your add-in is different than the one you posted, and if the name contains one or more spaces, you should add single quotes around the filename.

Val = oApp.Run("'ADDIN.xlam'!FUNCTIONNAME", Var)

Hope this helps!

Perfect! Thank you!

 

[Domenic]

You're very welcome, cheers!