• A.S.C. () - this function allows you to return the numeric code for the passed character. For example, ASC("D") will return 68. This function is useful for determining the next or previous letter. It is usually used in conjunction with the function Chr(), which performs the inverse operation - returns a character according to its numeric code transmitted. Variants of this function are AscB() And AscW():
  • AscB () - returns only the first byte of the numeric code for the character.
  • AscW () - returns the code for the character in Unicode encoding
• Chr () - returns a character by its numeric code. Can be used in conjunction with the Asc() function, but most often it is used when you need to print a service character (for example quotes - "), because You can’t just enter quotes in VBA code (you need to put double). This is the function I usually use.

  Dim sWord As String sWord = Chr(34) & "Word in quotes" & Chr(34)

  There are options for this function - ChrB() And ChrW(). Work similarly to the same options for the function Asc().

• InStr () And InStrRev () - one of the most popular features. Allows you to detect a character or sequence of characters in the body of a string variable and return their position. If the sequence is not found, then 0 is returned.

  Dim sStr As String sStr = "w" If InStr(1, "Hello, World!", sStr, vbTextCompare) > 0 Then MsgBox "The search word is present!" Else MsgBox "The searched word is missing!" End If

  The difference between the functions is that InStr() searches for the specified word from the beginning of the line, and InStrRev() from the end of the line

• Left () , Right () , Mid () - the ability to take the number of characters you specify from an existing string variable on the left, right or middle, respectively.

  Dim sStr As String sStr = "Hello, World!" MsgBox Mid(sStr, 1, 5)

  Dim sStr As String sStr = "Hello, World!" MsgBox Mid(sStr, 1, 5)

• Len () - ability to get the number of characters in a line. Often used with loops, replace operations, etc.
• LCase () And UCase () - convert the string to lower and upper case respectively. Often used to prepare a value for comparison when case is not important when comparing (surnames, names of companies, cities, etc.).
• LSet () And RSet () - the ability to fill one variable with symbols of another without changing its length (left and right respectively). Extra characters are cut off and spaces are substituted for missing characters.
• LTrim () , RTrim () , Trim () - the ability to remove spaces respectively on the left, right or both left and right.
• Replace () - the ability to replace one sequence of characters in a string with another.

  Dim sStr As String sStr = "Hello, World!" MsgBox Replace(sStr, "Hello" , "Bay" )

  Dim sStr As String sStr = "Hello, World!" MsgBox Replace(sStr, "Hello", "Bay")

• Space () - get a string from the number of spaces you specify;
  Another similar function is Spc () , which is used to format console output. It multiplies spaces based on the width of the command line.
• StrComp () - ability to compare two strings.
• StrConv () - ability to convert a string (to Unicode and back, to upper and lower case, capitalize the first letter of words, etc.):

  Dim sStr As String sStr = "Hello, World!" MsgBox StrConv("Hello, World!" , vbUpperCase)

  Dim sStr As String sStr = "Hello, World!" MsgBox StrConv("Hello, World!", vbUpperCase)

  Constants can be used as the second parameter parameter:

  • vbUpperCase: Converts all text characters to UPPER CASE
  • vbLowerCase: Converts all text characters to lowercase
  • vbProperCase: Converts the first character of each word to Upper Case
  • *vbWide: Converts string characters from single-byte to double-byte
  • *vbNarrow: Converts string characters from double-byte to single-byte
  • **vbKatakana: Converts Hiragana characters to Katakana characters
  • **vbHiragana: Converts Katakana characters to Hiragana characters
  • ***vbUnicode: Converts a string to Unicode using the system's default code page
  • ***vbFromUnicode: Converts a Unicode string to the system's default code page

  * applicable for localization of the Far East
  ** Applicable for Japan only
  *** not supported on Macintosh operating systems

• StrReverse () - “reverse” a string by placing its characters in reverse order. The function only works from Excel 2000 and higher. An example of using the function, as well as other methods for turning a word, can be found in this article: How to turn a word around?
• Tab () is another function that is used to format console output. Reproduces tab characters in the number you specify. If no quantity is specified, simply inserts a tab character. You can also use the constant to insert a tab character into a string value vbTab.
• String () - allows you to get a string of a specified number of characters (which again are specified by you). Typically used to format output in conjunction with the function Len().

(http://www.cyberforum.ru/vba/thread638743.html)

Option 1

strW= (="KOROTEEV DMITRY VLADIMIROVICH") strB=Replace(Replace(strW, Chr(61), ""), Chr(34), "")

Option

You can also use a byte array:

Sub n() Dim Mass() As Byte, n As Long, Zam As String, TXT As String TXT = "="" DMITRY VLADIMIROVICH KOROTEEV" Mass = StrConv(TXT, vbFromUnicode) For n = 0 To UBound(Mass) If Mass(n)<>34 And Mass(n)<>61 Then Zam = Zam + Chr$(Mass(n)) Next MsgBox Zam End Sub

Option

Or filtering with mid:

Sub nn() Dim n As Long, TXT As String, L As String, Zam As String TXT = "="" DMITRY VLADIMIROVICH KOROTEEV" For n = 1 To Len(TXT) L = Mid$(TXT, n, 1) If L<>"""" And L<>"=" Then Zam = Zam + L Next MsgBox Zam End Sub

Line 6 can be replaced with husky:
Visual Basic Code
1
If L Like “[!””=]” Then Zam = Zam + L

Option

Also through position search and recursion:

Sub test() Dim n As Long, txt As String txt = "=""DMITRY VLADIMIROVICH KOROTEEV" txt = Change(txt, "=") txt = Change(txt, """") MsgBox txt End Sub Function Change( txt As String, What As String, Optional Pos = 1) Dim n As Long If Pos<>0 Then n = InStr(Pos, txt, What) Change = Mid$(txt, Pos, IIf(n - Pos< 0, Len(txt), n - Pos)) + Change(txt, What, IIf(n = 0, 0, n + 1)) End If End Function

You can also use regular expressions, but I don’t know them.

Option

Through right-to-left search and recursion:

Visual Basic Code

1 2 3 4 5 6 7 8 Function Change(txt As String, What As String, Optional Pos As Long) Dim n As Long If Pos = 0 Then Pos = Len(txt) If Pos<>-1 Then n = InStrRev(txt, What, Pos) Change = Change(txt, What, IIf(n = 1, -1, n - 1)) + Mid$(txt, n + 1, Pos - n) End If End Function

Option

And there is also Split And Join

Strb = Join(Split(Join(Split(strW, "="), ""), """"), "")

Off-topic: But this is for sadists

Uppercase and lowercase

With ActiveDocument.Range "uppercase.Text = Ucase(.Text) "lowercase.Text = Lcase(.Text) End With

or StrConv() - convert a string (to Unicode and back, to upper and lower case, capitalize the first letter of words, etc.) - see below

String Operations

For data of the String type, there is only one operation - concatenation (union). For example, the result of the concatenation operation of three string values ​​“Peter” & » » & “Ivanovich” will be the string “Peter Ivanovich”. It is also possible to use another operator for the concatenation operation, for example: “ten” + “thousand”. The difference between these expressions is that in the first case, the operands can be values ​​of any type (they will simply be converted to strings), and in the second, both operands must be of type String. There are a large number of functions for working with strings (table. Functions for working with strings).

Table “Functions for working with strings”

Function	Description	Example
Len(str)	Determines the length of the string	From a=len(“Characters”) it follows a=9
Left (<строка>, <длина>)	Extracts from argument<строка>specified number of characters on the left	Left("1234string", 4) ="1234″
Right(<строка>, <длина>)	Extracts from argument<строка>specified number of characters to the right	Right(" 1234string", 6) ="string"
Mid(<строка>, <старт> [, <длина>])	Extracts from argument<строка>substring with the specified number of characters, starting at position<старт>	Mid("12345678″, 4.3) ="456"
Mid(<строка>, <старт>)	A substring from the position is extracted<старт>to the end of the line	Mid("12345678″, 4) ="45678"
LTrim(<строка>)	Removes spaces at the beginning of a line	LTrim("print") ="print"
RTrim (<строка>)	Removes spaces at the end of a string	RTrim("print ") = "print"
Trim(<строка>)	Removes spaces at the beginning and end of a string	Trim("print") ="print"
InStr([<старт>, ] < строка1>, <строка2> [, <сравнение>])	Searches for a substring in a string. Returns the position of the first occurrence of a string<строка2>to line<строка1>, <старт>— the position from which the search begins. If this argument is omitted, the search starts from the beginning of the string	Instr("C:Temp test.mdb", "Test")=9 If the search string is not in the specified string, the function returns 0
InStrRev([<старт>, ] <строка1>, <строка2> [, <сравнение>])	Searches for a substring in a string, but starts the search at the end of the string and returns the position of the last occurrence of the substring. Optional argument<сравнение>defines the type of comparison between two strings
Replace(<строка>, <строкаПоиск>, <строкаЗамена>)	Allows you to replace one substring in a string with another. This function searches for all occurrences of the argument<строкаПоиск>in argument<строка>and replaces them with<строкаЗамена>

To compare string values, you can use regular numeric comparison operators because when comparing characters, their binary codes are compared. The Like operator is also used to compare string values, which allows you to detect an imprecise match, for example, the expression "Input" Like "Input*" will evaluate to True because the string being compared begins with the word "Input". The asterisk (*) character in a string replaces an arbitrary number of characters. Other characters that are processed by the Like operator in the compared string:

• ? – any character (one);
• # – one digit (0–9);
• [<список>] – a character that matches one of the characters in the list;
• [!<список>] – a character that does not match any of the characters in the list.

• A.S.C.() - this function allows you to return the numeric code for the passed character. For example, ASC("D") will return 68. This function is useful for determining the next or previous letter. It is usually used in conjunction with the function Chr(), which performs the inverse operation - returns a character according to its numeric code transmitted. Variants of this function are AscB() And AscW():
  • AscB() - returns only the first byte of the numeric code for the character.
  • AscW() - returns the code for the character in Unicode encoding
• Chr() - returns a character by its numeric code. Can be used in conjunction with the Asc() function, but most often it is used when you need to print a service character (for example quotes - "), because You can’t just enter quotes in VBA code (you need to put double). This is the function I usually use.

  Dim sWord As String sWord = Chr(34) & "Word in quotes" & Chr(34)

  There are options for this function - ChrB() And ChrW(). Work similarly to the same options for the function Asc().

• InStr() And InStrRev() - one of the most popular features. Allows you to detect a character or sequence of characters in the body of a string variable and return their position. If the sequence is not found, then 0 is returned.

  Dim sStr As String sStr = "w" If InStr(1, "Hello, World!", sStr, vbTextCompare) > 0 Then MsgBox "The search word is present!" Else MsgBox "The searched word is missing!" End If

  The difference between the functions is that InStr() searches for the specified word from the beginning of the line, and InStrRev() from the end of the line

• Left() , Right() , Mid() - the ability to take the number of characters you specify from an existing string variable on the left, right or middle, respectively.

  Dim sStr As String sStr = "Hello, World!" MsgBox Mid(sStr, 1, 5)

• Len() - ability to get the number of characters in a line. Often used with loops, replace operations, etc.
• LCase() And UCase() - convert the string to lower and upper case respectively. Often used to prepare a value for comparison when case is not important when comparing (surnames, names of companies, cities, etc.).
• LSet() And RSet() - the ability to fill one variable with symbols of another without changing its length (left and right respectively). Extra characters are cut off and spaces are substituted for missing characters.
• LTrim() , RTrim() , Trim() - the ability to remove spaces respectively on the left, right or both left and right.
• Replace() - the ability to replace one sequence of characters in a string with another.

  Dim sStr As String sStr = "Hello, World!" MsgBox Replace(sStr, "Hello", "Bay")

• Space() - get a string from the number of spaces you specify;
  Another similar function is Spc() , which is used to format console output. It multiplies spaces based on the width of the command line.
• StrComp() - ability to compare two strings.
• StrConv() - ability to convert a string (to Unicode and back, to upper and lower case, capitalize the first letter of words, etc.):

  Dim sStr As String sStr = "Hello, World!" MsgBox StrConv("Hello, World!", vbUpperCase)

  Constants can be used as the second parameter parameter:

  • vbUpperCase: Converts all text characters to UPPER CASE
  • vbLowerCase: Converts all text characters to lowercase
  • vbProperCase: Converts the first character of each word to Upper Case
  • *vbWide: Converts string characters from single-byte to double-byte
  • *vbNarrow: Converts string characters from double-byte to single-byte
  • **vbKatakana: Converts Hiragana characters to Katakana characters
  • **vbHiragana: Converts Katakana characters to Hiragana characters
  • ***vbUnicode: Converts a string to Unicode using the system's default code page
  • ***vbFromUnicode: Converts a Unicode string to the system's default code page

  *applicable for Far East localizations
  ** Applicable for Japan only
  *** not supported on Macintosh operating systems

• StrReverse() - “reverse” a string by placing its characters in reverse order. The function only works from Excel 2000 and higher. An example of using the function, as well as other methods for turning a word, can be found in this article: How to turn a word around?
• Tab() is another function that is used to format console output. Reproduces tab characters in the number you specify. If no quantity is specified, simply inserts a tab character. You can also use the constant to insert a tab character into a string value vbTab.
• String() - allows you to get a string of a specified number of characters (which again are specified by you). Typically used to format output in conjunction with the function Len().

The following three functions allow you to work with an array of strings

Split (<строка> [, <разделитель>]) – converts a string into an array of substrings. By default, space is used as the delimiter. This function is convenient to use for breaking a sentence into words. However, you can specify any other delimiter in this function. For example, Split(3, "This is a test sentence") returns an array of three string values: "This", "test", "sentence". Join (<массивСтрок> [, <разделитель>]) – converts an array of strings into a single string with the specified delimiter. Filter(<массивСтрок>, <строкаПоиск>[, <включение>] [, <сравнение>]) – scans an array of string values ​​and searches for all substrings that match the given string. This function has four arguments:<строкаПоиск>– search string;<включение>– a parameter (Boolean value) that specifies whether the returned rows will include the searched substring or, conversely, only those array rows that do not contain the searched string as a substring will be returned;<сравнение>– parameter defining the string comparison method. Three more functions provide string conversion: LCase(<строка>) – converts all characters in a string to lowercase, for example the LCase("MAIL") function returns the string "mail"; UCase(<строка>) – converts all characters in the string to upper case; StrConv(<строка>, <преобразование>) – performs several types of string transformations depending on the second parameter. This parameter is described by built-in constants, for example the function StrConv("Russia", VbProperCase) returns the value "Russia".

And the last two functions generate strings of characters

Space(<число>) – creates a string consisting of the specified number of spaces; String(<число>, <символ>) – creates a string consisting of the number of characters specified in the first argument. The symbol itself is specified in the second argument.

Example

Create a program that works with string variables. To do this, create a form whose labels contain the following messages: 1 label: the length of the string entered in the first text field is reported (1 line); 2 label: converts all characters of the third text field (3rd line) to capital letters; 3rd label: displays the contents of the first and second text fields (lines 1 and 2) together.

Execution technology

• Open Word, save the document, and go to the VBA editor.
• Create a shape similar to the picture below.
• Write an event handler for the OK button.
• Compile the program.
• Run the form.

Private Sub CommandButton1_Click() Dim a As String Dim b As String Dim c As String Dim k As String Dim d As String Dim n As Integer a=TextBox1.Text n=Len(a) Label7.Caption=“the length of the first line is” & n & "characters" c=TextBox3.Text k=Ucase(с) Label8.Caption=k b=TextBox2.Text d=a + " " + b Label9.Caption=d End Sub

Task

It is necessary that in Excel in cell A1 a search is performed for words written separated by commas in column A2, and the result, and the words found, with the number of their repetitions (if possible) in the searched text, are written in another, third, cell. (it would be even better if they were highlighted (or underlined) in some way in the first cell... so that they would be immediately visible. This is how you put words into an array:

Visual Basic Code

1 2 3 4 5 6 Dim m() As String If InStr(1, Cells(1, 2).Value, ","") > 0 Then m = Split(Replace(Cells(1, 2).Value, " ", ""), " ,"") Else ReDim m(0): m(0) = Trim(Cells(1, 2).Value) End If

And then in a loop you search for all the words (again in a loop, nested) Added after 23 minutes

Visual Basic Code

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Option Compare Text Option Explicit Sub QWERT() Dim R, N, K Dim m() As String If InStr(1, Cells(1, 2).Value, ",") > 0 Then m = Split(Replace(Cells( 1, 2).Value, " ", ""), ",") Else ReDim m(0): m(0) = Trim(Cells(1, 2).Value) End If For R = 0 To UBound( m) N = 1 If InStr(1, Cells(1, 1).Value, m(R)) > 0 Then K = InStr(N, Cells(1, 1).Value, m(R)) Do COLOR K , Len(m(R)) N = K + Len(m(R)) K = InStr(N, Cells(1, 1).Value, m(R)) Loop While K > 0 End If Next R End Sub Sub COLOR(ST, LN) With Cells(1, 1).Characters(Start:=ST, Length:=LN).Font .Color = RGB(0, 0, 255) .Bold = True End With End Sub

Added after 15 minutes And this is how it will search at the beginning of the word and select the entire word

Visual Basic Code

1 2 3 4 5 6 7 8 9 10 11 Sub COLOR(ST, LN) LN = LN - 1 Do LN = LN + 1 Loop While VBA.Mid(Cells(1, 1).Value, ST + LN, 1)<>" " With Cells(1, 1).Characters(Start:=ST, Length:=LN).Font .Color = RGB(0, 0, 255) .Bold = True End With End Sub

You can do a search not from the beginning of the word, but by part of the word. — add another Do-Loop cycle. Only shift the start (ST) to the left to a space (http://www.cyberforum.ru/vba/thread567588.html)

How to: Match a String against a Pattern (Visual Basic)

Compliance check strings to the template String Data Type (Visual Basic) - Like operator (Visual Basic) . the left operand of the Like operator is a string expression, and the right operand is a template string Like returns a Boolean value

Character check

? - any one symbol

• myString consists of the character W followed by any 2 characters

  Dim sMatch As Boolean = myString Like "W??"

Any character from the list and range

Any character and then one of the characters A, C or E

Dim sMatch As Boolean = myString Like "?" case sensitive

• myString = num characters and then one character from the range: i, j, k, l, m or n:

  Dim sMatch As Boolean = myString Like "num" case sensitive

Like works with both a zero-length string array string (""). allows you to check that a string is empty

A character from the list or no character

1. The Like operator is used twice, and the results are combined using the Or Operator (Visual Basic) or OrElse Operator (Visual Basic).
2. In the first statement template, insert a list of characters in square brackets ().
3. In the second statement template, do not place anything in the check location. Example: checking a seven-digit phone number phoneNum, which must contain exactly three digits, followed by a space, a hyphen, a dot, or no character, and then four digits. (the first three digits may not be separated from the last four - “no character”) Null , an error occurs. If an argument is given comparison_type , argument start_position is mandatory. line_1 Required. String expression, which is being searched. line_2 Required. The string expression you are looking for. comparison_type Optional. Defines the type string comparisons. If the value of the argument comparison_type is Null, an error occurs. If the argument comparison_type omitted, comparison type is determined by parameter Meaning Wedawe. Specify a valid LCID (LocaleID) parameter to use the comparison rules specified in the language settings.

   Options

   Argument comparison_type uses the following parameters:

   Return values

   Notes

   Function InStrB used with byte data contained in a string. Function InStrB returns the byte position, not the character position, of the first occurrence of one string within another.

   Examples

   Using the InStr function in an expression Every time you use expressions you can use the function InStr. For example, if you want to determine the position of the first point ( . ) in a field that contains an IP address (named "IPAddress"), you can use the function InStr to find it:

   InStr(1,,"")

   Function InStr looks at each value in the "IPAddress" field and returns the position of the first point. Therefore, if the value of the first octet of the IP address is 10. , the function returns the value 3.

   You can use other functions that use the result of the function InStr, to extract the value of the IP address octet that precedes the first dot, for example:

   Left(,(InStr(1,,."")-1))

   In this example the function InStr(1,,"") returns the position of the first point. As a result of subtracting 1, the number of characters preceding the first point is determined, in this case - 2. Then the function Left extracts these characters from the left side of the "IPAddress" field, returning the value 10.

   Using the function InStr in Visual Basic for Applications (VBA) code

   NOTE. The following examples illustrate the use of the Visual Basic for Applications (VBA) module. For more information about working with VBA, select Developer's reference in the drop-down list next to the button Search, and then enter one or more keywords in the search field.

   In this example, the function InStr used to get the position of the first occurrence of one string within another.

   Dim SearchString, SearchChar, MyPos SearchString ="XXpXXpXXPXXP" " String to search in. SearchChar = "P" " Search for "P". " A textual comparison starting at position 4. Returns 6. MyPos = Instr( 4, SearchString , SearchChar , 1) " A binary comparison starting at position 1. Returns 9. MyPos = Instr( 1, SearchString , SearchChar , 0) " Comparison is binary by default " (last argument is omitted). MyPos = Instr( SearchString , SearchChar ) " Returns 9. MyPos = Instr( 1, SearchString , "W" ) "Returns 0.

Often lines in a program contain unnecessary whitespace characters at the end or beginning of the line that need to be removed because extraneous leading or trailing spaces in a line can cause problems with the program.

VBA has three functions designed to remove leading and trailing spaces from a string: LTrim, RTrim, Trim. Keep in mind that these functions do not actually modify the original string, but rather return a copy of the string with the extra spaces removed.

Determining String Length

You typically need to know the length of a string when formatting messages for a user or when formatting string data entered by a procedure into an Excel worksheet or Word document. VBA uses the function for these purposes Len. In the listing below, there are two spaces between the parentheses and the word.

You should be careful with fixed length strings. Because a fixed-length string is always the same length, the Len function always returns the declared length of the string, regardless of the actual length of the string. For example, if the fixed-length string variable StrokeName, which is 15 characters long, actually contains the word "sun", then the function Len(StrokeName) will return the result 15. In this case, to find out the actual length of the string (in our case - 6), you need to use the following combination of functions: Len(Trim(StrokeName)).

Comparing and searching strings

VBA has two functions that help you compare strings: StrComp, InStr.

Function StrComp

Syntax

StrComp(String1, String2[, Compare])

String1, String2 - any two string expressions that need to be compared.

When StrComp is executed, one of the following values ​​is returned:

• -1 if String1 is less than String2;
• 0 if String1 and String2 are equal;
• 1 if String1 is greater than String2.

The above listing compares two strings in text mode: "Default String" and "Default String". Comparison result = 1, i.e. "Default String" is greater than "Default String".

Experiment with a variety of strings to better understand how the StrComp function works.

Function InStr

The InStr function allows you to determine whether one string contains another string.

Syntax

StrComp(String1, String2[, Compare])

String1, String2 - any valid string expressions. The function checks whether String1 is contained in String2.

Compare is an optional argument that can be any of the following predefined constants (if omitted, the current Option Compare setting is used):

• vbBinaryCompare - binary comparison of two strings;
• vbTextCompare - text comparison of two strings;
• vbDatabaseCompare - used only in Microsoft Access.

Start - an optional argument, is a numeric expression and specifies the position of the character in String1 from which the check should begin.

InStr returns a number indicating the position of the character in String1 where String2 was found. If InStr does not find String2 in String1, then 0 is returned. If String1 (or String2) is Null, then the function also returns Null.

Line splitting

Sometimes it becomes necessary to split a string into its component parts. In VBA, three functions solve this problem: Left, Right, Mid.

Left function

Syntax

Left (String, Length)

The function returns a copy of the String, starting with the first character and including the number of characters specified by Length. If Length is a number greater than the actual length of the String, then the entire String expression is returned.

Right function

Syntax

Right (String, Length)

String is any valid string expression.

Length - any numerical value.

The function returns a copy of the String, starting with the last character and including, from right to left, the number of characters specified by Length. If Length is a number greater than the actual length of the String, then the entire String expression is returned. The Right function always copies characters from the end of a string to the beginning.

Mid function

Syntax

Mid (String, Start, [, Length])

String is any valid string expression.

Length, Start - any numerical values.

The Mid function returns a copy of the String starting at the character position in the String specified by the Start argument. The optional Length argument specifies the number of characters to copy from String to Mid. If Start contains a greater number than the actual length of the String, then an empty string is returned.

Characters that cannot be entered from the keyboard

It often happens that you need to enter a character for which there is no key on the keyboard (for example, a copyright symbol). Another situation is when you need to include a VBA service character in a string (the most common case is the inclusion of double quotes).

To include characters in a string that cannot be entered from the keyboard, or that have special meaning to VBA, use the function Chr.

Syntax

Chr (Charcode)

Charcode - any numeric expression that is a valid code for the character set used by a computer. Must be an integer between 0 and 255.

The Chr function takes a single character code as an argument and returns a string containing the character corresponding to that code. This function is used in the above listings to feed a line when displaying a message on the screen Chr (13).

Because the characters used to start a new line are so important when formatting messages and other string data manipulated by VBA routines, there are several predefined constants for these characters to avoid the need to use the Chr function:

• vbCr is a carriage return character. Equivalent to Chr(13)
• vbLf - offset character by one line. Equivalent to Chr(10)
• vbCrLf - carriage return + offset character by one line. Equivalent to Chr(13)+ Chr(10)
• vbTab - tab character. Chr(9) equivalent

To view the list code-symbol you need to open the VBA help system and upon request "character sets" The corresponding table will be presented.

Formatting Data Values

Very often, for one reason or another, the data format at the output of a program does not completely suit us. This problem is solved by the function Format.

The VBA Format function is identical to the Format function in Excel and uses the same data formatting placeholders.

Syntax

Format (Expression [, Format[, Firstdayofweek [, Firstweekofyear]]])

Expression - any valid expression (required argument).

Format is a valid expression of a named or user-defined format.

Firstdayofweek is a constant that specifies the first day of the week.

Firstweekofyear - a constant that defines the first week of the year.

To use the Format function, you must either set a predefined format, or create an image of a specific format, using placeholder characters.

Named formats for use with the Format function

Named format	Action
General Date	Formats date and time information into a sequential date number using the date and time format settings for this computer.
Long Date	Formats only the date portion into a sequential date, using the computer's Long date format settings.
Medium Date	Formats only the date portion as a sequential date, using your computer's Medium date format settings.
Short Date	Formats only the part containing the date into a sequential date, using the computer's settings for the Short date format.
Long Time	Formats only the time portion into a sequential date, using the computer's Long time format settings.
Medium Time	Formats only the time portion into a sequential date, using the computer's Medium time format settings.
Short Time	Formats only the time portion into a sequential date, using the computer's settings for Short time format.
General Number	Formats a number into a string without any special characters.
Currency	Formats a number with a currency symbol, a thousands separator, and only two decimal places.
Fixed	Formats a number so that there is always at least one digit before the decimal point and at least two digits after it.
Standard	Formats a number with a thousands separator so that there is at least one digit before the decimal separator and at least two digits after it.
Percent	Formats a number as a percentage by multiplying it by 100 and adding a percent symbol.
Scientific	Formats a number into regular exponential format.
Yes/No	The string "Yes" is returned if the formatted number is non-zero, otherwise - "No".
True/False	The string "True" is returned if the formatted number is non-zero, otherwise - "False".
On/Off	The string "On" is returned if the formatted number is non-zero, otherwise - "Off".

Placeholders for creating custom formats

Placeholder character	Action (using the number 1234.45 as an example)
0	A numeric character that displays a digit if one is in that position, or 0 if it is not. You can use the 0 symbol to display leading zeros for integers and trailing zeros for decimals: 00000.000 displays 01234.450
#	A numeric symbol displays a digit if there is one in this position, otherwise it does not display anything. The # filler character is equivalent to 0, except that leading and trailing zeros are not displayed: #####.### displays 1234.45
$	Displays dollar sign: $###,###.00 displays $1,234.45
.	Decimal placeholder character, displays the decimal point at the designated position in the 0 placeholder character string: #.##.## displays 1234.45
%	Percent symbol, multiplies the value by 100 and adds a percent sign at the position indicated by the placeholders 0:#.#0.00% displays the number 0.12345 as 12.35% (rounding occurs)
,	Thousands separator, adds commas as thousands separators in 0 and # placeholder strings: ###,###,###.00 displays 1,234.45
E-e-	Displays the value in exponential format with an exponent for negative values ​​only: #.####E-00 displays 1.23445E03
E+ e+	Displays the value in exponential format with an exponent for both negative and positive values: #.####E+00 displays 1.23445E+03
/	Separates day, month, and year for formatting date values.
m	Specifies how to display months in dates: m displays 1, mm - 01, mmm - Jan, mmmm - January
d	Specifies how to display days in dates: d displays 1, dd - 01, ddd - Sun, dddd - Sunday
y	Displays the day of the year as a number from 1 to 366
yy	Specifies how to display years in dates: yy - 66, yyyy - 1966
q	Displays the quarter of the year as a number from 1 to 4
w	Displays the day of the week as a number (1 - Sunday)
ww	Displays the week of the year as a number from 1 to 54
:	Separates hours, minutes and seconds in time format values: hh:mm:ss - 01:01:01, h:m:s - 1:1:1
h	Specifies how to display the hours: hh - 01, h - 1
m	Specifies how to display minutes: mm - 01, m - 1
s	Specifies how to display seconds: ss - 01, s - 1
AM/PM	Displays time in 12-hour time format with AM or PM added
@	A character placeholder that displays a space if there is no matching character in the formatted string.
	Displays all characters in uppercase.
>	Displays all characters in lowercase.

Chr

Chr function

Chr(CharCode)
Chr$(CharCode)
ChrB(CharCode)
ChrW(CharCode)

Function Chr(Ch aracte r) allows you to get a character by the value of its ANSI or Unicode numeric code

Return value

Functions Chr, ChrB, ChrW return a value of the String subtype of type Variant containing the character corresponding to the specified ANSI or Unicode character code. Functions Chr$, ChrB$, ChrW$ return a value of String type accordingly

Note

Chr And Chr$ return a character by its ANSI encoding
ChrB And ChrB$ return single-byte line
ChrW returns a Unicode character, however on non-Unicode systems its behavior is similar Chr
Use in parameter CharCode values ​​greater than 255 generate runtime errors 5: Invalid procedure call or argument or 6: Overflow

Options CharCode The required argument is a Long value that specifies the character. Usually the function Chr used when inserting non-printable characters into text strings (carriage return, line feed, tabulator, etc.). Codes 0-31 correspond to standard ASCII control characters. For example, Chr(10) returns newline character Example Dim retval retval = Chr(65) Debug.Print retval " returns A Category

In the section on the question People, please explain in detail what the function ord(x) and its inverse chr(x) mean in Pascal? given by the author Nick Brown the best answer is There is a special table of characters (extended ASCII code, when each character has a numeric value, in total there are 255 values ​​in the table, that is, each character is assigned a number,
chr(0..254) converts a number (one byte) into the character to which this number belongs, for example chr(71) the result will be the letter "G", also pressing keys on the keyboard produces its own code, this is exactly the code when placed in this operator and will give the result which key is pressed, this is me for example. But the ORD(x) operator does this process in reverse order.
We set the value character and get a number from 0 to 254 (one byte)
for example ORD("G") will give us the result 71 (in decimal format)
That's all it seems!!!

Answer from Katyonachik[newbie]
It's simple, really)
Ord ("X") - will return the code of the character X from the ASKII table.
The result of the inverse function will be a symbol that corresponds to the entered code (from which the function is taken) from the same ASCII table.

Answer from Juri[guru]
These are not reciprocal functions!
The Ord function returns the ordinal value of an enumerated type.
The count starts from zero.
For the byte type, the function will return the actual value - the ordinal number coincides with the value.
For the char type, the function will return the serial number of the character, which (the number is random) coincides with the ASCII code.
The chr function returns the character with the given ASCII code.
Conclusion: Simply, with regard to symbolic values, they really work as the inverse of each other...
Therefore, we can say that the ord function is the inverse of the chr function, but not vice versa - the chr function is not the inverse of the ord function, since the scope of the ord function is not limited to working with symbols!