Altering a macro with a macro

[TheIrishThug]

i'm writing a conversion rate function. since converstion rates change everyday. i want to be able to update the macro. it doesn't need to be extremely accurate, just make sure that if its been, say a week, that the program will ask me to reset the conversion rate. this is going to be run alot so i'd rather not have the user input it everytime.
i was thinking something along the lines of:

Function ConvertEuro(WholeLine As String) As Double
Dim DateUpdated As Date
Dim ConverstionRate As Double

DateUpdated = 12/29/05
ConversionRate= 1.81

if Now()-DateUpdated > [one week] then
[at this point it probably needs to break the function and then call a sub that will alter the function. altering "DateUpdated = 12/29/05" to "DateUpdated = NOW()" and "ConversionRate= 1.81" to "ConversionRate= newRate" where newRate is the new rate that the user inputed. and then the sub would restart ConvertEuro]
end if
ConvertEuro = ConversionRate * CDbl(WholeLine)
end function

everything in []'s is what i need help coding. thanks

[Bob Phillips]

I would use a worksheet cell to hold the rate, and make it a user procedure
to change it once a week. Much simpler.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"TheIrishThug" <[email protected]>
wrote in message
news:[email protected]...
>
> i'm writing a conversion rate function. since converstion rates change
> everyday. i want to be able to update the macro. it doesn't need to be
> extremely accurate, just make sure that if its been, say a week, that
> the program will ask me to reset the conversion rate. this is going to
> be run alot so i'd rather not have the user input it everytime.
> i was thinking something along the lines of:
>
> Function ConvertEuro(WholeLine As String) As Double
> Dim DateUpdated As Date
> Dim ConverstionRate As Double
>
> DateUpdated = 12/29/05
> ConversionRate= 1.81
>
> if Now()-DateUpdated > [one week] then
> [at this point it probably needs to break the function and then call a
> sub that will alter the function. altering "DateUpdated = 12/29/05" to
> "DateUpdated = NOW()" and "ConversionRate= 1.81" to "ConversionRate=
> newRate" where newRate is the new rate that the user inputed. and then
> the sub would restart ConvertEuro]
> end if
> ConvertEuro = ConversionRate * CDbl(WholeLine)
> end function
>
> everything in []'s is what i need help coding. thanks
>
>
> --
> TheIrishThug
> ------------------------------------------------------------------------
> TheIrishThug's Profile:
http://www.excelforum.com/member.php...o&userid=29682
> View this thread: http://www.excelforum.com/showthread...hreadid=496761
>

[HSalim[MVP]]

You don't need to edit the macro - if you plan it correctly.

You can store the DateUpdated in a number of places
a. separate worksheet or workbook, located on say a shared netwrok drive
b. in the Windows Registry
c. in the currrent workbook in a custom document property.

But best of all, - You can subscribe to WebService to get a daily (or even
more frequently if you prefer) exchange rate. You can then have your macro
log in to the web service and retrieve the rate, perhaps even store a
history of exchange rates

HS

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
:I would use a worksheet cell to hold the rate, and make it a user procedure
: to change it once a week. Much simpler.
:
: --
:
: HTH
:
: RP
: (remove nothere from the email address if mailing direct)
:
:
: "TheIrishThug" <[email protected]>
: wrote in message
: news:[email protected]...
: >
: > i'm writing a conversion rate function. since converstion rates change
: > everyday. i want to be able to update the macro. it doesn't need to be
: > extremely accurate, just make sure that if its been, say a week, that
: > the program will ask me to reset the conversion rate. this is going to
: > be run alot so i'd rather not have the user input it everytime.
: > i was thinking something along the lines of:
: >
: > Function ConvertEuro(WholeLine As String) As Double
: > Dim DateUpdated As Date
: > Dim ConverstionRate As Double
: >
: > DateUpdated = 12/29/05
: > ConversionRate= 1.81
: >
: > if Now()-DateUpdated > [one week] then
: > [at this point it probably needs to break the function and then call a
: > sub that will alter the function. altering "DateUpdated = 12/29/05" to
: > "DateUpdated = NOW()" and "ConversionRate= 1.81" to "ConversionRate=
: > newRate" where newRate is the new rate that the user inputed. and then
: > the sub would restart ConvertEuro]
: > end if
: > ConvertEuro = ConversionRate * CDbl(WholeLine)
: > end function
: >
: > everything in []'s is what i need help coding. thanks
: >
: >
: > --
: > TheIrishThug
: > ------------------------------------------------------------------------
: > TheIrishThug's Profile:
: http://www.excelforum.com/member.php...o&userid=29682
: > View this thread:
http://www.excelforum.com/showthread...hreadid=496761
: >
:
:

[TheIrishThug]

ok, thanks guys. i think i'm gonna tape "Keep It Simple Stupid" on my computer from now on.