Define Range in Function

mst3k4L

Board Regular
Joined
Nov 3, 2011
Messages
55
I recently changed a sub to a function, and the function no longer works.
Code:
Function Bo(p) As Double
    Dim BoCoeff As Variant

' Start at bottom and looks for last filled cell
    Sheet2.Range("A9", Range("A65536").End(xlUp)).Name = "RangeA"
    Sheet2.Range("B9", Range("B65536").End(xlUp)).Name = "RangeB"
    
    BoCoeff = Evaluate("LinEst(RangeB, RangeA^{1,2,3})")
    Bo = p ^ 3 * BoCoeff(1) + p ^ 2 * BoCoeff(2) + p ^ 1 * BoCoeff(3) + BoCoeff(4)
'    Below only used in debugging
'    Bo = p ^ 3 * Sheet2.Range("A3") + p ^ 2 * Sheet2.Range("B3") + p ^ 1 * Sheet2.Range("C3") + Sheet2.Range("D3")

   

End Function
If I comment out the definition of RangeA and RangeB and use the new definition of Bo it works, so how should I define RangeA and RangeB?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Why do you need to create the named ranges through vba? You could simply create dynamic ranges, which you then refer to in your code.
 
Upvote 0
You could do it like this, but Excel will not see a dependency on Sheet2, so the results will not update if it changes:
Code:
Function Bo(p As Double) As Double
    Dim sY          As String
    Dim sX          As String
    Dim ad          As Variant
 
    With Sheet2
        sX = .Range("A9", .Cells(.Rows.Count, "A").End(xlUp)).Address
        sY = .Range(sX).Offset(, 1).Address
        ad = .Evaluate("LinEst(" & sY & ", " & sX & "^{1,2,3})")
        Bo = ad(1) * p ^ 3 * ad(2) * p ^ 2 + ad(3) * p + ad(4)
    End With
End Function

For that reason, I'd do this instead:

Code:
Function Bo(p As Double, rX As Range, rY As Range) As Double
    Dim sY          As String
    Dim sX          As String
    Dim ad          As Variant
 
    sX = Range(rX(1), rX(rX.Count).End(xlUp)).Address(External:=True)
    sY = rX.Offset(, 1).Address(External:=True)
 
    ad = Evaluate("LinEst(" & sY & ", " & sX & "^{1,2,3})")
    Bo = ad(1) * p ^ 3 * ad(2) * p ^ 2 + ad(3) * p + ad(4)
End Function
... called as

=Bo(A1, Sheet2!A9:A65536, Sheet2!B9:B65536)

I didn't test either one.
 
Upvote 0
Why do you need to create the named ranges through vba? You could simply create dynamic ranges, which you then refer to in your code.
I tried that idea, but I was still having problems.

Code:
Function Bo(p) As Double
    Dim BoCoeff As Variant
    
'Using predefined dynamic ranges
    BoCoeff = Evaluate("LinEst(Bo_Data, Bo_Pressure^{1,2,3})")
    Bo = p ^ 3 * BoCoeff(1) + p ^ 2 * BoCoeff(2) + p ^ 1 * BoCoeff(3) + BoCoeff(4)

End Function
With Bo_Pressure and Bo_Data defined as:

Code:
=OFFSET(Data!$A$8,0,0,COUNT(Data!$A$8:$A$65536),1)
=OFFSET(Data!$B$8,0,0,COUNT(Data!$B$8:$B$65536),1)

I double checked these dynamic ranges, and they do appear to give accurate areas, but I still get an "#VALUE!" error. What am I missing?
 
Upvote 0

Forum statistics

Threads
1,223,428
Messages
6,172,043
Members
452,444
Latest member
ShaImran193

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top