passing defined name to vb

[bmeehan]

i have a large list of defined names which are various currency an
futures prices. is there a way to have a subroutine or function acces
these values w/o having to pass each one as am argument. depending o
the value entered into a cell i want to access a different define
name. for example, if a cell has EurGbp entered in it i want to acces
the EUR and GBP named range from the function. I have seen examples o
how to access a single defined range fro ma formula but not a large an
growing list of names. thanks

 

[Tom Ogilvy]

assume EurGbp is in Cell A1 of the active sheet

set rng = Range(Left(Range("A1").Value,3))
set rng1 = Range(Right(Range("A1").Value,3))
msgbox rng.Address(External:=True) & vbNewLine & _
rng1.Address(External:=True)

 

[Bob Phillips]

An example

Function myCalc(currencies)
Dim i As Long
Dim temp

For i = 1 To Len(currencies) Step 3
temp = Range(Mid(currencies, i, 3)) + temp
Next i

myCalc = temp
MsgBox myCalc

End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)