Why is this getting an error? It says that the 'public' line is creating a
compiler error -- it's an invalid attribute in the sub.

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Public Z As Workbook


Z = ActiveWorkbook.Name

Hi Mark,

Mark wrote:
Why is this getting an error? It says that the 'public' line is
creating a compiler error -- it's an invalid attribute in the sub.

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Public Z As Workbook


Z = ActiveWorkbook.Name

To declare a public variable, it must be outside of any procedures.
Example:

Public gbMyVar As Boolean

Sub Test()
gbMyVar = Application.ScreenUpdating
MsgBox gbMyVar
End Sub

If you're just going to use that variable in the current module, you should
use Private instead.

One other item - this code

Z = ActiveWorkbook.Name

will fail because you have declared Z as type Workbook, which is an object
variable. You are trying to set a string (return value of Name property) to
an object variable, which will not work. If you truly want a reference to
the ActiveWorkbook object, you can do this instead:

Set Z = ActiveWorkbook

If you just want the name, then you should declare Z As String.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Thought this would solve my problem, but it didn't. Any idea why this line,
in a different sub in the same module that I set Z = activeworkbook, doesn't
work....

Range("B2").Select
ActiveCell.FormulaR1C1 = _
"='[" & Z & "]Summary Data'!R503C[32]" 'this line gets error
message

error is 91 - object variable or with block variable not set

"Jake Marx" wrote:

Hi Mark,

Mark wrote:
Why is this getting an error? It says that the 'public' line is
creating a compiler error -- it's an invalid attribute in the sub.

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Public Z As Workbook


Z = ActiveWorkbook.Name

To declare a public variable, it must be outside of any procedures.
Example:

Public gbMyVar As Boolean

Sub Test()
gbMyVar = Application.ScreenUpdating
MsgBox gbMyVar
End Sub

If you're just going to use that variable in the current module, you should
use Private instead.

One other item - this code

Z = ActiveWorkbook.Name

will fail because you have declared Z as type Workbook, which is an object
variable. You are trying to set a string (return value of Name property) to
an object variable, which will not work. If you truly want a reference to
the ActiveWorkbook object, you can do this instead:

Set Z = ActiveWorkbook

If you just want the name, then you should declare Z As String.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Hi Mark,

Mark wrote:
Thought this would solve my problem, but it didn't. Any idea why
this line, in a different sub in the same module that I set Z =
activeworkbook, doesn't work....

Range("B2").Select
ActiveCell.FormulaR1C1 = _
"='[" & Z & "]Summary Data'!R503C[32]" 'this line gets
error message

error is 91 - object variable or with block variable not set

If you are using Set Z = ActiveWorkbook, then Z holds a reference to the
workbook object. So, in order to get the name, you would have to use Z.Name
instead of just Z.

If that's not the case, please post all the code from that module so we can
help you debug.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]