initialize a global matrix by reading data from excel files

[Adahm]

I would like to initialize from vba a global matrix with data read from an excel file.
Is that possible?

the reason that prompts me to search for this solution is that by connecting the excel file to user parameters, you can not read string data or boolean, but only numerical data.

 

[Adahm]

I understand something, but I can't reach the solution.
the problem is to define the matrix without defining its size and then dimension it as I read the excel rows.

At the moment I arrived here:

'I define the global variant variable because data can be string, boolean or numeric
global vexceldata as variant

private sub define_vexceldata()

'...after opening the excel file

Irow = 1
icol = 1
do while ws.cells(irow, icol) < > "
redim vexceldata(irow, 3)
for n = 1 to 3
♪
vfileexceldata(irow, icol) = ws.cells(irow, icol)
next
irow = irow + 1
loop
end

according to me does not work the redim preserve
Does anyone know anything about it?

 

[Adahm]

if someone can serve I found the problem.
you can only vary the last size of the array, in this case the second that I define as column.
I solved by reversing the matrix in this way:

 ...
redim preserve vexceldata(3, irow)
...

Thus, the rows I read in the excel file become columns and row columns.

 

[Mito125]

I was thinking about a solution. I haven't had a chance to try it yet and test it. but if you define the matrix looking for the last row and last column of the sheet? so you should not use redim. In this way you would have your matrix to fill. I'm not very expert on vba.

 

[Adahm]

thanks for the suggestion.
even if I don't know how to find the last row and column in the sheet, you know how?

 

[Mito125]

VBA Code To Find Last Row Or Last Column (Best Way)

Discover the best methods to make your VBA code dynamic by coding in a way that allows your macros to automatically expand and collapse based on the size of each individual data set. This can be done by finding the Last Row or Last Column of your particular data range.

www.thespreadsheetguru.com

I'm not very expert on vba, but I would have used one of the systems here presented. If you tell me how your excel sheet is formed I can try to create the matrix from the values present.