Excel 4 macro help SHOW.DETAIL

[GlennUK]

Hi all,

this might sound like a bizarre request, but I've just done a macro record to find out how to show detail in an outline and this is what Excel recorded ...

Columns("BY:BY&quot.Select
ExecuteExcel4Macro "SHOW.DETAIL(2,77,TRUE,,42)"

The only Excel macro function help I can get my hands on only gives the first 4 argument descriptions.

So my question is ... what does the 42 stand for?

Thanks in advance, Glenn.

 

[xlbo]

I think it's an error in recording - I can vaguely remember this coming up a while ago on one of the discussion lists. Does it run ?? 'cos the way I remember, you should get a "too many arguments" error. AFAIK, you can just delete one of the commas after TRUE and it'll work. Therefore, the 42 IS the 4th argument.

If you could post that help text I'd certainly appreciate it as I've never managed to find anything useful regarding excel4macros

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[GlennUK]

Hi Geoff,

no it doesn't run, so took the 42 out completely, and runs.

Here is the help for SHOW.DETAIL :

Macro Sheets Only
Expands or collapses the detail under the specified expand or collapse button.

Syntax

SHOW.DETAIL(rowcol, rowcol_num, expand, show_field)
Rowcol is a number that specifies whether to operate on rows or columns of data.

Rowcol Operates on

1 Rows
2 Columns
3 The current cell's row or column. The second argument, rowcol_num, is then ignored.

Rowcol_num is a number that specifies the row or column to expand or collapse. If you are in A1 mode, you must still give the column as a number. If rowcol_num is not a summary row or column, SHOW.DETAIL returns the #VALUE! error value and interrupts the macro.
Expand is a logical value that specifies whether to expand or collapse the detail under the row or column. If expand is TRUE, Microsoft Excel expands the detail under the row or column; if FALSE, it collapses the detail under the row or column. If expand is omitted, the detail is expanded if it is currently collapsed and collapsed if it is currently expanded.
Show_Field is a string specifying the name of the field to add to a PivotTable, if the selection is inside a PivotTable. The new field is added as the new innermost field. Available for only innermost row or column fields.

 

[xlbo]

Runs without the comma and with the 42 as well ;-)
Looks like the last argument is for pivot tables only anyway so you should be fine with (row,col,true/false,)

I think the recording error is something to do with the excel4macro array returned being actually a string seperated by commas which excel has a hard time converting to a proper array

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[GlennUK]

Hi Geoff,

thanks for your comments. The only reason I wanted to record the action was that coding the ShowDetail VBA command was giving me an error, and I wanted to know what I'd done wrong syntax wise. Then I get an Excel 4 macro command in the recorded code!

Oh well, I'm running this with the embedded ExecuteExcel4Macro ( without the 42 of course ), as I have to have this automation completed by the end of today.


Cheers, Glenn.