VBA Coding - Where to find helpful methods

Peter B

I am fairly new to VBA. I am using it for creating macros with Microsoft Excel. I have been working with PHP for a while now and love the easy access to lots of different methods already built in to PHP.

For example I am working a lot with dates in the task I am doing now.

One thing that bugs me is that I cannot find a simple function to convert and display dates in different ways. I have been googling for ages and finally found out how to display a month name using a function taking the month number as a parameter. I want to find more ways to change the look of dates easily. eg from "01/10/2008" to "October, the 1st 2008"

Also I want to be able to display a number with its suffix automatically chosen such as "31st", "2nd", "3rd", or "10th". I know I can create this function quickly and easily but would ideally like to know are these functions out there in VBA and if they are where could I find them.

Thanks

Eoin

This page might help you with the date formatting, or just google FormatDateTime().

You need to find a VBScript reference - I used to have one in CHM format, but I don't think I took it with me when I left my last job. I used it when I was coding in ASP, and I assume there are no major differences.

Edit - check out this download from MS:
http://www.microsoft.com/downloads/details.aspx?familyid=01592C48-207D-4BE1-8A76-1C4099D7BBB9&displaylang=en

BostonB

I think you are really looking for the Excel Object Model.
Office 2003 Editions: Excel VBA Language Reference
http://www.microsoft.com/downloads/details.aspx?familyid=2204a62e-4877-4563-8e83-4848dda796e4&displaylang=en

Eoin

Is all that stuff not VB Script?

BostonB

I'm no expert but no.

http://www.natecsystems.com/98%20Sep-Oct%20VBA%20vs%20Visual%20Basic.html

Eoin

Ah right. Well, the article says that VB has the same syntax, so the link I provided should still help for the basic functions.

BostonB

Indeed but the power in Excel Macros is programming the object model. Theres a learning curve, so to save time I'd hit an Excel VBA forum. I used to use VBCity and ExpertsExchange.

Peter B

Cheers for the replies.

Just a simple example would be

Change "21/11/08" to "21st of November, 2008".

The FormatDateTime method does not seem to be able to do this????

Anyone know how you would code it?

FSL

Using 'dd mmmm, yyyy' will give you 21 November, 2008 but I don't know any format which would return the 'st' 'nd' 'rd' or 'th' after the number. you would need to code your own format function to include it in the string produced by the format statement.

The reason it isn't included is probably because the abbreviations are language dependent. e.g. 1st 1er etc.

none

VB was MS RAD that grew out of old BASIC for DOS. VBA was an almost full-featured VB engine executed inside MS Office applications. VBScipt is a trimmed down scripting version of VB to be executed either by the IE browser or by the Windows shell or by the IIS server. One of the sacrifices in VBScript was an almighty Format function. In VBA it is surely available but even it can't give you what you want ("st", "nd", etc.).

BostonB

This any use?

http://www.techonthenet.com/excel/formulas/format_date.php