Display value of a VBA variable inside a cell

  • Thread starter Thread starter Paul Kraemer
  • Start date Start date
P

Paul Kraemer

Hi,

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
follows:

=GetMyBoolean()

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,
Paul
 
Consider:

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

Function bb()
Application.Volatile
bb = b
End Function

Sub second()
b = True
Application.CalculateFullRebuild
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.
 
Back
Top