Hi,
I am trying to get an array from an excel range like this:
var ArrNr() as Variant
redim ArrNr(LastRow - FirstRow + 1)
ArrNr = WS.Range(“B” + sFirstRow, “B” + sLastRow).Value
redim or not, array is not filled while the vice versa way does work meaning I can fill a range from a Xojo array.
Trying with Collection I get error: “Array cannot be cast to Collection”.
Any idea what I missed?
Going via
dim VArrNr as Variant
gives back an OLEobject which TypeName is not shown nor do I know if this is available as Excel*-Type.
I must otherwise change the approach and do all calculations in Xojo and write later all arrays to Excel.
But, if I come into the situation needing to read lots of rows, a solution to this problem would be helpful!
Here is one example, where two columns of data are created in Excel, and column B has the number of data rows counted, and then an array (ArrNr) is populated with data from the Excel column.
Your first example does the slow cell by cell approach which takes to long for some thousands of rows.
But great example anyway!
The second shows the load all approach; I will check if transpose is needed or if Range is enough.
I saw with my code that a row (“B2:E2”) worked but not a column (“B2:B6”); but if transpose would do the trick → great!
The 0-based array hint → yes: VBA arrays are one based by default.
To manipulate large amounts of Excel Data fast, then return filtered data extracts to populate a ListBox/WebListBox or report in Xojo, I call PowerShell scripts using Shell.Execute in Xojo.
