Application.WorksheetFunction.UPPER ()

  • Thread starter Thread starter Leif Rasmussen
  • Start date Start date
L

Leif Rasmussen

Hi
Can some one tell me, have I use these syntax. When
recording a function from Excel (for uppercase) I get
"UPPER()" but I get a suntax error imediately if using it
as: UPPER(myname).

Application.WorksheetFunction.UPPER (myname) returns an
error at the moment of runtime. (UCASE() will do the job)

But how to use the built in functions in general ???
 
Leif, in general it is used like this =UPPER(A1) to change whats in A1 to
upper case if "myname" is a named range that should also work like this
=UPPER(myname)

--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
But what to do with Excels build in functions if they is
not a range object, but the content of a variable.
 
Generally, IF the function is one that is allowed, I use

application.sum(range("a1:a21")
or
application.proper(range("a1"))
or
application.proper([a1])
but vbproper would still be better.
 
Go into the VBE and look in VBA's help for:
"List of Worksheet Functions Available to Visual Basic"

You'll see UPPER is missing.

You could also use strconv() inside VBA to convert between cases, too.

When I work with worksheet functions inside VBE, I (almost) always drop the
..worksheetfunction portion.

There's a few functions where
application.worksheetfunction.xxx and application.xxx handle things differently.

Two of them are .match and .vlookup.

Used like application.worksheetfunction.vlookup(), if no match is found, then an
error is raised:

dim Res as variant
on error resume next
res = application.worksheetfunction.vlookup(...)
if err.num <> 0 then
'no match found
err.clear
end if
on error goto 0


But if you use it like: application.vlookup(), an error can be returned:

dim res as variant
res = application.vlookup(...)
if iserror(res) then
'no match found
end if

I find the second version easier.
 
Back
Top