Excel UPPER() / LOWER() instead of VBA UCase() or LCase()

[James Snyder]

I am working in a macro and for the sake of speed would like to use the Excel UPPER() function instead of VBA's UCase(). I am failing to find it among the Excel objects. I thought it might be part of WorksheetFunction, but that is not the case. Is it useable from VBA?

 

[Jonmo1]

If it's for the sake of speed, then it's faster to use functions that are native to VBA than it is to use Worksheet Functions.

 

[James Snyder]

Correct me if I am wrong, but since the Excel function is "in process", it runs faster than a VBA "out of process" function. From a website on Excel efficiency:

Worksheet Functions

You can use Excel's standard worksheet functions in your VBA code, rather than writing the functions in VBA. Since these are fully executable instructions in native code, rather than interpreted VBA code, they run much faster.

 

[par60056]

The difference is that the function already exists in VBA.

This is talking about writing a user function to mimic a worksheet function.

 

[taurean]

What you've quoted is correct but doesn't apply to the context you have. The quote refers to VBA function which mean UDFs (user defined functions) which tend to be slower than native excel functions. I am sure it doesn't apply to built-in / native functions and methods.

 

[James Snyder]

Thanks taurean and par60056. That is why I asked. The original question was whether UPPER could be used in VBA. I think the answer is "No". All I can find are examples of it in formulas. UCase it is!

 

[Firefly2012]

If it is just a question of can you use it then you can if you use it in conjunction with Evaluate:

MsgBox Evaluate("UPPER(""test upper case"")")

Note that this will be much slower than using UCase

 

[Jonmo1]

UPPER is not available to the WorksheetFunction class in VBA because there is an equivelant function native to VBA.

There are several functions not available to VBA for the same reason..Though I can't name them

 

[Rick Rothstein]

Thanks taurean and par60056. That is why I asked. The original question was whether UPPER could be used in VBA. I think the answer is "No". All I can find are examples of it in formulas. UCase it is!

Actually, UPPER can be used in place of UCase, but for the reasons taurean posted, it should not be used. Just to show you how, though (for information purposes only) where I just use a simplified selection for the range...

Selection = Evaluate("IF(LEN(" & Selection.Address & "),UPPER(" & Selection.Address & "),"""")")

 

[taurean]

I was referring to post#3. UPPER / LOWER functions are not available in VBA.