String Functions:



VBA Functions - By Category

TECH ont he Net

|String Functions: |Numeric / Mathematical Functions: |Logical Functions: |Data Type Conversion Functions: |

|ASC (VBA) |ABS (WS, VBA) |CASE (VBA) |CBOOL (VBA) |

|CHR (VBA) |ATN (VBA) |IF-THEN-ELSE (VBA) |CBYTE (VBA) |

|Concatenate with & (WS, VBA) |COS (WS, VBA) | |CCUR (VBA) |

|CURDIR (VBA) |EXP (WS, VBA) | |CDATE (VBA) |

|FORMAT Strings (VBA) |FIX (VBA) |Lookup / Reference Functions: |CDBL (VBA) |

|INSTR (VBA) |FORMAT Numbers (VBA) |CHOOSE (WS, VBA) |CDEC (VBA) |

|INSTRREV (VBA) |INT (WS, VBA) |SWITCH (WS) |CINT (VBA) |

|LCASE (VBA) |LOG (WS, VBA) | |CLNG (VBA) |

|LEFT (WS, VBA) |RND (VBA) | |CSNG (VBA) |

|LEN (WS, VBA) |ROUND (VBA) |Information Functions: |CSTR (VBA) |

|LTRIM (VBA) |SGN (VBA) |ISDATE (VBA) |CVAR (VBA) |

|MID (WS, VBA) |SIN (WS, VBA) |ISERROR (WS, VBA) | |

|REPLACE (VBA) |TAN (WS, VBA) |ISNULL (VBA) | |

|RIGHT (WS, VBA) | |ISNUMERIC (VBA) | |

|RTRIM (VBA) | | | |

|SPACE (VBA) | | | |

|STR (VBA) | | | |

|STRCONV (VBA) | | | |

|TRIM (WS, VBA) | | | |

|UCASE (VBA) | | | |

|VAL (VBA) | | | |

|Date & Time Functions: |Financial Functions: |File/Directory Functions: | |

|DATE (VBA) |DDB (WS, VBA) |CHDIR (VBA) | |

|DATEADD (VBA) |FV (WS, VBA) |CHDRIVE (VBA) | |

|DATEDIFF (VBA) |IPMT (WS, VBA) |CURDIR (VBA) | |

|DATEPART (VBA) |IRR (WS, VBA) |DIR (VBA) | |

|DATESERIAL (VBA) |MIRR (WS, VBA) |FILEDATETIME (VBA) | |

|DATEVALUE (WS, VBA) |NPER (WS, VBA) |FILELEN (VBA) | |

|DAY (WS, VBA) |NPV (WS, VBA) |GETATTR (VBA) | |

|FORMAT Dates (VBA) |PMT (WS, VBA) |MKDIR (VBA) | |

|HOUR (WS, VBA) |PPMT (WS, VBA) |SETATTR (VBA) | |

|MINUTE (WS, VBA) |PV (WS, VBA) | | |

|MONTH (WS, VBA) |RATE (WS, VBA) | | |

|MONTHNAME (VBA) |SLN (WS, VBA) | | |

|NOW (WS, VBA) |SYD (WS, VBA) | | |

|TIMESERIAL (VBA) | | | |

|TIMEVALUE (WS, VBA) | | | |

|WEEKDAY (WS, VBA) | | | |

|WEEKDAYNAME (VBA) | | | |

|YEAR (WS, VBA) | | | |

Note: (WS) = Worksheet function, (VBA) = Visual Basic for Applications function

String Functions:

MS Excel: ASC Function (VBA)

[pic]

In Excel, the ASC function returns the ASCII value of a character or the first character in a string.

The syntax for the ASC function is:

Asc( string )

string is the specified character to retrieve the AscII value for. If there is more than one character, the function will return the AscII value for the first character and ignore all of the characters after the first.

Applies To:

• Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

• VBA function (VBA)

VBA Function Example:

The ASC function can only be used in VBA code. Here are some examples of what the ASC function would return:

|Asc ("W") |would return 87 |

|Asc ("Wednesday") |would return 87 |

|Asc ("x") |would return 120 |

For example:

Dim LResult As Integer

LResult = Asc ("W")

In this example, the variable called LResult would now contain the value 87.

MS Excel: CHR Function (VBA)

[pic]

In Excel, the CHR function returns the character based on the ASCII value.

The syntax for the CHR function is:

Chr( ascii_value )

ascii_value is the ASCII value used to retrieve the character.

Applies To:

• Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

• VBA function (VBA)

Worksheet Function Example:

|Chr (87) |would return "W" |

|Chr (105) |would return "i" |

VBA Function Example:

The CHR function can only be used in VBA code. For example:

Dim LResult As String

LResult = Chr(87)

In this example, the variable called LResult would now contain the value "W".

MS Excel: Concatenate with & (WS, VBA)

[pic]

To concatenate multiple strings into a single string in Excel, you can use the "&" operator to separate the string values.

The syntax for the "&" operator is:

string1 & string2 [& string3 & string_n]

Applies To:

• Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

• Worksheet function (WS)

• VBA function (VBA)

VBA Function Example:

The "&" operator can be used to concatenate strings in VBA code. For example:

Dim LValue  As String

LValue  = "Alpha" & "bet"

The variable LValue would now contain the value "Alphabet".

Frequently Asked Questions

Question:For an IF statement in Excel, I want to combine text and a value.

For example, I want to put an equation for work hours and pay. If I am paid more than I should be, I want it to read how many hours I owe my boss. But if I work more than I am paid for, I want it to read what my boss owes me (hours*Pay per Hour).

I tried the following:

=IF(A2 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download