?
Solved

referring to named ranges in excel vba modules

Posted on 2011-04-25
13
Medium Priority
?
566 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:kalbano
  • 6
  • 4
  • 3
13 Comments
 
LVL 2

Expert Comment

by:balatheexpert
ID: 35460014
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
 

Author Comment

by:kalbano
ID: 35460284
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
 
LVL 2

Expert Comment

by:balatheexpert
ID: 35460442
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:kalbano
ID: 35460498
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
 
LVL 2

Expert Comment

by:balatheexpert
ID: 35461365
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
 
LVL 42

Expert Comment

by:dlmille
ID: 35464662
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
 

Author Comment

by:kalbano
ID: 35468796
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
 
LVL 42

Expert Comment

by:dlmille
ID: 35468957
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
 

Author Comment

by:kalbano
ID: 35471542
I dont see anything different in your last example.....
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35472198
Sorry - add line 7 - Application.Volatile - to the function
See attached...

Dave
MUcalc-for-ee-r1.xls
0
 

Author Comment

by:kalbano
ID: 35474586
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
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 35476601
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
 

Author Closing Comment

by:kalbano
ID: 35498189
Thanks much!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question