How to use this function?

  • Thread starter Thread starter Á÷À˵ÄË«Óã
  • Start date Start date
Á

Á÷À˵ÄË«Óã

I want to know how to use the Application.WorkSheetFunction.Text
function,especially the second argument it uses.

Any help?


--
 
The Text() function is different from the
Application.WorkSheetFunction.Text,so is
function format().

--
 
Hi

In VBA exists function Format(), with syntax
Format(Expression,FormatString,FirstDayOfWeek,FirstWeekOfYear)
All parameters except 1st one are optional, last 2 parameters will have
meaning only, when expression returns a date.

In Excel exists function TEXT(), with syntax
TEXT(NumericExpression,FormatString)
The worksheetfunction TEXT() is more limited in use, but in general both
behave identical in their common area, and both do return a string value.

In VBA, Application.WorksheetFunction.AnyValidFunction invokes an Excel
worksheetfunction, which otherwise you usually enter into worksheet cell as
a part of formula. P.e.:
TodaysDateString = Application.WorksheetFunction.Text(Date,"dddd,
dd.mm.yyyy")
, which really is same as
TodaysDateString = Format(Date,"dddd, dd.mm.yyyy")

Btw, in VBA exists functions Date and Now, with syntax
Date
Now
, which are returning current system date (and time).
An equivalent for those are the worksheetfunctions TODAY() or NOW(), in VBA
used p.e. as
Application.WorksheetFunction.Today()
At same time
Application.WorksheetFunction.Date(Year,Month,Day)
is an equivalent for VBA function DateSerial, with syntax
DateSerial(Year,Month,Day).

The meaning of this long explanation - don't never forget, that
worksheetfunctions and VBA functions are 2 different collections.
 
Hi Arvi,

Using:
Application.WorksheetFunction.Today() or
Application.WorksheetFunction.Date(Year,Month,Day)
Click to expand...

would cause an '"Object doesn't support this property or method" error
message.

Not all worksheet functions are available to VBA, particularly not when
equivalent VBA functions exist.

---
Regards,
Norman



Arvi Laanemets said:
Hi

In VBA exists function Format(), with syntax
Format(Expression,FormatString,FirstDayOfWeek,FirstWeekOfYear)
All parameters except 1st one are optional, last 2 parameters will have
meaning only, when expression returns a date.

In Excel exists function TEXT(), with syntax
TEXT(NumericExpression,FormatString)
The worksheetfunction TEXT() is more limited in use, but in general both
behave identical in their common area, and both do return a string value.

In VBA, Application.WorksheetFunction.AnyValidFunction invokes an Excel
worksheetfunction, which otherwise you usually enter into worksheet cell
as a part of formula. P.e.:
TodaysDateString = Application.WorksheetFunction.Text(Date,"dddd,
dd.mm.yyyy")
, which really is same as
TodaysDateString = Format(Date,"dddd, dd.mm.yyyy")

Btw, in VBA exists functions Date and Now, with syntax
Date
Now
, which are returning current system date (and time).
An equivalent for those are the worksheetfunctions TODAY() or NOW(), in
VBA used p.e. as
Application.WorksheetFunction.Today()
At same time
Application.WorksheetFunction.Date(Year,Month,Day)
is an equivalent for VBA function DateSerial, with syntax
DateSerial(Year,Month,Day).

The meaning of this long explanation - don't never forget, that
worksheetfunctions and VBA functions are 2 different collections.
Click to expand...
 
rover317 -

When you use Application.WorkSheetFunction.Text(value,format_text) in VBA,
you are using Excel's TEXT() worksheet function.

The Excel Text() worksheet function is identical to using
Application.WorkSheetFunction.Text() in VBA.

As JE McGimpsey said, the Text() worksheet function and the VBA Format()
function are similar; they are not identical; the VBA Format() function has
more features.

Here's part of the Help for the Text() worksheet function:

+++++++++++++++++
TEXT
Converts a value to text in a specific number format.
Syntax
TEXT(value,format_text)
Value is a numeric value, a formula that evaluates to a numeric value, or
a reference to a cell containing a numeric value.
Format_text is a number format in text form from in the Category box on
the Number tab in the Format Cells dialog box.
+++++++++++++++++

To see some of the possible number formats for the second argument, select a
cell, and choose Format | Cells | Number | Custom.

For example, in a worksheet cell, =TEXT(3.3333,"0.0") returns 3.3.

For more details and examples, search for "guidelines for custom number
formats" in Excel's Help (not in VBA Help).

- Mike
www.mikemiddleton.com

rover317 wrote: > The Text() function is different from the
Application.WorkSheetFunction.Text,so is function format(). <

JE McGimpsey wrote: > See the TEXT() function in XL Help. It's very similar
to the VBA function Format() <

rover317 wrote: > I want to know how to use the
Application.WorkSheetFunction.Text function,especially the second argument
it uses. <
 
Back
Top