Basic concepts of VBA

1. VBA object

Almost 90% of VBA programs operate on objects. VBA has corresponding objects, attributes, methods, and events. objects are the core

A) how to operate objects

Object. attribute, object. method, parent object. Sub-object. Attribute

For example, sheets ("worksheet"). Name ---- sheets ("worksheet") is an object, and name is an object attribute.

Workbooks (2). Close ----- the former is an object, and the close is an object method.

Range ("A1: a100"). Comment. Delete -------- range is the parent object. comment is the sub-object. Delete is the method.

B) Common objects and their meanings

I. Application represents the entire Excel application

Ii. window Indicates window

Iii. worksheet represents a worksheet

Iv. Sheets execution or a set of all worksheets in the active workbook

V. shaperange represents the shape area, which is a set of shapes in the document.

Vi. PivotTable indicates the pivot table on the worksheet.

VII. Workbook represents an Excel Workbook

VIII. shape represents objects in the drawing layer, such as custom images, arbitrary polygon, OLE objects, or images

IX. range indicates a cell, a row, a column, a selected area, or a 3D area.

X. name indicates the definition name of the cell area. The name can be a built-in name (such as print_area) or a custom name.

XI. Chart indicates the chart in the workbook.

XII. filedialog provides a file dialog box, which is similar to the standard 'open' and 'save' dialog boxes in office applications.

XIII. commandbarpopup represents a pop-up control on the command bar.

XIV. commandbar represents a command bar in the container Application

C) attributes of VBA

An attribute is an external and internal feature of an object, including the size, color, margin, quantity, or behavior of one aspect, such as whether it can be activated, visible, or refreshed, modifies the feature of an object by modifying its attribute values. You can press f2 to view all members when prompted, right-click to display implicit members, or view help to search for members of the worksheets object.

D) VBA Method

VBA requires the object to be in the money. After the method is in, for example, worksheets. Add ---- worksheets indicates the worksheet object, and add indicates the method to create. Next, let's take a look at the worksheets method list.

I. Add create a worksheet, chart, or macro table. The newly created worksheet becomes an active worksheet.

Ii. Copy copy the worksheet to another location in the workbook

Iii. delete objects

Iv. fillacrossheets: copy the cell area to the same location of all other worksheets in the set.

V. Move move the worksheet to another location in the workbook

Vi. printout Print Object

VII. printpreview: displays the preview of an object based on the appearance of the printed object.

VIII. Select select object

E) VBA events

Time is the action triggered by an object in a certain state. Each object has many events and different trigger conditions at different times.

I. Event example

Private sub workbook_open ()

Sheets (1). Select

[A1] = "Zhang Shuai peng"

End sub

When sheet1 is opened, A1 is assigned a string.

Ii. Classification and usage of events

There are many types of events in VBA, and classification table mixing is determined by objects.

Application application events

Workbook workbook events

Worksheet worksheet event

Chart chart event

Userform event

Label event (controls in the form)

Image event (ActiveX Control)

F) VBA Operator

I. Arithmetic Operators

^ Calculate a certain power of a number, such as a ^ 2

* Multiplication

/Division

\ Triggers two numbers and returns an integer

MoD calculates the remainder of two numbers

+ Addition operation

-Subtraction

& Character connection

Ii. Comparison Operators

<Less

<= Less than or equal

> Greater

> = Greater than or equal

= Equal

<> Not equal

Like and is comparison string operations

Iii. logical operators

And is used for logical connection between two expressions of the object.

Eqv is used to perform logical equivalence operations on two expressions.

IMP is used to perform logical implication operations on two expressions.

Not is used to perform logical negative operations on expressions.

Or is used to perform Logical Analysis on two expressions.

XOR is used to perform logical mutex or operations on two expressions.

Iv. Annotations

Comments after the end of a line of code or after a line of 'single quotes'

G) simple character processing functions

I. Option compare [binary | text | database]

If you do not add the following parameters, the default comparison method is used. Default comparison method at the module level

Bianary case-insensitive text sorting level

Option compare binary

Sub character comparison ()

Msgbox "A"> "A" 'returns true

End sub

Text is case sensitive

Option compare text

Sub character comparison ()

Msgbox "A"> "A" 'returns false.

End sub

Ii. strcomp (string1, string2, [compare]) character Similarity comparison

If string1 is less than string2, return-1.

Return value greater than 1

Returns 0

If string1 or string2 is null, null is returned.

Iii. strconv (string, conversion, lcid) string type conversion

The second conversion parameter indicates the conversion type. nine types of text can be converted based on different parameters.

Vbuppercase (constant) 1 (value) converts the characters in the string to uppercase.

Vblowercase 2 converts the characters in the string to lowercase letters.

Vbpropercase 3 converts the start letter of each character in the string into uppercase letters.

Vbwide 4 * converts a single-byte string into double-byte characters

Vbnarro 8 * converts double-byte characters in a string into single-byte characters

Vbkatakanna 16 ** converts a hirakana character in a string into a katakana character

Vbhiraganra 32 ** converts a katakana character in a string to a hirakana character

Vbunicode 64 converts a string to Unicode

Vbfromunicode # convert a string from Unicode to the default code page of the system

Code:

Sub strconv ()

Msgbox strconv ("enlish", vbuppercase) & CHR (10) & strconv ("enlish", vblowercase) & CHR (10) & strconv ("English", vbpropercase) & CHR (10) & strconv ("English", vbwide)

 

End sub

Iv. Format (expression [, format [, firsdayofweek [, firstweekioyear]): format characters

Code:

Sub date ()

Msgbox format (date, "yyyy Year M month") & CHR (10) & format (date, "AAA") & CHR (10) & format (now, "H ") & "o'clock", 64, "now"

End sub

V. lcase (string)/ucase (string)

Converts the string to uppercase or lowercase and returns the result.

Vi. String (number, character)/space (number)

Returns a string of repeated characters.

String (5, "*") returns ***** space (5), and returns five spaces.

VII. lset/rset

In a string variable, the string is aligned to the left, or the user-defined type variable is copied to another user-defined type variable. In other words, the string is selected by string. If the length of string 2 is less than the string length, it is filled with spaces. Otherwise, the length of string 1 starts from the left.

Tragedy: I just understood what it meant. It turned out to be a trade-off between the following strings based on the length of the first string. If the length is not long enough, it will be filled with spaces.

Code: Sub lset usage ()

Dim str1

Str1 = "Guangzhou 11111111111111"

Lset str1 = "Guangzhou Honda"

Msgbox "[" & str1 & "]"

Str1 = "manufacturer"

Lset str1 = "Guangzhou Honda manufacturing factory"

Msgbox str1

End sub

VIII. instr ([start,] string1, string2 [, compare]) returns the position where the string appears.

The third parameter specifies whether it is case sensitive,

Vbusecompareoption-1 use the option compare statement to set and execute a comparison

Vbbinarycompare 0 performs a binary comparison (Case Insensitive)

Vbtextcompare 1 executes a comparison based on the original text (case sensitive)

For example: instr (4, "I am a good guy", "good", 1) returns coordinates

IX. Left/Mid/right: select from left, center, and right

Left (string, length) returns the specified number of characters on the left of the string.

Mid (string, start [, length]) returns a string containing a specified number of characters starting from the center.

Right (string, length) returns the specified number of characters that contain the string from the right.

X. ltrim (string)/rtrim (string)/trim (string) Remove Spaces

Returns a copy of its parameter string. No leading blank (ltrim) trailing blank (rtrim) or leading and trailing blank (TRIM)

XI. Like string Similarity comparison

Result = string like pattern

If String matches pattern to true, otherwise result is false. If one of them is null, it is also null.

Wildcard characters can be used in pattern.

? Any single character

* 0 or multiple characters

# Any number (0-9)

[Charlist] any single character in charlist

[! Charlist] no longer any single character in charlist

 

Simple verification case implementation:

Insert a user form, add a text box, and double-click it to register its change event.

Private sub textbox#change ()

If Len (textbox1.text)> 0 then

Ifright (textbox1.text, 1) like "[A-Z]" then exit sub else me. textbox1 = left (textbox1.text, Len (textbox1.text)-1)

End if

End sub

Effect. If you enter any character, you cannot enter any character except lowercase letters, including numbers, punctuation marks, and Chinese characters.