How to call a function from an excel addin with arguments

[CodeNinja]

Hello all,
I am trying to call a function from an xlam file I created. I would like to keep all the code in 1 place so I do not have duplicate code in a workbook as well. Ideally this should work for anyone that has the add in [not just my computer]. The function I want to store is 'Excel Groomer.xlam'!PowerTools.isMemberWorksheetType [PowerTools is the module]. It requires 3 arguments (wsGroom as worksheet, wsNames as worksheet, and headerRow as long).

I have been trying to use application.run, but am not sure how to pass arguments... I have the following tools in the xlam:

Public Function isMemberWorksheetType(wsgroom As Worksheet, wsnames As Worksheet, headerRow As Long) As Boolean
' test for (2 different types of: Full Name or Last Name) OR (member Id)  If either of those conditions exist, it is Member.
Dim rng As Range
Dim cl As Range
Dim count As Integer
Dim headerRng As Range
isMemberWorksheetType = False
Set headerRng = wsgroom.Range(headerRow & ":" & headerRow)
If countOccurrancesInRange(gatherUsedRangeFromHeaders(wsnames.Range("FullName")), headerRng) > 1 Then
    isMemberWorksheetType = True
End If
If countOccurrancesInRange(gatherUsedRangeFromHeaders(wsnames.Range("LastName")), headerRng) > 1 Then
    isMemberWorksheetType = True
End If
If countOccurrancesInRange(gatherUsedRangeFromHeaders(wsnames.Range("Member_ID")), headerRng) > 0 Then
    isMemberWorksheetType = True
End If


End Function
Public Function countOccurrancesInRange(rangeToSearch As Range, rangeToCheck As Range) As Integer
Dim cl As Range
Dim rng As Range
countOccurrancesInRange = 0
For Each cl In rangeToCheck
    Set rng = rangeToSearch.Find(cl.Value, , , xlWhole)
    If Not rng Is Nothing Then
        countOccurrancesInRange = countOccurrancesInRange + 1
    End If
Next cl
End Function


Public Sub test()
MsgBox ("hi")
End Sub

I can easily run the sub test using :

Sub test()
Application.Run ("'Excel Groomer.xlam'!PowerTools.test")
End Sub

Because I can run test, I believe I am on the right track, I just don't know how to pass the arguments. Any suggestions?

Thanks,

CN.

 

[CodeNinja]

Figured this out... I can use:

Public Function callMacroFunction(wsGroom As Worksheet, wsNames As Worksheet, headerRow As Long) As Boolean
    callMacroFunction = Application.Run("'Excel Groomer.xlam'!PowerTools.isMemberWorksheetType", wsGroom, wsNames, headerRow)
    
End Function

Thanks.

CN

 

[pizzaboy]

You can create a Reference to the Add-In via the VBA Editor (Tools > References (Browse if in another location than the default)).