How to identify variable workbooks in an excel Addin

  • Thread starter Martin in Frisco Texas
  • Start date
M

Martin in Frisco Texas

I am using a 2002 Excel workbook Auto_Open marco to run a macro in an
Addin.xla that I created. The workbook macro contains a procedure -
Range("WorkbookName") = ActiveWorkbookName - because the Addin is intended to
be used with workbooks with variable names. The Addin Marco that is called
from the Auto_Open macro contains the following procedure - WBFilename =
Worksheets("Calculations").Range("WorkbookName"). WBFileName is used in many
macros in the Addin to identify the workbook. At the point of such Addin
macro I get a message "Run-Time error '9': subscript out of range". When I
run the Auto_Open macro from the VBA menu "Run" the error does not happen,
and both macros work fine. Why do I get the error message? If you would
like, I can email a sample of these two macros. I have tried lots of ways to
solve this, but have not been successful.
 
J

Jim Cone

Martin,

The Auto_Open macro in the add-in will only run when the add-in workbook is opened.
It does not run when other workbooks are opened.

Jim Cone
San Francisco, USA


"Martin in Frisco Texas" <Martin in Frisco (e-mail address removed)>
wrote in message
I am using a 2002 Excel workbook Auto_Open marco to run a macro in an
Addin.xla that I created. The workbook macro contains a procedure -
Range("WorkbookName") = ActiveWorkbookName - because the Addin is intended to
be used with workbooks with variable names. The Addin Marco that is called
from the Auto_Open macro contains the following procedure - WBFilename =
Worksheets("Calculations").Range("WorkbookName"). WBFileName is used in many
macros in the Addin to identify the workbook. At the point of such Addin
macro I get a message "Run-Time error '9': subscript out of range". When I
run the Auto_Open macro from the VBA menu "Run" the error does not happen,
and both macros work fine. Why do I get the error message? If you would
like, I can email a sample of these two macros. I have tried lots of ways to
solve this, but have not been successful.
 
M

Martin in Frisco Texas

Jim Cone

Thanks for your reply.

The Auto_Open macro is in the workbook, not the Add-in. The Auto_Open Macro
calls a sub in the Addin called "OpenProc". I get the error messaage in the
"OpenProc" sub.

Martin
 
J

Jim Cone

Martin,

Some comments...
An add-in always uses the "ActiveSheet" when references to
ranges are made, unless a specific workbook/sheet reference is used.
So Range("A1") refers to that range on the active sheet in the active workbook.
(the add-in is never active)

Workbooks("FileName.xls").Worksheets(1).Range("A1") refers to that range
on the first worksheet, in the FileName workbook, if that workbook is open,
otherwise you get an error.

However, if you assign a value to a variable...
Dim FileName as String
FileName = "OtherName.xls"
Then Workbooks(FileName).Worksheets(1).Range("A1") refers to the "OtherName" workbook.
Note - that there are no quote marks surrounding the variable, as you have already
told Excel that it is a String.

If you only want your add-in to run its code with certain workbooks then
you can check the active workbook name against a list of valid names...

Dim strName as String
strName = ActiveWorkbook.Name
If strName = "Larry.xls" or strName = "Moe.xls" or strName = "Curly.xls" then
'do stuff
Else
Exit Sub
End If

Regards,
Jim Cone
San Francisco, USA
'------------------------------


"Martin in Frisco Texas"
<[email protected]>
wrote in message
Jim Cone
Thanks for your reply.
The Auto_Open macro is in the workbook, not the Add-in. The Auto_Open Macro
calls a sub in the Addin called "OpenProc". I get the error messaage in the
"OpenProc" sub.
Martin
 
M

Martin in Frisco Texas

Jim

Thanks again.

What this all boils down to is that I want to make WBFileName equal to the
active workbook name, because each workbook that is intended to worik with
the addin will have a different filename. I can do that by WBFileName =
ActiveWorkbook.Name in the Addin macro "OpenProc" and it works, but I get
"out of script" error messages in other macros that are called by the
OpenProc macro. It is just not consistent. When these other macros are
executed by menus, they work fine.

Martin
 
J

Jim Cone

Martin,

To call a macro in an add-in you can use the following syntax...
Application.Run "YourAdd-in.xla!YourSubName"

If there are arguments required for the sub then...
Application.Run "YourAdd-in.xla!YourSubName", Arg1, Arg2

Note the exclamation point !

Regards,
Jim Cone


"Martin in Frisco Texas"
<[email protected]>
wrote in message
Jim

Thanks again.

What this all boils down to is that I want to make WBFileName equal to the
active workbook name, because each workbook that is intended to worik with
the addin will have a different filename. I can do that by WBFileName =
ActiveWorkbook.Name in the Addin macro "OpenProc" and it works, but I get
"out of script" error messages in other macros that are called by the
OpenProc macro. It is just not consistent. When these other macros are
executed by menus, they work fine.

Martin
 
M

Martin in Frisco Texas

Jim

Thanks once more.

I have been using the syntax as you suggested.

I have discovered that once the Addin is installed, the activeworkbook
becomes the addin and remains the addin until the Auto_Open sub is finished.
Then the activeworkbook becomes the (nonaddin) workbook. This is why the sub
reacts differently depending upon if they are run from the Auto_Open sub or
run from menu.

I am thinking about using an If statement depending upon which workbook is
the activeworkbook. This way I can write different subs to run each way.

Hope you have a better idea.

I appreciate all the attention you have given my problem.

Martin
 
J

Jim Cone

Martin,

In the VisualBasicEditor (VBE) Project window (Ctrl +R), select "ThisWorkbook" in the
add-in workbook project.
In the Properties window (F4), the "IsAddin" property should be set to True.
If this is done the workbook will not be visible and should never become "active".

Regards,
Jim Cone


"Martin in Frisco Texas"
<[email protected]>
wrote in message
Jim
Thanks once more.
I have been using the syntax as you suggested.
I have discovered that once the Addin is installed, the activeworkbook
becomes the addin and remains the addin until the Auto_Open sub is finished.
Then the activeworkbook becomes the (nonaddin) workbook. This is why the sub
reacts differently depending upon if they are run from the Auto_Open sub or
run from menu.
I am thinking about using an If statement depending upon which workbook is
the activeworkbook. This way I can write different subs to run each way.
Hope you have a better idea.
I appreciate all the attention you have given my problem.
Martin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Help with Macro Security 1
Addin Auto_Open code executes only in debug mode 2
Script out of range 3
Unwanted workbooks on startup 2
Adding custom menus to existing excel application 0
Opening some workbooks with a Macro renames them 7
Addin not allowing thisworkbook.save in before save event 3
Opening a list of Excel files and run a macro in each 17

Top