assign result of ExecuteExcel4Macro to an array

K

keepitcool

I'm stumbling along with following:
The idea is to get a list of all workbooks INCLUDING addins.

How do I get following (or any xl4 macro command) to return an array?


Function AllBooks() As Variant
Dim v
v = ExecuteExcel4Macro("Documents(3)")
Stop
End Function

For the moment I circumvent this by using a name object...
but I'm sure there must be a more elegant way of doing this.

keepITcool
amsterdam, holland
 
P

Peter T

Hi KeepItCool

I'm afraid I don't have your answer, only a question.
Apart from needing to use a name object I also need to use
the worksheet, like this:

Sub Tester()
Dim r As Range, c, v()
ActiveWorkbook.Names.Add Name:="test", _
RefersToR1C1:="=DOCUMENTS(3)"
Set r = Rows(1)
r.FormulaArray = "=test"
c = r.SpecialCells(xlCellTypeFormulas, 2).Count
ReDim v(1 To c)
For i = 1 To c
v(i) = r.Cells(i)
Debug.Print v(i)
Next
End Sub

Not elegant! Do you have a method that does not require
the worksheet?

TIA
Peter
 
K

keepitcool

but it should be ..nah MUST be .. doable with

evaluate() and some string manipulation involving {}...
I'm sure!

problem is with name objects that some remnants
(hidden namespace) stay in memory...
and may lead to problems.


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


Peter T said:
AhHa, thanks

Seems already elegant to me <g>

Peter

-----Original Message-----
i use:
Sub ff()
With ActiveWorkbook
.Names.Add "tmp", "=Documents(3)"
v = [tmp]
.Names("tmp").Delete
End With
End Sub

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


Peter T said:
Hi KeepItCool
Click to expand...
Click to expand...
<snip>
Click to expand...
 
P

Peter T

KeepITcool,
but it should be ..nah MUST be .. doable with

evaluate() and some string manipulation involving {}...
I'm sure!
Click to expand...

Hope you're right, I'm not so sure but I'll be watching.
problem is with name objects that some remnants
(hidden namespace) stay in memory...
and may lead to problems.
Click to expand...

I trust this only applies to a name that "refersto" an XLM,
I add/delete normal names all the time.

According to Laurent Longre:
http://www.cpearson.com/excel/hidden.htm
the "hidden namespace" is at application level so I assume
this is not related to a "normal" workbook level name, at
least I hope not!

Regards,
Peter
 
Top