Retain Workbook Name throughout Module
 

Hi,

I use the below to capture the name of the file that the macro was run from,
this is then used in the Subs code before another Sub is called. My
question is how do I retain the Workbook Name so that I can use in another
Sub within the same module.

iFname = Application.ActiveWorkbook.Name

Thanks, Rob

Retain Workbook Name throughout Module
 

Define iFname in the declarations portion of the code module (above the first
Sub or Function definition). Do not declare it in any Subs or Functions in
the code module:

Option Explicit
Dim iFname As String

Sub MySub1()
iFname = Application.ActiveWorkbook.Name
End Sub

Sub MySub2()
If iFname="" then
MsgBox "You have not run MySub1 yet"
Else
MsgBox iFname
End if
End Sub

If you need it to be 'visible' across several modules, declare it using
Public instead of Dim.

"Rob" wrote:

Hi,

I use the below to capture the name of the file that the macro was run from,
this is then used in the Subs code before another Sub is called. My
question is how do I retain the Workbook Name so that I can use in another
Sub within the same module.

iFname = Application.ActiveWorkbook.Name

Thanks, Rob


.

Retain Workbook Name throughout Module
 

Thank you, couldn't find this in VBA help so much appreciated.

Rob

"JLatham" wrote in message
...
Define iFname in the declarations portion of the code module (above the
first
Sub or Function definition). Do not declare it in any Subs or Functions
in
the code module:

Option Explicit
Dim iFname As String

Sub MySub1()
iFname = Application.ActiveWorkbook.Name
End Sub

Sub MySub2()
If iFname="" then
MsgBox "You have not run MySub1 yet"
Else
MsgBox iFname
End if
End Sub

If you need it to be 'visible' across several modules, declare it using
Public instead of Dim.

"Rob" wrote:

Hi,

I use the below to capture the name of the file that the macro was run
from,
this is then used in the Subs code before another Sub is called. My
question is how do I retain the Workbook Name so that I can use in
another
Sub within the same module.

iFname = Application.ActiveWorkbook.Name

Thanks, Rob


.