Calling Subs from an Add-In

Question

I have an Excel workbook with some basic opening code in Workbook_Open and some code in xSheet i.e.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Run s00Config_Worksheet_SelectionChange(Target)

End Sub

where sub s00Config_Worksheet_SelectionChange exists in an Excel AddIn which is loaded (and visible for the moment).

But when I execute the program in the first sheet all I get is Sub or Function not defined.

In the AddIn the sub has been defined as

Public Sub s00Config_Worksheet_SelectionChange(ByVal Target As Range)

Questions are:

1. Am I thinking this the right way, and
2. How do I get my excel sheet to execute subs in the Excel AddIn (XLAm), there are a lot of them

Answer 1

Run is provided by Excel, and VBA sees it as just a normal subroutine. So when you say Run s00Config_Worksheet_SelectionChange(Target), VBA tries to evaluate the function immediately and then pass it to Run as its first argument. To prevent VBA from interfering like this, Run requires that you pass it the target subroutine name as a string, and then proceed with the rest of the target's argument list:

Run "s00Config_Worksheet_SelectionChange", Target

The same syntax applies if you want to get a value back from whatever function you call:

v = Run("s00Config_Worksheet_SelectionChange", Target)

Answer 2

1. Make sure you reference the addin in your VBA project (Tools - References - Browse).

2. Remove Run and the parentheses:

   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
       s00Config_Worksheet_SelectionChange Target
   End Sub