TM1 Forum

I have a problem which is not strictly a PAfE issue but I know some here are still into VBA

I am using the following to repoint the cube source of the formulae

c.Formula = Replace(c.Formula, "$B$1", "'XX'!$B$1")

It updates the forumla fine but the revised formula does not work until I edit the cell with F2 and hit enter, after which things are fine.

I found an online reference which suggested to follow up the Replace with the following replace in order it force the change.

c.Formula = Replace(c.Formula, "=", "=")

But this is not working for me. And with thousands of DBRW's in the workbook .....

Any suggestions?

Hello!

JohnO wrote: ↑Tue Apr 02, 2024 3:13 am It updates the forumla fine but the revised formula does not work until I edit the cell with F2 and hit enter, after which things are fine.

Is the cell format still General, or is it changed to Text?

1. Try Ctrl + Alt + F9 to recalculate all cells, including the ones with updated formulas.
2. Try to add Application.Calculate to the end of your VBA script

In your VBA code, could you not set the cells you are updating to Dirty using the .Dirty method?
This should force a recalculation of the cells next time the sheet is calculated.

gtonkin wrote: ↑Tue Apr 02, 2024 6:01 pm In your VBA code, could you not set the cells you are updating to Dirty using the .Dirty method?
This should force a recalculation of the cells next time the sheet is calculated.

George, that worked (Sort of). What I have found is that PAfE has to be loaded but no connection /login is necessary for changes to be detected. I assume this is because otherwise Excel just considers the formula a text string.
After doing perhaps a 1000 cell changes (I need probably 15,000 ) then Excel crashes. I'd say some sort of way the add in works that is causing the issues. Arghhh

I should also say that Sendkeys("F2") appears to work, again with the add-in active. However I have experienced crashes, though not as severe as using .dirty

Try using Application.EnableEvents = False before modifying the cell formula.
Then .Dirty method on the range.
Then trigger calculation.

WilliamSmith wrote: ↑Wed Apr 03, 2024 5:10 pm Try using Application.EnableEvents = False before modifying the cell formula.
Then .Dirty method on the range.
Then trigger calculation.

Thanks William.

I was able to get it to work by doing this. Specifically, with the add-in loaded I was able to disable events once at the beginning of the routine and cycle through the worksheets and cells and set .Dirty on each in scope cell and at the end reenable events.

Rebuild then picked up up all of the changes.

You're welcome, glad I could help