Application.Caller.Text not available when collapsing groups via VBA

[DougM111]

Hi All

I have a workbook that contains a column that gets its value from a UDF. The UDF relies on the Application.Caller.Text property to return the original value of the cell under specific circumstances. Unfortunately, I can't use Application.Caller.Value as it causes a circular reference. The workbook also contains row grouping.

In order to provide non-Excel expert users a simple way to expand and collapse the group, I have placed a button on the worksheet which runs the VBA to achieve this. When the groups are collapse, the UDF is triggered. I'm not convinced that this is valid behaviour but Excel does it anyway.

What I have found is that when the group is collapse via VBA code, the Application.Caller.Text property is not available in the UDF and therefore the function fails. However, when you use the native Excel + or - or numeric grouping buttons on the worksheet, the Application.Caller.Text property in the function IS available.

To prove this I created a very simple workbook. I added a module to the project and added the following Sub & function.

Function TestAppCallerText(argCellValue)
 
On Error Resume Next
 
MsgBox argCellValue & " - " & Application.Caller.Text
 
If Err <> 0 Then
    MsgBox Err.Description
End If
 
TestAppCallerText = Err
 
End Function

Sub CollapseGroup()
    ActiveSheet.Outline.ShowLevels RowLevels:=1
End Sub

I added the following values to the worksheet. The first 3 rows in column A contain "A1", "A2" and "A3" respectively. Corresponding cells in Column B call the TestAppCallerText function and pass the value of the corresponding cell in column A as an argument e.g. "=TestAppCallerText(A1)".

I then grouped the rows. You should now see the grouping +, - and numeric level in the top left corner of the worksheet.

I then added a button to the worksheet (below the grouped rows) which runs the sub "CollapseGroup".

If you now click on the grouping minus or numeric level "1" button, you will get a message box showing the value of Application.Caller.Text in each row where the function has been triggered by the collapse group. You will probably see something like "A1 - 0" in the Message box. If you then expand the group using the Excel grouping button, the group will be expanded.

Now try clicking on the button which runs the VBA command "ActiveSheet.Outline.ShowLevels RowLevels:=1" to collapse the group. Notice the error shown instead of the Text value.

This is happening in Excel 2007 but I have also tried it in 360 with the same result. So I'm assuming this is a bug where the Application.Caller.Text property is not available when the collapse group is executed via VBA. Interestingly, other Application.Caller properties such as Address and Value are available but as mentioned earlier, I can't use Value as it results in a circular reference.

I guess the question is, is there another VBA command which can be used to collapse and expand specific groups in a worksheet?

Any suggestions would be greatly appreciated.

Cheers
Doug

 

[DougM111]

I seem to have found a suitable workaround for this issue.

Prior to executing the Outline.ShowLevels command, I set the Application.Calculate method to Manual. This avoids triggering the UDF when the Outline.ShowLevels command executes, therefore avoiding the issue with Application.Caller.Text property not being available when the UDF is triggered by this specific command.

I then set the Application.Calculate methof back to Automatic after the Outline.ShowLevels command has executed so that normal functionality resumes. At this point, the UDF is also triggered but the Application.Caller.Text property is now available. Go figure....

Sub CollapseGroup()

    Application.Calculation = xlCalculationManual

    ActiveSheet.Outline.ShowLevels RowLevels:=1

    Application.Calculation = xlCalculationAutomatic
    
End Sub