Calling Subs stored in an AddIn file

D

Dr.K

Board Regular
Joined
May 16, 2007
Messages
83
Based on advice from Mr.Tusharm, I am breaking my macro up into separate files: an XLS file for storing local user data, and an XLA file for storing the macro code.

http://www.mrexcel.com/board2/viewtopic.php?t=275429

However, I'm having problems running the macros from the AddIn...

I was under the impression that Subs stored in an XLA module were availible to EVERYTHING. Right now I have buttons on the data XLS that calls the macros in the XLA... and I'm getting "Sub Not Defined" errors. GRRRRR.

Am I suposed to inlcude the AddIn workbook name when I call the Sub?
IE, instead of:

RequestGenerator

RequestForm_Macros.xla!RequestGenerator


Any advice is appreciated.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Silly question: have you installed the add-in via Tools > Add-ins, or are you opening it via code in your XLS file?
 
Upvote 0
Its not installed, but its open and running the in back ground (ie, its hidden becuase the "Is AddIn" flag is set to true.

Would installing the AddIn fix this?

The rason I havn't installed it is becuase its still under development.
Seems like a pain to install/uninstall each time you need to edit it. And once I'm ready to distribute it, how do I "extract" the installed version that I've edited the code in?
 
Upvote 0
Yes, you are on the right track. Suppose your add-in has a subroutine
Code: 
Public Sub CallFromExternalProcess(x)
    MsgBox "CallFromExternalProcess: " & x
    End Sub
Then, to call it when a button in some other workbook is clicked, you would use
Code: 
Private Sub CommandButton1_Click()
    Application.Run _
        "'c:\documents and settings\{windows user id}\application data\microsoft\addins\book1.xla'!CallFromExternalProcess", _
        1
    End Sub
or if you establish a reference (Tools | References...) to the add-in, you would use
Code: 
Private Sub CommandButton2_Click()
    CallFromExternalProcess 2
    End Sub
When you establish a reference to another file (add-in in this case), VBA will treat all the subroutines and functions in the other file as though they were in your own project (within reason).

For a MSDN article authored by me that refers to the above techniques in a slightly different context see
How to use a class (object) from outside of the VBA project in which it is declared
http://support.microsoft.com/default.aspx/kb/555159

Finally, and specific to your case, the best approach might be for the add-in to create the buttons on a dynamic basis. In an add-in I am currently completing, the code adds buttons to a data entry worksheet that it creates on a temporary basis. The code, in part, reads
Code: 
Sub addButtons(whatWS As Worksheet)
    Dim aBtn As Button
    Set aBtn = whatWS.Buttons.Add(136.5, 0, 38, 16.5)
    With aBtn
    .OnAction = "'" & ThisWorkbook.Name & "'!FindData"
    .Characters.Text = "Find"
        End With

    Set aBtn = whatWS.Buttons.Add(136.5, 25, 38, 16.5)
    With aBtn
    .OnAction = "'" & ThisWorkbook.Name & "'!UpdateData"
    .Characters.Text = "Update"
        End With
You are not restricted to creating buttons at runtime. You can also create menus. For John Walkenbach's table-driven approach (I use a similar though not identical method) see http://www.j-walk.com/ss/excel/tips/tip53.htm
 
Upvote 0
Thank you for your thoughtful response.

One more question (sorry if its a dumb questions):
If I add the reference, then do all of the end users have to add that reference before the program will run? Or does installing the AddIn automatically create the required reference?

If each user has to open the VBA editor and add the reference, it will somewhat defeat the purpose of having a self-installing macro.
 
Upvote 0
Ok, I know understand how to execute subs with Run... using the method where you spell out the path name is essentially late binding. That makes a lot of sense.
Basically, you need to use Run to execute subs in different workbooks. If the XLA is open, you need to reference the name of the workbook first. If the XLA is installed, then you don't need to reference anything.

Finally, and specific to your case, the best approach might be for the add-in to create the buttons on a dynamic basis.
Click to expand...

Thats an interestng point, and I've been tinkering with that quite a bit today... however, one thing that frustrates me is that you can't customize the color/appearence of the button objects created that way!

Unlike an activeX button, which has a property page, or a shape button, which you can use the bucket on, I can't figure out how to change the color of those buttons! I even tried programatic solutions in the immediate window to no avail, the buttons stayed grey.

Oh well, maybe I'll just dynamically create rectangle shapes and assign macros to those.

Thanks again for the help.
 
Upvote 0
I am impressed with your persistence. My guess is that you are also learning a lot. You can add an ActiveX Forms control and get a reference to it, which you can then use to change its properties with
Code: 
Option Explicit

Sub addAnActiveXCommandButton()
    Dim x As MSForms.CommandButton
    Set x = ActiveSheet.Shapes.AddOLEObject("forms.commandbutton.1").OLEFormat.Object.Object
    x.ForeColor = RGB(255, 0, 0)
    End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Saving a XLAM file (from within it's own code) leaves a temp copy in the directory
Replies
0
Views
162
BenW71
B
P
  • Solved
VBA to open file using Cell for the path
Replies
4
Views
547
Peter Davison
P
J
  • Question
Excel VBA .Save Causing a Save As to 'Random' number filename
Replies
2
Views
150
jcoeng
J
G
  • Solved
Change Macro to import files from hyperlinks
2
Replies
12
Views
398
GarnesGambit
G
M
  • Question
Seeking alternate/better solution for excel XLAM addin, currently using form/multipages
Replies
1
Views
421
Exceljeanie
E

Forum statistics

Threads
1,214,646
Messages
6,120,718
Members
448,986
Latest member
andreguerra

Share this page

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