Using Builtin Functions And Statements

If you want to further customize the way a number displays, you use the Format function. You can create your own number formats by combining specific characters along with symbols that represent the numbers, for example:

NUMERIC CHARACTERS

DISPLAYS

0

A numeric digit or a zero if the number does not have a digit in that place. Use this character to ensure that a digit appears in a specific place. For example, 0000 always displays a four-digit number. If there are fewer digits, a zero displays for the non-specified digits.

#

A numeric digit if the number has a digit in that place. If there is no digit, a value does not display in that place.

Decimal point placeholder.

%

An expression as a percentage by multiplying by 100 and adding a percent sign.

Thousands separator.

E-, E+, e-, e+

Numeric expression in scientific format. The number of digits on the right side of the symbol indicates the number of digits in the exponent.

\ or " "

The character that follows that backslash or enclosed in quotes. For example, to place a plus sign (+) in the number string you would type \+ in the desired location.

□ Type the remaining VBA code to work with the formatted number.

0 Switch to Excel and run the associated macro.

■ The macro runs and reformats the number using the specified formatting function.

□ Type the remaining VBA code to work with the formatted number.

0 Switch to Excel and run the associated macro.

■ The macro runs and reformats the number using the specified formatting function.

You can remove excess spaces from the front or the end of a specific string using one of the built-in trim functions in VBA. Extra spacing at the beginning or end of strings can affect the way the string displays. You have three different functions for trimming excess spacing. The RTrim function removes the excess spacing at the end of the string. The LTrim function removes the excess spacing at the beginning of the string. If you want to remove the extra spaces from both ends of a string simultaneously, you can use the Trim function.

Each function requires just one argument: the string containing the excess spacing. Typically, you pass the string to the function as the value of a variable or the contents of a cell in a worksheet. For example, LTrim(LongString) trims the excess spacing at the beginning of a string.

Each function returns a Variant data type with a subtype of String. See Chapter 3 for more information on data types in VBA. If you want the function to return a String data type value, you need to place the String type declaration symbol, a dollar sign, at the end of the function. When you use the dollar sign at the end of the function to return a String value, make sure the variable to which you assign the results is declared as a string.

None of these functions remove excess spacing within a string. For example, Trim(" This is a sample string ") removes the spacing only before the word This and after string. The extra spacing within the string remains untouched.

REMOVE EXTRA SPACING FROM A STRING

REMOVE EXTRA SPACING FROM A STRING

□ Create a new subroutine.

< Declare other variables needed for the subroutine.

LQ Assign the string expression to StringVar.

□ Create a new subroutine.

B Type Dim StringVar As String, replacing StringVar with the variable containing the string.

< Declare other variables needed for the subroutine.

LQ Assign the string expression to StringVar.

Many of the built-in functions in VBA return a Variant data type value. Good for simplifying code because their data types can handle any type of data, Variants still are not as efficient as Strings, which require less memory to store than Variants. For this reason, you can modify most of the built-in VBA functions that return a Variant to return a String data type by simply adding a dollar sign symbol ($) to the end of the function name. You can use the dollar sign with the following functions:

CHR

CHRB

CURDIR

Date

Dir

Error

Input

InputB

Lcase

Left

LeftB

Ltrim

Mid

MidB

Oct

Right

RightB

Rtrim

Space

Str

String

Time

Trim

UCase

ype Result = Trim$(StringVar), replacing StringVar with the variable to receive the trimmed string.

Q Type additional VBA code.

ype Result = Trim$(StringVar), replacing StringVar with the variable to receive the trimmed string.

Q Type additional VBA code.

□ Switch to Excel and run the associated macro.

If] File Edit

View

Insert Format

Tools Data Windov-

Help

Type a question for help

u

% I «

I«. 2

SI H 9 -

■II'" 'Il «

I u 1 m

= =

A1

-

f*

A

B

c

D

E

F

G

H

|

J

K

L r

1

2

3

4

5

6

7

8

9

in

11

12

Excel Macr

13

14

I OK

15

16

17

18

19

20

2n

22

23

24

25

2b

2!

r

► M \sheets/

-■ The Trim function removes the excess spaces at the beginning and end of the string.

0 0

Post a comment