Defined Named cells lined to a Function Procedure

[swiftcode]

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

[swiftcode]

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

[Norman Jones]

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" <swiftcode@discussions.microsoft.com> wrote in message
news:D0757E5A-2FFA-43B4-892A-2CF903B1B344@microsoft.com...
> 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
>

[Norman Jones]

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" <normanjones@whereforartthou.com> wrote in message
news:egD0bEjzFHA.3812@TK2MSFTNGP09.phx.gbl...
> 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" <swiftcode@discussions.microsoft.com> wrote in message
> news:D0757E5A-2FFA-43B4-892A-2CF903B1B344@microsoft.com...
>> 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
>>
>
>

[swiftcode]

Hi Norman,

Thank you very much.

Rgds
Ray