In Excel, I have a column as follows, as part of a workbook template:
Date Asset Return
1/3/2005 0.003582399
1/4/2005 -0.01908258
1/5/2005 0.002080625
1/6/2005 0.005699497
1/7/2005 -0.008040505
1/10/2005 -0.00339116
1/11/2005 -0.009715187
1/12/2005 0.002371855
1/13/2005 -0.00580783
1/14/2005 0.001058481
1/18/2005 0.015483842
1/19/2005 -0.014690715
1/20/2005 -0.015714799
1/21/2005 -0.010796326
I'm using Excel as the user interface. A user will put data into Excel, and then it'll be read in from another program (Matlab) which will do calculations.
To do this, I use a VBA command to send it to Matlab and refer to the range directly or by naming the range:
MLPutMatrix "VARIABLE_NAME", Range("B8:B2954")
or
MLPutMatrix "VARIABLE_NAME", Range("NamedRange")
My problem is that I don't know what the size of the range will be in advance, since the number of rows will be different depending on what data the user puts into the Excel range.
I tried to create a named range that refers to the last non blank row by using this formula :
Sheet1!$B$8:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$B:$B)+8)
However, apparently named ranges that are defined with a formula don't show up in the name box and can't be used in VBA scripts, so this does not work. I need to manually create a name for it to show up in the name box and access it with VBA, but of course I can't do this if I don't know my range dimensions in advance.
So my problem is: I need to find some way to be able to refer to a dynamic range either by name or by its size, and then be able to refer to it in VBA so that I can send it to Matlab using the "MLPutMatrix" command.
With Worksheets("mydata") '<--| change "mydata" with your actual sheet name
MLPutMatrix "VARIABLE_NAME", .Range("B2", .Cells(.Rows.Count, "B").End(xlUp)) '<--| change "B2" to your actual first cell and "B" to your actual column data index
End With
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments