How can I execute formular which I insert by VBA?-CodePudding

I wrote some code and have a question.

I sucessfully make macro which insert formular into cell.

Problem is It is not working automatically.

Function test(PCell As Range) As String
    test = Chr(61) & Replace(PCell.Address, "$", "")
End Function

Cell shows =N3 (simple example). And I can execute Push "F2" - "Enter". It is working well.

Problem is .. There are more than 100 cells. If there is no solution, I have to push F2 - Enter 100 hundred times.

After select the cells, How can I execute the formular in cells? or by using VBA?

I tried to use Selection.Evaluate() But there is nothing happened. And "F9" key is also.

CodePudding user response：

to have function calculate at every sheet change, just add Application.Volatile (see:

How to use this function?

In excel worksheet, press Alt F11 or Developer Tab->Visual Basic Right Click in any item in Project Explorer and then select Insert->Module
In this module, insert this above code.
To run this code you can use the immediate window (Ctrl G) or write another procedure to run this code.

Suggestions

Instead of using Replace you can use Range.AddressLocal(False,False) It will produce the same result.

This function will run very fast

For 1000 rows: 0.016 sec (or 16 milliseconds)

For Reference my laptop is dual core i7-5500u which is low end.