Using Excel functions in VBA

[lukeshuttlewood]

Hi,

Using the TODAY() function in an Excel spreadsheet cell at this moment in time would return something like 09/Nov/2005.

I want to know if it is possible to use excel functions in VBA without ever applying anything to the spreadsheet itself to get the return product.

I have tried this (below) in VBA.......I realy want to get just the 2 characters from the right of the product which would be = 05..............If it worked anyway!!!


ThisYear = Right("" & Application.WorksheetFunction.TODAY() & "", 2)

Does anyone know the syntax for this?

 

[Richard Schollar]

Hi Luke

You can do it like this:

ThisYear = EVALUATE("RIGHT(TEXT(TODAY()," & Chr(34) & "ddmmyy" & CHR(34) &"),2)")

or:

ThisYear = RIGHT(CSTR(EVALUATE("YEAR(TODAY())")),2)

Best regards

Richard

 

[lukeshuttlewood]

Cheers rich!

 

[tusharm]

VBA itself has many functions of its own, Date() being one of them. The worksheetfunction method supports only some XL functions, typically those missing from VB(A).

In your case Year(Date()) will do the job. No need for Evaluate(...)

Hi,

Using the TODAY() function in an Excel spreadsheet cell at this moment in time would return something like 09/Nov/2005.

I want to know if it is possible to use excel functions in VBA without ever applying anything to the spreadsheet itself to get the return product.

I have tried this (below) in VBA.......I realy want to get just the 2 characters from the right of the product which would be = 05..............If it worked anyway!!!


ThisYear = Right("" & Application.WorksheetFunction.TODAY() & "", 2)

Does anyone know the syntax for this?