Excel VBA, PAFX: make a cell static

[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 !

[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.

[Wim Gielis]

Hi Paul,

Thanks ! Version 56 is pretty new and I don’t have it yet.
I will experiment once I have it.

[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.

[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...

[monkey97]

Has it been resolved since then?

[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