passing defined name to vb
 

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

--
Message posted from http://www.ExcelForum.com

passing defined name to vb
 

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)

--
Regards,
Tom Ogilvy

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


---
Message posted from http://www.ExcelForum.com/

passing defined name to vb
 

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)

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


---
Message posted from http://www.ExcelForum.com/