How to identify variable workbooks in an excel Addin
 

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.

How to identify variable workbooks in an excel Addin
 

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

How to identify variable workbooks in an excel Addin
 

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

"Jim Cone" wrote:

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

How to identify variable workbooks in an excel Addin
 

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"

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


"Jim Cone" wrote:
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
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.

How to identify variable workbooks in an excel Addin
 

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

"Jim Cone" wrote:

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"

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


"Jim Cone" wrote:
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
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.

How to identify variable workbooks in an excel Addin
 

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"

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

"Jim Cone" wrote:

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"

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


"Jim Cone" wrote:
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
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.

How to identify variable workbooks in an excel Addin
 

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

"Jim Cone" wrote:

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"

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

"Jim Cone" wrote:

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"

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


"Jim Cone" wrote:
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
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.

How to identify variable workbooks in an excel Addin
 

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"

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


"Jim Cone" wrote:
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

How to identify variable workbooks in an excel Addin
 

Jim

I checked and the "IsAddin" is set to true.

Martin

"Jim Cone" wrote:

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"

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


"Jim Cone" wrote:
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

How to identify variable workbooks in an excel Addin
 

Martin,
I am out of ideas.
Regards,
Jim Cone


"Martin in Frisco Texas"

wrote in message

Jim
I checked and the "IsAddin" is set to true.
Martin


"Jim Cone" wrote:
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"

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


"Jim Cone" wrote:
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

How to identify variable workbooks in an excel Addin
 

Jim

Thanks for all your efforts.

Martin

"Jim Cone" wrote:

Martin,
I am out of ideas.
Regards,
Jim Cone


"Martin in Frisco Texas"

wrote in message

Jim
I checked and the "IsAddin" is set to true.
Martin


"Jim Cone" wrote:
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"

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


"Jim Cone" wrote:
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