Converting Excel Formula to VBA Script

[euge_prime2001]

Hi Guys,
I would like to ask your help how to convert these excel formula into VBA Script:
Beg_Date: =DATE(YEAR(TODAY())-1,10,1)
End_Date: =TODAY()
Week No: = Beg_Date-End_Date

Thanks a lot for your help!

Regards,
Eugene

 

[Joe4]

I will give you the tools you need and let you do it.

The DATE function in Excel is equivalent to the DATESERIAL function in VBA.
The TODAY() function in Excel is equivalent to the DATE function in VBA.
the YEAR function is the same in both.

 

[Scott T]

Week No: = Beg_Date-End_Date
This will give you a negative number is this what you want?

 

[euge_prime2001]

Sorry for that one.
Here's the correct excel formula
Beg_Date: =DATE(YEAR(TODAY())-1,10,1)
End_Date: =TODAY()
Week No: = End_Date-Beg_Date

Regards,
Eugene

 

[Joe4]

Sorry for that one.
Here's the correct excel formula
Beg_Date: =DATE(YEAR(TODAY())-1,10,1)
End_Date: =TODAY()
Week No: = End_Date-Beg_Date

Did you see my reply?
It is really just a simple substitution of using the function name equivalents I gave you.

 

[euge_prime2001]

Hi Joe,
Thanks for your help.
Here's what i did to convert the said formula into VBA script.

mBegDate = DateSerial(Year(Now()) - 1, 10, 1)
mEndDate = Format(Now(), "yyyymmdd hhmm am/pm")
mWKNum = mEndDate - mBegDate

Regards,
Eugene

 

[Joe4]

Why did you apply the FORMAT function to your mEndDate calculation?
You did not do that in your original calculations. Don't do that here. The FORMAT function returns a Text value, not a Date value.
If you do that, you won't be able to use it in other calculations, like mWKNum.

There is no need to use the FORMAT function here. If it is being displayed anywhere on your sheet, just format the cell (not the value).

 

[euge_prime2001]

You are correct Joe. I already removed the format function in the script.

 

[euge_prime2001]

Thanks for your help!

 

[Joe4]

You are welcome.

Note that the FORMAT function in VBA works the same way as the TEXT function does in Excel.