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?
Using VBA Replace to alter DBRW references in PAfE
- Elessar
- Community Contributor
- Posts: 342
- Joined: Mon Nov 21, 2011 12:33 pm
- OLAP Product: PA 2
- Version: 2.0.9
- Excel Version: 2016
- Contact:
Re: Using VBA Replace to alter DBRW references in PAfE
Hello!
Is the cell format still General, or is it changed to Text?
- Try Ctrl + Alt + F9 to recalculate all cells, including the ones with updated formulas.
- Try to add Application.Calculate to the end of your VBA script
Best regards, Alexander Dvoynev
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
- gtonkin
- MVP
- Posts: 1202
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Using VBA Replace to alter DBRW references in PAfE
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.
This should force a recalculation of the cells next time the sheet is calculated.
-
- Posts: 96
- Joined: Mon Jul 29, 2019 5:02 am
- OLAP Product: Planning Analytics
- Version: 2.0.9.x
- Excel Version: Office 365 16
Re: Using VBA Replace to alter DBRW references in PAfE
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
-
- Posts: 96
- Joined: Mon Jul 29, 2019 5:02 am
- OLAP Product: Planning Analytics
- Version: 2.0.9.x
- Excel Version: Office 365 16
Re: Using VBA Replace to alter DBRW references in PAfE
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
- WilliamSmith
- Posts: 40
- Joined: Tue Dec 13, 2022 8:54 pm
- OLAP Product: TM1 / PA / PAx / PAW
- Version: TM1 11
- Excel Version: 365
Re: Using VBA Replace to alter DBRW references in PAfE
Try using Application.EnableEvents = False before modifying the cell formula.
Then .Dirty method on the range.
Then trigger calculation.
Then .Dirty method on the range.
Then trigger calculation.
-
- Posts: 96
- Joined: Mon Jul 29, 2019 5:02 am
- OLAP Product: Planning Analytics
- Version: 2.0.9.x
- Excel Version: Office 365 16
Re: Using VBA Replace to alter DBRW references in PAfE
Thanks William.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.
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.
- WilliamSmith
- Posts: 40
- Joined: Tue Dec 13, 2022 8:54 pm
- OLAP Product: TM1 / PA / PAx / PAW
- Version: TM1 11
- Excel Version: 365