VBA entry functions (3)

(Most of them are collected in summary of online blogs)

String Functions

========================================================== ========================================================== ====

Mid (string, starting from the nth, length) in [String] [starting from the nth] [length string]
For example, if mid ("xiaoxin invincible",) is returned"
 
Instr (starting from the first few, string 1, string 2) Searches from the specified position and returns the position of string 2 in string 1.
For example, if instr (1, "Xiao Xin invincible", "Xiao") is returned, 1, instr (2, "Xiao Xin invincible", "Xiao"), 0 is returned. 0 indicates not found
 
Rev (string 1, string 2, starting from the nth position), search for string 2 from the beginning, and return the position of string 2 in string 1. Note that, the returned value is calculated based on the previous value.
For example, when Rev ("Xiao Xin invincible", "Xiao", 2) returns 2; when Rev ("Xiao Xin invincible", "Xin", 1) returns 0, it cannot be found because it starts to search forward from the 1st characters of "xiaoxin invincible. 0 indicates not found
 
Left (string, length) Returns [length] characters starting from the left of [String]
For example, left ("Xiao Xin invincible, 3) returns" Xiao Xin Wu"
 
Right (string, length) Returns [length] characters starting from the right of [String]
For example, right ("xiaoxin invincible", 3) returns "Xin invincible"
 
Ucase (string) returns the [String] in uppercase format, only valid for English characters
For example, ucase ("xiaoxinwudi") returns "xiaoxinwudi"
 
Lcase (string) returns the lowercase form of a [String], only valid for English characters
For example, lcase ("xiaoxinwudi") returns "xiaoxinwudi"
 
ASC (character) returns the [character] ASCII code. If there are multiple characters, only the first character ASCII code is returned, and the CHR () function is a reversible process.
For example, if ASC ("small") is returned,-12127 is returned. If ASC ("Small Xin invincible"),-12127 is returned.
 
CHR (ASCII encoding) returns the characters represented by [ASCII] encoding, And the CHR () function is a reversible process.
For example, CHR (-12127) returns "small"; CHR (ASC ("small") returns "small" (here to demonstrate the reversible nature of ASC and CHR, for example, you can use this feature to encrypt text)
 
Trim (string) returns the [String] After and after Removal
For example, trim ("xiaoxin invincible") returns "xiaoxin invincible", and spaces in the middle are not affected.
 
String (number, character) Returns [number] [characters]
For example, string (3, "small") returns "small", while string (3, "Small invincible") returns "small", only the first character is valid.
 
Space (number) Returns [number] Spaces
For example, space (5) returns ""
 
Strconv converts a string to a specified type. There are only two common types. vbwide converts the halfwidth to the fullwidth, and vbnarrow converts the fullwidth to the halfwidth.
For example, strconv ("Xiao", vbwide) returns "Xiao", strconv ("Xiao", vbnarrow) returns "Xiao"
'Note vbwide = 4, vbnarrow = 8
 
Len (string) returns the length of [String]
For example, Len ("xiaoxin invincible") returns 4

Formatting Function =================================================== ========================================================== =====

Format [$] (expr [, FMT])
Format returns the variant.
Format $ force return as text
--------------------------------
Number Format
--------------------------------

Fixed format parameters:
General number, which is a common number. For example, it can be used to remove the thousands separator.
Format $ ("100,123.12", "General number") returns 100123.12

Currency type. You can add a thousand-digit separator and a currency symbol.
Format $ ("100123.12", "currency") Return Value ￥100,123.12

The fixed format is a number with two decimal places.
Format $ ("100123", "fixed") returns 100123.00

Standard, that is, the number of decimal places and two decimal places.
Format $ ("100123", "standard") returns 100,123.00

Percent percentage
Format $ ("100123", "percent") returns 10012300.00%

Scientific scientific note
Format $ ("100123", "scientific") Return Value 1.00e + 05

Yes/No. If the value is 0, no is returned. Otherwise, yes is returned.
Format $ ("100123", "Yes/No") Return Value Yes

True/false if the value is 0, false is returned; otherwise, true is returned.
Format $ ("100123", "True/false") returns true

If the on/off value is 0, return off. Otherwise, return on
Format $ ("100123", "Yes/No") Return Value on
Custom format parameters

"" Original value returned without formatting

0 placeholder formatting, less than 0
Format $ ("100123", "0000000") returns 0100123

# Placeholder formatting. If not, do not add 0.
Format $ ("100123", "######") Return Value 100123

. Forcibly display the decimal point
Format $ ("100123.12", ". 000") returns 100123.120

% Is converted to a percentage. A % Represents multiplied by 100.
Format $ ("10.23", "0.00%") returns 1023.00%
Format $ ("10.23", "0.00% %") Return Value: 102300.00% %

, In the unit of thousands
Format $ ("10.23", ",") returns 0
Format $ ("10010.23", ",") returns 10
Format $ ("10010.23", ", 0.00") returns 10.01

E-e + e-e + is displayed as a scientific notation (pay attention to the format statement, otherwise it will be mixed with other meanings of E)
Format $ (12.5, "0.00e + 00") Return Value: 1.25e + 01

$ Force display currency symbols
Format $ ("10.23", "{threadcontent}. 00") Return Value ￥10.23

-+ () Space: Display by position
Format $ ("1234.56", "-(0.00)") Return Value-(1234.56)

\ Escape character, displaying special characters
Format $ ("1234.56", "#. 00") return value #1234.56

"ABC" shows strings in double quotation marks. To include a string in the format, use CHR (34) to enclose the text (34 is double quotation marks ("))
Format $ (123.45, "TTT") returned value ttt
Note: When the text in double quotation marks contains special parameter symbols such as E, the escape character "\" should be used; otherwise, it will be displayed as E

; Similar to the multi-object operator.

When there are four parts,
When the value is greater than 0, the first part is formatted,
When the value is less than 0, format the second part,
When the value is 0, format the data in the third part,
If the value is null, format it according to the fourth part. For example:
Format $ (0, "0.00; negative; zero; null") returns zero

When there are three parts,
When the value is greater than 0, the first part is formatted,
When the value is less than 0, format the second part,
If it is equal to 0, format it by the third part, for example:
Format $ (-10, "0.00; TTT; 0") Return Value ttt

When there are two parts,
When the value is greater than or equal to 0, the first part is formatted,
When the value is less than 0, format the second part,
Format $ (-123, "0.00; ABC") return value abc
Format $ (123, "0.00; ABC") returns 123.00

When there is a total part, press the left side of the semicolon to format it.

---------------------------------
Date Format
---------------------------------
Note: In the Chinese operating system, the system automatically enters the month as month, for example, May, rather than May.

Fixed format parameters
General date basic type
Format $ (now, "General date") returned value 14:56:15

Long date defined by the operating system
Format $ (now, "long date") returns May 25, 2006

Date in medium date (yy/Mmm/DD)
Format $ (now, "medium date") returned values: 06-5 months-25

Short date short date defined by the operating system
Format $ (now, "short date") Return Value 2006-5-25

Long time defined by the operating system
Format $ (now, "long time") returned value 15:06:36

Medium time in the 12-hour format with AM/PM, without seconds
Format $ (now, "medium time") Return Value pm

Short time in 24-hour format, without seconds
Format $ (now, "Short Time") Return Value

Custom format parameters
: Used to identify the interval of time characters
Format $ (Time (), "HH: NN") Return Value

/Used to identify the interval between date characters
Format $ (now, "yyyy/mm/DD") Return Value

C format the date and time of the national mark
Format $ (now, "C") Return Value 14:56:15

Y The day of the year
Format $ (now, "Y") returns 145

D The day of the month (1-366)
Format $ (now, "D") return value 25

DD: the number of days (01-31) with 0 in front of less than 10)
Format $ ("2006-1-7", "DD") Return Value 07

Ddd day of week
Format $ (now, "DDD") returns Thursday

Dddd day of the week
Format $ (now, "dddd") returns Thursday

Ddddd display standard date
Format $ (now, "ddddd") Return Value

Dddddd long date
Format $ (now, "dddddd") Return Value: January 1, May 25, 2006

W. The day of the week
Format $ (now, "W") return value 5

WW week of the year
Format $ (now, "ww") Return Value 21

M months (Note: when used for time, it can also be expressed as minutes)
Format $ (now, "M") return value 5
Format $ (now, "H: M") Return Value

MM: The number of months with 0 in front when the value is less than 10 (Note: when used for time, the table can also be used as a minute with 0)
Format $ (now, "M") Return Value 05
Format $ (now, "HH: mm") Return Value

Mmm month
Format $ (now, "mmm") returns the value May

Q quarter of a year (1-4)
Format $ (now, "Q") returns 2

Two-digit year of YY (00-99)
Format $ (now, "YY") Return Value 06

Yyyy four-digit year (0100-9999)
Format $ (now, "YYYY") returns 2006

H The Nth hour of a day (0-23)
Format $ (now, "H") Return Value 16

HH hours with 0 (00-23) when less than 10)
Format $ ("7:30:28", "hh") Return Value 07

N minutes per hour (0-59)
Format $ ("7:30:28", "n") return value 30

Nn with 0 minutes (00-59) when the value is less than 10)
Format $ ("", "n") Return Value 03

Seconds in one minute (0-59)
Format $ ("", "S") return value 8

When the value of SS is less than 10, the value is 0 minutes (00-59)
Format $ ("", "SS") Return Value 08

Ttttt standard time. If the number of hours is less than 10, no value is provided, which is the same as H: mm: Ss.
Format $ ("7:03:28", "ttttt") Return Value

AM/PM: the current value is am or PM.
Format $ (now, "AM/PM") Return Value pm

A/P indicates that the current value is a or P
Format $ (now, "A/P") return value p

Ampm determines whether a value ranging from 0 to 2359 is am or PM. It can be regarded as equal to or equal to. For example, ampm can be regarded.
Format $ (1000, "ampm") Return Value AM

Joint formatting

M/D/yy format $ (now, "m/D/YY") return value 5-25-06

D-mmm-yy format $ (now, "d-mmm-yy") Return Value: 25-5 month-06

D-mmmm format $ (now, "d-mmmm") Return Value: 25-May

Mmmm-yy format $ (now, "Mmmm-yy") returned values: May

Hh: Mm AM/PM format $ (now, "HH: Mm AM/PM") Return Value pm

H: mm: ss a/P format $ (now, "H: mm: ss a/P") Return Value 4:51:38 P

H: Mm format $ (now, "H: mm") Return Value: 16: 51

H: mm: SS format $ (now, "H: mm: SS") returned value: 16:51:38

M/D/yy H: Mm format $ (now, "m/D/yy H: mm") return value 5-25-06

----------------------------------
Text Format
----------------------------------
When there are two parts, the first part is non-empty formatting, and the second part is the formatting expression of the null value or null value.
@ Insert formatted text at the matching position. When the placeholder position does not exist, it is blank (empty string)

When there is only one @ symbol, the formatted text is added at the end.
Format $ ("chin", "@ A") Return Value China

There are multiple @ placeholders, which are matched from right to left and formatted text is displayed at the corresponding position
Format $ ("chin", "@ [email protected] @") Return Value Chain

When it matches! When matching, it is changed to from left to right matching
Format $ ("chin", "[email protected] @") Return Value cahin

When there are more placeholders than the original string, just add spaces in the corresponding position
Format $ ("C", "@ [email protected]") return blank AC

& Character placeholders. Except when the placeholder position does not exist, it is not displayed, and the rest are the same @

When there are more placeholders than the original string, just add spaces in the corresponding position
Format $ ("C", "& A &") Return Value AC

<Force lowercase. Display All characters in lowercase.
Format $ ("I love you", "<") return value I love you

> Force uppercase. Display All characters in uppercase/lowercase format.
Format $ ("I love you", ">") return value I love you

! Fill in the character placeholder from left to right. The default value is the right-left padding character placeholder.
Format $ ("chin", "[email protected] @") Return Value cahin

----------------------------------------
Force Date and Time in Chinese format
----------------------------------------
Aaaa week
Format $ (now, "aaaa") return value Friday

O Chinese month
Format $ (now, "O") returns the value May

O single-byte month
Format $ (now, "O") returns the value May

A Chinese Date
Format $ (now, "A") returns 26 days

A single-byte date
Format $ (now, "A") Return Value 26 days

E short Chinese Year
Format $ (now, "e") returns six years

E bytes year
Format $ (now, "e") returns 6 years

EE Chinese Year
Format $ (now, "ee") Return Value

EE single-byte year
Format $ (now, "ee") returns 2006

The problem of unexpected Chinese dates is so easy to solve:
Format $ (now, "eeoa") Return Value May 26

 

VBA entry functions (3)