How to call a function from an excel addin with arguments

CodeNinja

Well-known Member
Joined
Feb 18, 2013
Messages
644
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:

Code:
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 :
Code:
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.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Figured this out... I can use:

Code:
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
 
Upvote 0
You can create a Reference to the Add-In via the VBA Editor (Tools > References (Browse if in another location than the default)).
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top