Defined Named cells lined to a Function Procedure
 

Hi All,

I have a problem with an excel funtion that i created, i have a series of
defined named range pointing to various cells, however, i cannot seem to call
them out within my procedure. Could anyone please help? It is like something
below:


Function CCY_CALC(PosVal, CCY)

CCYType = CCY

Select Case CCYType
Case "AUD"
' AUD_USD is the defined name which points to a cell with the amount of 1.3

RateD = CCY_CALC * AUD_USD

Case "CAD"
' AUD_USD is the defined name which points to a cell with the amount of 1.01

RateD = CCY_CALC * CAD_USD

End Select


CCY_CALC = RateD

End Function

Thanks in advance
Ray

Hi all,

Sorry slight error in my code, it should be as follows:



Function CCY_CALC(PosVal, CCY)

CCYType = CCY

Select Case CCYType
Case "AUD"
' AUD_USD is the defined name which points to a cell with the amount of 1.3

RateD = PosVal * AUD_USD

Case "CAD"
' AUD_USD is the defined name which points to a cell with the amount of 1.01

RateD = PosVal * CAD_USD

End Select


CCY_CALC = RateD

End Function

Hi SwiftCode,

Change:

RateD = PosVal * AUD_USD
to
RateD = PosVal * Range("AUD_USD")

and change:

RateD = PosVal * CAD_USD
to
RateD = PosVal * Range("CAD_USD")

---
Regards,
Norman



"swiftcode" wrote in message
...
Hi all,

Sorry slight error in my code, it should be as follows:



Function CCY_CALC(PosVal, CCY)

CCYType = CCY

Select Case CCYType
Case "AUD"
' AUD_USD is the defined name which points to a cell with the amount of
1.3

RateD = PosVal * AUD_USD

Case "CAD"
' AUD_USD is the defined name which points to a cell with the amount of
1.01

RateD = PosVal * CAD_USD

End Select


CCY_CALC = RateD

End Function

Hi SwiftCode,

And that would be better with:

RateD = PosVal * Range("AUD_USD").Value

and

RateD = PosVal * Range("CAD_USD").Value

Whilst Value is the default property of the range object, it is good
practice to be explicit.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi SwiftCode,

Change:

RateD = PosVal * AUD_USD
to
RateD = PosVal * Range("AUD_USD")

and change:

RateD = PosVal * CAD_USD
to
RateD = PosVal * Range("CAD_USD")

---
Regards,
Norman



"swiftcode" wrote in message
...
Hi all,

Sorry slight error in my code, it should be as follows:



Function CCY_CALC(PosVal, CCY)

CCYType = CCY

Select Case CCYType
Case "AUD"
' AUD_USD is the defined name which points to a cell with the amount of
1.3

RateD = PosVal * AUD_USD

Case "CAD"
' AUD_USD is the defined name which points to a cell with the amount of
1.01

RateD = PosVal * CAD_USD

End Select


CCY_CALC = RateD

End Function

Hi Norman,

Thank you very much.

Rgds
Ray