You can use the following method to refer a named range just like you mention a range:

Range("DosePerField").Cell

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

Solved

Posted on 2011-04-25

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

13 Comments

You can use the following method to refer a named range just like you mention a range:

Range("DosePerField").Cell

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

(Sorry about saying 8 columns. Its only 6)

```
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
```

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

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
```

Cheers,Dave

See attached...

Dave

MUcalc-for-ee-r1.xls

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

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:

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
```

Enjoy!Dave

MUcalc-for-ee-r2.xls

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Determine Range to Select | 5 | 26 | |

excel - employee availability remaining after scheduled | 7 | 34 | |

Match Values and Return to Cell | 6 | 17 | |

MS Excel default colors in styles | 2 | 12 |

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

Connect with top rated Experts

**22** Experts available now in Live!