Is it possible to reference global/public vars in cell forumulas?

I want to create a function that sets a global variable say true and false,
and have a cell formula that has an if statement:

=if([Globalvar], do something, "")

I can't use a UDF because it causes other Macros I have to fail with
RunTimeError 1004. It's something about UDFs not allowed to alter other
cells, but the UDF wouldn't be modiying cells, but my non-UDF macros are.

So, I'm experimenting with a different approach.

Ideas?

Hi,

If you decalre the variable like this then it's available in all subs and
functions

Dim Globalvar

Sub nn()
Globalvar = "MyString"
Call yyy
End Sub

Sub yyy()
MsgBox Globalvar
Call MyFunc
End Sub

Private Function MyFunc()
MsgBox Globalvar
End Function

Mike

"DocBrown" wrote:

Is it possible to reference global/public vars in cell forumulas?

I want to create a function that sets a global variable say true and false,
and have a cell formula that has an if statement:

=if([Globalvar], do something, "")

I can't use a UDF because it causes other Macros I have to fail with
RunTimeError 1004. It's something about UDFs not allowed to alter other
cells, but the UDF wouldn't be modiying cells, but my non-UDF macros are.

So, I'm experimenting with a different approach.

Ideas?

What Mike has mentioned is about module level variables. If you want
variables to be accessed at Workbook Level or Public Module level you will
have to use Public keyword..Refer the below 1 page article about the scope
and lifetime of variables in VBA

http://www.ozgrid.com/VBA/variable-scope-lifetime.htm

--
Jacob


"DocBrown" wrote:

Is it possible to reference global/public vars in cell forumulas?

I want to create a function that sets a global variable say true and false,
and have a cell formula that has an if statement:

=if([Globalvar], do something, "")

I can't use a UDF because it causes other Macros I have to fail with
RunTimeError 1004. It's something about UDFs not allowed to alter other
cells, but the UDF wouldn't be modiying cells, but my non-UDF macros are.

So, I'm experimenting with a different approach.

Ideas?

Thanks for the quick reply.

What about reference the global var in a worksheet cell?

John

"Mike H" wrote:

Hi,

If you decalre the variable like this then it's available in all subs and
functions

Dim Globalvar

Sub nn()
Globalvar = "MyString"
Call yyy
End Sub

Sub yyy()
MsgBox Globalvar
Call MyFunc
End Sub

Private Function MyFunc()
MsgBox Globalvar
End Function

Mike

"DocBrown" wrote:

Is it possible to reference global/public vars in cell forumulas?

I want to create a function that sets a global variable say true and false,
and have a cell formula that has an if statement:

=if([Globalvar], do something, "")

I can't use a UDF because it causes other Macros I have to fail with
RunTimeError 1004. It's something about UDFs not allowed to alter other
cells, but the UDF wouldn't be modiying cells, but my non-UDF macros are.

So, I'm experimenting with a different approach.

Ideas?