Re: MS Office COM Basics
Posted: 29 Apr 2018, 21:41
Hello, Anyone might have an idea how to properly set the TotalList parameter of the Range.Subtotal function.
Here is the msdn page for the function:
https://msdn.microsoft.com/en-us/vba/ex ... thod-excel
The page describes the parameter as a Variant type of "An array of 1-based field offsets, indicating the fields to which the subtotals are added. For more information, see the example."
Here's what I came up with:
Assigning the array works fine which means it really is of variant type, however the Subtotal command won't accept the array as a parameter. Anyone might have an idea to make this work?
Here is the msdn page for the function:
https://msdn.microsoft.com/en-us/vba/ex ... thod-excel
The page describes the parameter as a Variant type of "An array of 1-based field offsets, indicating the fields to which the subtotals are added. For more information, see the example."
Here's what I came up with:
Code: Select all
safeArr := ComObjArray(12, 2)
safeArr[0] := 5
safeArr[1] := 6
ws.Range("L2:M3").Value := safeArr
ws.Range("B1:F4649").Subtotal(1, -4157, safeArr)