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

DougM111

New Member
Joined
Dec 12, 2016
Messages
2
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.

Code:
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

Code:
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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

DougM111

New Member
Joined
Dec 12, 2016
Messages
2
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....

Code:
Sub CollapseGroup()

    Application.Calculation = xlCalculationManual

    ActiveSheet.Outline.ShowLevels RowLevels:=1

    Application.Calculation = xlCalculationAutomatic
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,964
Members
413,955
Latest member
FalcoDaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top