Is it possible to use the vbscript/VBA ARRAY() function in calls to Excel using the OleCallMethod function?

I am trying to use the Excel Subtotal function. One of the paramters of this function are the columns you wish to subtotal which are passed as an array.

This command works and subtotals column 2

equ xlSum to -4157

stat=OleCallMethod(oRange, "Subtotal", 1, xlSum, 2, True, False, True)

However, if I wish to subtotal two columns, I must pass the column numbers as an array.

This command does not work. I am attempting to pass column numbers 2 and 3 as an ARRAY

equ xlSum to -4157

stat=OleCallMethod(oRange, "Subtotal", 1, xlSum, ARRAY(2,3), True, False, True)

This article provides more information but I'm not sure how to prototype this in OI.

http://www.4guysfromrolla.com/webtech/032801-1.shtml

Any help is appreciated.

Hank -

Found references to SafeArray, sounds like it would do the trick.

See http://archive.baarns.com/excel/faq/xd_plat1.asp

or

http://www.geocities.com/Jeff_Louie/safearray.html

as a starting point.

CodeProject has article shows how to use a scripting dictionary to pass arrays. See href=http://www.codeproject.com/useritems/ExcelReportsAjac.asp]http://www.codeproject.com/useritems/ExcelReportsAjac.asp

There are probably more examples out there. I googled "Vbscript SafeArray Excel Example"

HTH

Bob

Hank,

Another way is to write the formula and cell refences eg '=sum(':counterX:':':counterY:')' and place that at your total.

Some of my exports have multiple sums on the same column, the only way I've been able to keep track on my summing is by having counters all over the place.

Pascal

Thanks Bob,

I wasn't aware of a "safearray". I have looked at the links you provided. It appears the first step is prototyping the following Dll calls below. Any hint on how to prototype a variable type SAFEARRAY in OI.

Could this be a project for Revelation? I'm sure other people would be interested in this functionality.

WINOLEAUTAPI SafeArrayAllocDescriptor(UINT cDims, SAFEARRAY ppsaOut); WINOLEAUTAPI SafeArrayAllocDescriptorEx(VARTYPE vt, UINT cDims, SAFEARRAY ppsaOut);

WINOLEAUTAPI SafeArrayAllocData(SAFEARRAY * psa);

WINOLEAUTAPI_(SAFEARRAY *) SafeArrayCreate(VARTYPE vt, UINT cDims, SAFEARRAYBOUND * rgsabound);

WINOLEAUTAPI_(SAFEARRAY *) SafeArrayCreateEx(VARTYPE vt, UINT cDims, SAFEARRAYBOUND * rgsabound, PVOID pvExtra);

WINOLEAUTAPI SafeArrayCopyData(SAFEARRAY *psaSource, SAFEARRAY *psaTarget);

WINOLEAUTAPI SafeArrayDestroyDescriptor(SAFEARRAY * psa);

WINOLEAUTAPI SafeArrayDestroyData(SAFEARRAY * psa);

WINOLEAUTAPI SafeArrayDestroy(SAFEARRAY * psa);

WINOLEAUTAPI SafeArrayRedim(SAFEARRAY * psa, SAFEARRAYBOUND * psaboundNew);

WINOLEAUTAPI_(UINT) SafeArrayGetDim(SAFEARRAY * psa);

WINOLEAUTAPI_(UINT) SafeArrayGetElemsize(SAFEARRAY * psa);

WINOLEAUTAPI SafeArrayGetUBound(SAFEARRAY * psa, UINT nDim, LONG * plUbound);

WINOLEAUTAPI SafeArrayGetLBound(SAFEARRAY * psa, UINT nDim, LONG * plLbound);

WINOLEAUTAPI SafeArrayLock(SAFEARRAY * psa);

WINOLEAUTAPI SafeArrayUnlock(SAFEARRAY * psa);

WINOLEAUTAPI SafeArrayAccessData(SAFEARRAY * psa, void HUGEP ppvData); WINOLEAUTAPI SafeArrayUnaccessData(SAFEARRAY * psa); WINOLEAUTAPI SafeArrayGetElement(SAFEARRAY * psa, LONG * rgIndices, void * pv); WINOLEAUTAPI SafeArrayPutElement(SAFEARRAY * psa, LONG * rgIndices, void * pv); WINOLEAUTAPI SafeArrayCopy(SAFEARRAY * psa, SAFEARRAY ppsaOut);

WINOLEAUTAPI SafeArrayPtrOfIndex(SAFEARRAY * psa, LONG * rgIndices, void ppvData); WINOLEAUTAPI SafeArraySetRecordInfo(SAFEARRAY * psa, IRecordInfo * prinfo); WINOLEAUTAPI SafeArrayGetRecordInfo(SAFEARRAY * psa, IRecordInfo prinfo);

WINOLEAUTAPI SafeArraySetIID(SAFEARRAY * psa, REFGUID guid);

WINOLEAUTAPI SafeArrayGetIID(SAFEARRAY * psa, GUID * pguid);

WINOLEAUTAPI SafeArrayGetVartype(SAFEARRAY * psa, VARTYPE * pvt);

WINOLEAUTAPI_(SAFEARRAY *) SafeArrayCreateVector(VARTYPE vt, LONG lLbound, ULONG cElements);

WINOLEAUTAPI_(SAFEARRAY *) SafeArrayCreateVectorEx(VARTYPE vt, LONG lLbound, ULONG cElements, PVOID pvExtra);

Thanks Pascal,

This is a good work-a-round but there are also other cases where I would like to use the array function.

View this thread on the Works forum...