1

I created a function and saved it in the main module, as follows:

Public Function Teste() As Variant
    Application.Volatile
    MsgBox "rodou"
    Teste = ""
End Function

Calculation Options is set to Automatic. In the cell, I placed the formula (eg. =Teste()), expecting that Teste function would run, everytime I would modify any cell because Application.Volatile sets my function as Dirty, forcing Excel to run the function.

What happens is that Teste function is not called, unless I put any of the cells that will be updated in the Change event of the worksheet (eg. =Teste(B2)).

What I want is just to run Teste function everytime I do changes in the worksheet.

Thanks in advance.

MyThorRJ
  • 21
  • 1
  • 5
  • 1
    @Gary'sStudent That is not right. Excel does recalculate volatile functions on any change of any cell in any workbook. This behaviour is the very point of `Volatile`. In fact I cannot reproduce the OP's problem, I get the message box whenever I change any cell. – GSerg Apr 09 '16 at 18:27
  • Documentation says: "_This method has no effect if it's not inside a user-defined function __used to calculate a worksheet cell___". Qeustion is whether that means the function is used in a worksheet cell or the function modifies (another) worksheet cell in its code.Excel can know whether the function does or does not when it compiles. This function does not modify a worksheet cell in its code, hence might not be called. – Paul Ogilvie Apr 09 '16 at 18:31
  • @PaulOgilvie The documentation means "This method has no effect unless the function was placed in a cell." If you create a function and only use it in code, never placing it in any cell, `Volatile` will have no effect. Also, [no VBA function is allowed to change any cells](http://stackoverflow.com/q/13705663/11683). – GSerg Apr 09 '16 at 18:33
  • @CSerg, huh? "Also, no VBA function is allowed to change any cells". `AcriveSheet.Cells("A1")= 123`? – Paul Ogilvie Apr 09 '16 at 18:34
  • @PaulOgilvie Yes, that will not work if executed from a function called from a cell. – GSerg Apr 09 '16 at 18:37
  • @CSerg, didn't know. Thanks. – Paul Ogilvie Apr 09 '16 at 18:38
  • See also: http://stackoverflow.com/questions/29915312/how-vba-declared-volatility-works?rq=1 – Paul Ogilvie Apr 09 '16 at 18:38
  • stop the fight, please! I need help. hahahaha... just kidding. If I understood, my code is right but Excel is not working properly. So, what now? – MyThorRJ Apr 09 '16 at 21:20
  • Maybe you have disabled automatic recalculation? – Vegard Apr 09 '16 at 22:03
  • hi @Vegard. as I said before, calculation is set to automatic. – MyThorRJ Apr 09 '16 at 22:14

1 Answers1

0

it's working now! after compiling VBA code, Teste function is now running everytime I do changes in any cell of the worksheet. this information is in the post shared by @Paul Ogilvie (stackoverflow.com/questions/29915312/…).

thank you all for the prompt help!

Community
  • 1
  • 1
MyThorRJ
  • 21
  • 1
  • 5