Calling User-Defined Functions from a macro

[Andrew Schroder]

Hello, is there a way to call a user-defined function from an Excel macro ? I
regularly use special propriety functions (and I dont have access to the
code). These work fine in Excel, but they dont seem to work when included in
a macro (while the built-in functions in Excel work fine).
Thanks for any help you can provide !
Andrew Schroder

 

[Joel]

No reason why you can't. Just answered the same question in another posting
that started a couple of day ago. The problem is with cell references if
they are selecting the correct worksheet. You need to perform a worksheet
select before calling these functions if aren't referencig the sheet in the
function. Here are two examples of UDF functions that can be called from a
worksheet or another macro.


Example 1
Function MyFunction(Target as Range)
MyFunction = Target.Value
End Function

Sub Test()
Set Target = Sheets("Sheet1").Range("A1")
Data = Myfunction(Target)

End Sub

Example 2
Function MyFunction()
MyFunction = Range("A1")
End Function

Sub Test()
Sheets("Sheet1").Activate
Data = Myfunction()

End Sub

Sheets("Sheet1").activate

 

[Norman Jones]

Hi Andrew,

If the custom function resides in an add-in,
try using the Application's Run method.

So, for example, to use a function AAA in
an addin named theAddin.xla, try something
like:

'==========>>
Public Sub Tester()
Dim sStr As String
Dim Res As Variant

sStr = "theAddin.xla"

Res = Application.Run("'" _
& sStr _
& "'!AAA")
MsgBox Res
End Sub
'==========>>

 

[Bob Phillips]

Hi Andrew,

If the custom function resides in an add-in,
try using the Application's Run method.

or set a reference to that addin project and call the function directly.