asked on

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

balatheexpert

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

kalbano

ASKER

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)

balatheexpert

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?

kalbano

ASKER

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.

balatheexpert

and to refer to a cell in a formula, i dont think we can write code which is even worse than writing the formulas!!!

dlmille

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

kalbano

ASKER

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?

dlmille

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

kalbano

ASKER

I dont see anything different in your last example.....

dlmille

Sorry - add line 7 - Application.Volatile - to the function
See attached...

Dave
MUcalc-for-ee-r1.xls

kalbano

ASKER

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

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kalbano

ASKER

Thanks much!