Display value of a VBA variable inside a cell


Paul Kraemer


I am using Excel 2007. I have created a VBA module called MyModule. Inside
this module, I have defined a public variable as follows:

Public blnMyBoolean As Boolean

Next, I created a Function to return the value of this variable as follows:

Public Function GetMyBoolean() As Boolean
GetMyBoolean = blnMyBoolean
End Function

Next, I used my function as a formula for a cell on one of my worksheets as


The formula in this cell works, but it does not update automatically. If my
code changes the value of blnMyBoolean, the only way I can seem to get the
value of my cell (from the formula) to update is to select the cell, click at
the end of the formula in the formula bar, and then click "Enter".

Can anyone tell me if there is a way I can have the value of my cell (from
the formula) to be updated constantly (or at least any time my code changes
the value of blnMyBoolean)?

If I am no going about this in the best way and someone has a better/easier
way for me to have a cell reflect the value of a variable inside my VBA code,
any advice will be greatly appreciated.

Thanks in advance,

Gary''s Student


Public b As Boolean
Sub dural()
b = False
End Sub

Function bb()
bb = b
End Function

Sub second()
b = True
End Sub

The real problem is that Excel has to know when to re-calculate =bb().

By including the Volatile statement, the function will be re-calculated
whenever the worksheet is re-calculated.

It is now the responsibility of the sub that modifies the public variable to
also re-calculate the worksheet, thus refreshing the value of the function.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question