Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display value of a VBA variable inside a cell
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 -- Paul Kraemer |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display value of a VBA variable inside a cell
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. -- Gary''s Student - gsnu201001 "Paul Kraemer" wrote: 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 -- Paul Kraemer |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display value of a VBA variable inside a cell
thank you...that helped me alot!!
-- Paul Kraemer "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu201001 "Paul Kraemer" wrote: 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 -- Paul Kraemer |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
display dots inside a cell in graph form but without Excel graph? | Excel Programming | |||
Variable inside function range | Excel Programming | |||
Variable inside a formula | Excel Programming | |||
Variable with no Value inside Macro Equation?? | Excel Programming | |||
variable inside a formula? | Excel Programming |