Page 1 of 1
Excel VBA, PAFX: make a cell static
Posted: Tue Nov 03, 2020 1:07 am
by Wim Gielis
Hello all,
I am looking at a way to make 1 cell (or a bunch of cells) static, using Excel VBA. In TM1 Perspectives, this wasn't an issue. Something like this would work:
Code: Select all
Sub test()
With ActiveCell
.Value = .Value
End With
End Sub
But not anymore in PAFX (or whateve it is called this week, as Alan would say).
If the cell contains a simple DBRW, the DBRW is not overwritten, contrary to what happens in Perspectives.
So I figured a few workarounds, including:
Code: Select all
Sub test()
Dim z As Variant
With ActiveCell
z = .Text
.ClearContents
.Value = z
End With
End Sub
All to no avail. The DBRW is stubborn and survives (even after clearing cells, reculating, refreshing, ...).
I do see the idea of being able to paste data over DBRWs without erasing them and I like that. However we should also be able to do what I’m trying to do here.
Did anyone succeed ? Thanks !
Re: Excel VBA, PAFX: make a cell static
Posted: Tue Nov 03, 2020 7:05 am
by Paul Segal
This should work:
Code: Select all
Application.COMAddIns("CognosOffice12.Connect").Object.AutomationServer.Application("COR", "1.1").ClearSelection
with alerts turned off. If you're on any version earlier than .56 then the alerts won't turn off. .56 and above and they will.
Re: Excel VBA, PAFX: make a cell static
Posted: Tue Nov 03, 2020 7:35 am
by Wim Gielis
Hi Paul,
Thanks ! Version 56 is pretty new and I don’t have it yet.
I will experiment once I have it.
Re: Excel VBA, PAFX: make a cell static
Posted: Tue Nov 17, 2020 7:19 pm
by 20 Ton Squirrel
This is a bit pedantic but I rather specialize in Excel VBA and would like to offer a suggestion for your code, Wim.
Whenever you are writing values to the worksheet use the Value2 property.
Code: Select all
Sub test()
With ActiveCell
.Value2 = .Value2
End With
End Sub
The standard Value property "formats" the value, particularly currency which gets rounded. Value2 foregoes the checks on variable typing and just provides the value as-is. As an added bonus, Value2 is slightly faster when reading/writing in cells.
I realize that original code isn't being used but... well, I just can't help myself.
Re: Excel VBA, PAFX: make a cell static
Posted: Thu Nov 26, 2020 10:16 am
by Wim Gielis
The fun becomes even bigger if we also take into account
- the CurrentArray range. It is associated with good old array formulas (Ctrl-Shift-Enter to confirm).
- the spill range. It is associated with the new dynamic array formulas (which are awesome by the way)
Joy joy...
Re: Excel VBA, PAFX: make a cell static
Posted: Wed Feb 14, 2024 3:09 pm
by monkey97
Has it been resolved since then?
Re: Excel VBA, PAFX: make a cell static
Posted: Fri Feb 16, 2024 4:23 pm
by WilliamSmith
For posterity, the VBA code below will work:
Why it works? Disabling events will prevent ExcelDNA batch recalculation (e.g. RECALC_XXXX) from triggering, otherwise once the batch calculation returns from the PAfE add-in, the cell will be overwritten with the original formula AFTER the cell.Value = cell.Value operation completes.
Code: Select all
dim cell as range
set cell = ActiveCell 'ActiveCell, for each, etc.
Application.EnableEvents = False
cell.Value= cell.Value 'or .Value2
Application.EnableEvents = True