Formulas to macris

[vilas desai]

Dear Sirs,

Is there a guide / document on the equivalent macro expressions for Formulas.
For ex, what is the macro equivalent of MATCH or INDEX or RIGHT etc.
I am looking for a complete list of Excel Formulas in Col A and the macro equivalent expressions in Col B.

Thanks in advance

Vilas Desai

[HansV]

It woukd be difficult to provide a complete list.

Some Excel functions have a direct equivalent in VBA. For example, the worksheet functions LEFT, MID and RIGHT correspond to the Left, Mid and Right functions in VBA.
You have to be aware of some differences. For example, MOD is a function in Excel, and it works both with whole numbers and numbers with decimals:
=MOD(8, 1.5) returns 0.5
In VBA, Mod is not a function but an operator, for example 18 Mod 5 returns 3. And Mod works with whole numbers only.

Other functions don't have a "native" VBA equivalent, but can be used through the Application.WorksheetFunction object. For example, there is no direct equivalent to VLOOKUP in VBA, but you can use Application.WorksheetFunction.VLookup:

Dim varResult
varResult = Application.WorksheetFunction.VLookup(Range("A1").Value, Range("D1:E100"), 2, False)

computes the result of =VLOOKUP(A1,D1:E100,2,False)

And yet other functions do not have a direct equivalent at all, for example the INDIRECT function.

[vilas desai]

So that almost means Excel Functions and Excel Macros do not relate to each other, except that VBA can use some WorksheetFunction and that too is not straight forward.
Thank you so much for your clarification.
Best Regards
VD

[sdckapr]

I am sure Hans is aware of this, but some other comments on VBA vs Excel functions: some have the same name and different function, for example TRIM which in VBA removes, leading and trailing spaces from a text string. In Excel, the function not only removes the leading and trailing spaces, but removes extra spaces within the text string itself. There are also functions that have different names and essentially the same function: FORMAT in VBA is equivalent to TEXT function in Excel.

Steve

[HansV]

Yes, there are many subtle and not-so-subtle differences.

Another example: the VBA equivalent of DATE(year, month, day) is DateSerial(year, month, day). The VBA function Date returns the current date, just like the Excel function TODAY().