Reference Global vars in cell formulas?

  • Thread starter Thread starter DocBrown
  • Start date Start date
D

DocBrown

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
 
Thanks for the quick reply.

What about reference the global var in a worksheet cell?

John

Mike H said:
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 said:
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?
Click to expand...
Click to expand...
 
Back
Top