Accessing Dynamic Named Ranges in VBA

0
votes

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.

2
vbamatlabexcel
hi, do you have any issue using tables in excel? that gives you automatically a named dynamic range. pls see this video: youtube.com/watch?v=uLJEMnIT0zM it explains how to use a dynamic range name. hope it helps! - Hadi

2 Answers

1
votes
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
1
votes

It's not true that formula-defined named ranges can't be used in VBA. If you're having a problem doing that then there may be a problem with your formula.

A typical way to reference the range would be something like:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),2)