How to declare variable as public.

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

Good afternoon Mark

You appear to be writing a routine and announcing that as public, but you want to announce a variable as public.

Try this before you define your routine:

Public Msg, Style, Title, Help, Ctxt, Response, MyString

The variables listed will be able to be used by any subsequent code.

HTH

DominicB

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]