Application.WorksheetFunction.UPPER ()

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 ???
 
P

Paul B

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 **
 
L

Leif Rasmussen

But what to do with Excels build in functions if they is
not a range object, but the content of a variable.
 
D

Don Guillett

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.
 
D

Dave Peterson

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.
 
Top