referring to named ranges in excel vba modules

I have an excel spreadsheet with coding mixed between vba modules and excel functions.  The spreadsheet has up to 8 columns that can be used.  Several of the rows have named ranges. I found that I could perform a calculation for each column by referring to the named range (energy) in an excel formula.  The formula has nested if-then-else statements which I would rather write in a vba module.  I would like to use the named range in the vba function. however I cant find a way to reference the named range.  Also the spreadsheet is about to get more complicated as I will be adding a new row (as well as new worsksheets with additional tables) that will increase the choices that must be made.  I am reluctant to write the excel formula with even more nested if-then-else statements.   MUcalc-for-ee.xls
kalbanoAsked:
Who is Participating?
 
dlmilleConnect With a Mentor Commented:
Its not a huge deal, especially giving processing speeds.

When you write a UDF - User Defined Function (e.g., without the Application.Volatile command), the UDF only calculates when one of its input parameters change.

e.g., your function:

=Linearinter22d(param1, param2.Value, param3.Value)

would update anytime param1, param2, or param3 values changed.

With Application.Volatile, the function would update anytime the spreadsheet calculated.

Here's more on Application.Volatile:http://www.decisionmodels.com/calcsecretsi.htm

Recall, you were looking for a way to reference a range and the current cell/column where your function resides.  I provided that, with additional commentary that you can then improve on this function, should you choose to do so, to possibly eliminate the need for the Application.Volatile statement.

However, that begins perhaps to defeat the purpose of "simplifying the equations" in Excel Formulas and coding in VBA, which was your insight/decision.  If you start putting those parameters back into the function, the result could be just as complex an excel formula as when you started!  :)

I fixed a bug in my example - note the set param2, param3 needs to reference the destination cell's column.
 
Function testTMR(rng As Range) As Variant
Dim rngEnergy As Range
Dim param1 As Range, param1alt As Range, param2 As Range, param3 As Range

'formula to mimic with VBA:
'=IF(B6<>"",IF(Energy=6,Linearinter22d('6 tmr'!$B$3:$AB$44,B22,B15),Linearinter22d('18 tmr'!$B$3:$P$65,B22,B15)),"")

    Application.Volatile
    
    Set param1 = Sheets("6 tmr").Range("$B$3:$AB$44")
    Set param1alt = Sheets("18 tmr").Range("$B$3:$P$65")
    Set param2 = Cells(22, rng.Column)
    Set param3 = Cells(15, rng.Column)
    
    Set rngEnergy = Range("Energy")
    If Cells(6, rng.Column).Value <> "" Then 'check row 6 in the current column
        If Cells(rngEnergy.Row, rng.Column) = 6 Then 'check energy range's row, and current column
            testTMR = Linearinter22d(param1, param2.Value, param3.Value)
        Else
            testTMR = Linearinter22d(param1alt, param2.Value, param3.Value)
        End If
    Else
        testTMR = ""
    End If
        
End Function

Open in new window

Enjoy!

Dave
MUcalc-for-ee-r2.xls
0
 
balatheexpertCommented:
Hi,

You can use the following method to refer a named range just like you mention a range:
Range("DosePerField").Cells.Count

If you display this, it will show 8 which is the number of cells in that named range!!

Let me know if this helps out.

thanks,
Bala
0
 
kalbanoAuthor Commented:
Thanks, I had discovered that this sort of reference does in fact work.  however, if you look at the tmr formula on MU!B31:G31 you will see the formula references the energy range B11:G11.  This is the formula I want to write in vba code using select case.  I want the vba code to pick the correct table by referring to the energy in the same column.  Is there a way that the formulas in row 31 can pick the energy from the same column?
(Sorry about saying 8 columns.  Its only 6)
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
balatheexpertCommented:
no, you cant refer a sub range of a defined name with multiple cells! u can directly refer to the cell right? what's the point of referring to a named range?
0
 
kalbanoAuthor Commented:
Yes, I can refer to the cell directly.  Since the range is already named I thought it would be helpful to use the named ranges I already had.  
0
 
balatheexpertCommented:
and to refer to a cell in a formula, i dont think we can write code which is even worse than writing the formulas!!!

0
 
dlmilleCommented:
Try this - I created a function with VBA for the tmr row, to demonstrate:

 
Function testTMR(rng As Range) As Variant
Dim rngEnergy As Range
Dim param1 As Range, param1alt As Range, param2 As Range, param3 As Range

'formula to mimic with VBA:
'=IF(B6<>"",IF(Energy=6,Linearinter22d('6 tmr'!$B$3:$AB$44,B22,B15),Linearinter22d('18 tmr'!$B$3:$P$65,B22,B15)),"")

    Set param1 = Sheets("6 tmr").Range("$B$3:$AB$44")
    Set param1alt = Sheets("18 tmr").Range("$B$3:$P$65")
    Set param2 = Range("B22")
    Set param3 = Range("B15")
    
    Set rngEnergy = Range("Energy")
    If Cells(6, rng.Column).Value <> "" Then 'check row 6 in the current column
        If Cells(rngEnergy.Row, rng.Column) = 6 Then 'check energy range's row, and current column
            testTMR = Linearinter22d(param1, param2.Value, param3.Value)
        Else
            testTMR = Linearinter22d(param1alt, param2.Value, param3.Value)
        End If
    Else
        testTMR = ""
    End If
        
End Function

Open in new window


See attached, which uses that function at cell locations - MU!B31:G31

It should work, but without data (or spending time making up data), I didn't test the veracity of the function.  As you're well aquainted, give it a shot.

The goal here was to demonstrate that you can subreference a range (e.g., give me the current value at the current column of a range which is multiple columns wide, etc.)

Enjoy!

Dave
MUcalc-for-ee-r1.xls
0
 
kalbanoAuthor Commented:
Thanks!  It works very nicely.  There is one strange thing.  The tmr cell does not fill in until I put the cursor in the formula bar and enter.  Any way to make it fill automaticly?  
0
 
dlmilleCommented:
you could put the command:  Application.Volatile in the function, which forces execution on calculation.

Because of the way its written, you could put:  Application.Volatile, in the function, to force execution on calculation.

If you're further developing functions like these, I recommend doing -> The other alternative, is to have parameters that go to the function (instead of current cell) referencing all the data ranges that the function uses.  That way, when those values change, then the UDF (User Defined Function) would calculate....  More efficient than forcing executions on calculate event, re: Application.Volatile.

code:
 
Function testTMR(rng As Range) As Variant
Dim rngEnergy As Range
Dim param1 As Range, param1alt As Range, param2 As Range, param3 As Range

'formula to mimic with VBA:
'=IF(B6<>"",IF(Energy=6,Linearinter22d('6 tmr'!$B$3:$AB$44,B22,B15),Linearinter22d('18 tmr'!$B$3:$P$65,B22,B15)),"")
    Application.Volatile
    Set param1 = Sheets("6 tmr").Range("$B$3:$AB$44")
    Set param1alt = Sheets("18 tmr").Range("$B$3:$P$65")
    Set param2 = Range("B22")
    Set param3 = Range("B15")
    
    Set rngEnergy = Range("Energy")
    If Cells(6, rng.Column).Value <> "" Then 'check row 6 in the current column
        If Cells(rngEnergy.Row, rng.Column) = 6 Then 'check energy range's row, and current column
            testTMR = Linearinter22d(param1, param2.Value, param3.Value)
        Else
            testTMR = Linearinter22d(param1alt, param2.Value, param3.Value)
        End If
    Else
        testTMR = ""
    End If
        
End Function

Open in new window

Cheers,

Dave
0
 
kalbanoAuthor Commented:
I dont see anything different in your last example.....
0
 
dlmilleCommented:
Sorry - add line 7 - Application.Volatile - to the function
See attached...

Dave
MUcalc-for-ee-r1.xls
0
 
kalbanoAuthor Commented:
Actually you had inserted the application.volatile in a previous version.  It is a very helpful statement. In your last reply you mentioned :
The other alternative, is to have parameters that go to the function (instead of current cell) referencing all the data ranges that the function uses.  That way, when those values change, then the UDF (User Defined Function) would calculate....  More efficient than forcing executions on calculate event, re: Application.Volatile.

Could you give me more description?  Thanks

0
 
kalbanoAuthor Commented:
Thanks much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.