ExcelApplication; Worksheet; Array from Range

Xojo 2021 Release 1

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.

Any idea really appreciated!

(How does formatting code work???)

Array is 2-dimensional…

Still does not work…

If you don’t get an answer in the next 12 hours then I’ll take a look at it. That’s when I’ll be back near my development computer. :slight_smile:

Cool… Thanks a lot!!!

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!

Hello Hans,

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.

Is this similar to what you would like?

Here is a faster way to use a range by using transpose:

1 Like

Hello Eugine,
many thanks for the examples!

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.

Ok, will check this.

Many thanks again!!!

1 Like

Can get it to work as needed.

ValueArray2D does also not work.

Another idea? No one reading 2 dimensional array from Excel;-)

Range(“B1:B6”).Value itself gives back an OLEObject but I don’t know what kind.
There must be another wrapper between VBA jagged array and Xojo array.

The way vice versa as shown above does work… annoying.

Ok, copying via clipboard works!


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.

Kind regards, Andrew

Yes, using something from outside of Xojo seems a good idea.

Thanks for the idea/solution!