Wondershare EdrawMind
MindMap Gallery VBA
- 169
VBA
VBA is a powerful tool that helps users automate and optimize tasks in Microsoft Office applications.
Edited at 2024-04-10 14:10:09
- Recommended to you
- Outline
VBA
program
Program composition
module
process
code
object
object.property
object.method
Example: Macro recording
Set shortcut keys
Specify button
Assign frequently used taskbar buttons
Set security level
Programming environment VBE
Enter the editor
Alt F11
Development Tools—Visual Basic
Right-click on the worksheet label—view code
Development Tools—View Code
Main window
Project Explorer
Excel object
form object
Module vs class module
concept
Save VBA code
Create a specific class or object
Name after creation
Module 1, Module 2...
Category 1, Category 2...
Properties window
code window
Immediate window
Menu Bar
toolbar
Simple operation
Module addition and removal
Programming
help
basic grammar
type of data
type of data
Byte byte type
1
0~255, integer
age
Overtime days
Boolean type
2
Logical judgment, True/False
Integer type
2
-2768~32767, integer
Number of people
%
Long long integer type
4
-2147483648~2147483647, integer
Population
&
Single single precision floating point type
4
-3.402823E38~-1.401298E-45
PI
!
Double double precision floating point type
8
Negative number: -1.79769313486232E308~ -4.94065645841247E-324
Positive number: 4.94065645841247E-324~1.79769313486232E308
Basic wage
#
Currency currency type
8
-922,337,203,685,477.5808~922,337,203,685,477.5807
@
Decimal decimal type
Without decimals: /-79,228,162,514,264,337,593,543,950,335
Including decimals: /-7.9228162514264337593543950335
Smallest non-zero value: /-0.00000000000000000000000000001
14
Date date type
Date range: January 1, 100 to December 31, 9999
Time range: 0:00:00~23:59:59
8
date
String string type - variable length
10 string length
0 ~ about 2 billion
Name, job number
$
String string type-fixed length
string length
1 ~ about 65,400
$
Object
4
Object variables, used to reference objects
Variant
In addition to fixed-length String data and user-defined types, it can contain any kind of data.
If it is a numerical value, the maximum range can be Double.
If it is a character, it is consistent with the variable-length String range.
Custom
The range of each element is the same as the range of its own data type
constants and variables
declare variables
Declare multiple variables
Force declaration of variables
variable scope
single process
Dim/Static, acts on this process, local variable
single module
Dim/Private, acts on the module where it is located, module-level variables
All modules
Public, applies to all modules, announces variables
variable assignment
Data type variable assignment
Object variable assignment
constant
constant assignment
constant assignment
array
Array characteristics
Share a name: array name
Multiple variables of the same type form an array
The elements are stored in the array in order and are distinguished by index numbers.
Arrays are variables
Declare array
array dimensions
Declare dynamic array
Other ways to create
Array function
Split function
Range function
UBound/LBound function
Join function
Array writing to cell
object
Attributes
relativity
method
Share a name: array name
operator
arithmetic
Sum
-
Find the difference, find the opposite
*
Product
/
Seek business
\
Divisible
^
index
Mod
Take remainder
Compare
=
equal
expression1=expression2
Returns True when the two expressions are equal, False otherwise.
less than
expression1<expression2
Returns True when expression1 is less than expression2, otherwise returns False
>
more than the
Expression1>Expression2
Returns True when expression 1 is greater than expression 2, otherwise returns False
less than or equal to
expression1<=expression2
Returns True when expression1 is less than or equal to expression2, otherwise returns False
>=
greater than or equal to
Expression1>=Expression2
Returns True when expression1 is greater than or equal to expression2, otherwise returns False
not equal to
Expression1<>Expression2
Returns True when expression1 is not equal to expression2, otherwise returns False
Is
Compare reference variables of two objects
Object 1 Is Object 2
Returns True when object 1 and object 2 refer to the same object, otherwise returns False
Like
Compare two strings to see if they match
String 1 Like String 2
Returns True when string 1 matches string 2, otherwise returns False
connect
The expressions on both sides are text strings, and the connection is performed; the numerical values are calculated.
&
Regardless of the data type of the operator, the connection operation is performed
logic
And
and
Expression1 And Expression2
If the values of expression 1 and expression 2 are both Ture, return Tue, otherwise return False
Or
or
Expression1 Or Expression2
If one of the values of expression 1 and expression 2 is True, Tue will be returned, otherwise False will be returned.
Not
No
Not expression
If the value of the expression is True, return False, otherwise return True
Xor
XOR
Expression1 Xor Expression2
When the values of expression 1 and expression 2 are different, True is returned, otherwise False is returned.
Eqv
equivalence
Expression 1 Eqv Expression 2
When the values of expression 1 and expression 2 are the same, True is returned, otherwise False is returned.
Imp
contains
Expression1 Imp Expression2
The value of expression 1 is True, and when the value of expression 2 is False, False is returned, otherwise Ture is returned; equivalent to Not expression 1 or expression 2
wildcard
*
Replace any number of characters
?
replace any character
#
replace any number
[charlist]
Replace any character in [charlist]
[!charlist]
Replace any character not in [charlist]
order of operations
brackets
()
index
^
Find the opposite number
-
Multiplication and division
*,/
Divisible
\
Take remainder
Mod
Addition and subtraction
,-
String concatenation
&,
comparison operation
=,<>,<,>,<=,>=,Like,Is
logic
And
Or
Not
Xor
Eqv
Imp
built-in functions
Basic sentence structure
Commonly used statements
If...Then statement
Select Case statement
For...Next statement
Do While statement
Do Until statement
For Each...Next statement
GoTo statement
With statement
basic process
Sub process
writing location
Declare Sub procedure
Execute Sub process
Scope
public process
private process
Function process
writing location
Custom function
Code layout
indentation
short line
Merge multiple lines
Comment
Set (un)comment block
Common objects
Common objects
Application
Excel Application
Workbook
workbook
Worksheet
worksheet
Range
Single cell or range of cells
Application
ScreenUpdatingProperty
DisplayAlertsProperty
EnableEventsProperty
WorksheetFunctionProperty
Application common properties
ActiveCell
Currently active cell
ActiveChart
The active chart in the currently active workbook
ActiveSheet
The active worksheet in the currently active workbook
ActiveWindow
currently active window
ActiveWorkbook
Currently active workbook
Charts
All chart worksheets in the currently active workbook
Selection
All selected objects in the currently active workbook
Sheets
All Sheet objects in the currently active workbook, including ordinary worksheets, chart worksheets, Ms Excel 4.0 macro table worksheets and Ms Excel 5.0 dialog worksheets
Worsheets
All Worksheet objects in the currently active workbook (ordinary worksheets)
Workbooks
All currently open workbooks
Example: Excel interface settings
Workbook
Quote
The index number
name
Common operations
New
Open
activation
save
closure
ThisWorkbook/ActiveWorkbook
Worksheet
Quote
Index number or tag name
code name
Common operations
Create new worksheet
Change worksheet labels
delete worksheet
Activate worksheet
Copy worksheet
Move worksheet
Hide/show worksheet
Get the number of worksheets
Sheets
Range
Quote
Cell
Attributes
content
number
address
operate
Select cells
Selectively clear cells
Copy cell range
Cut cells
delete cell
Other operations
Quote the entire line
Reference the entire column
Union method of Application object
Offset property of Range object
Resize property of Range object
UsedRange property of Worksheet object
CurrentRegion property of Range object
End property of Range object
xlToLeft
Left, source cell <Ctrl left arrow key>
xlToRight
Right, source cell <Ctrl right arrow key>
wxya
Up, source cell <Ctrl up arrow key>
xlDown
Down, source cell <Ctrl down arrow key>
Other common objects
name
Cell comments
Beautify cells
font
Shading
frame
other
Example: Typical operation
Create workbook
Determine whether the workbook is open
Determine whether the workbook exists
Enter data into a closed workbook
Hide all worksheets except the active worksheet
Create new worksheets in batches
Classify data in batches
Save worksheet as new worksheet
Quickly merge data from multiple tables
Summarize data from multiple workbooks in the same folder
Create a directory for the worksheet
Excel events
Worksheet event
Worksheet event list
Activate
Occurs when a worksheet is activated
BeforeDoubleClick
After double-clicking the worksheet, the default double-click operation occurs before
BeforeRightClick
After right-clicking the worksheet, the default right-click operation occurs before
Calculate
Occurs after recalculating the worksheet
Change
Occurs when a cell changes
Deactivate
Occurs when changing from an active worksheet to an inactive worksheet
FollowHyperlink
Occurs when any hyperlink in the stand-alone worksheet
PivotTableUpdate
Occurs after updating pivot table
SelectionChange
Occurs when the selection changes
Automatically prompt for changes
Select object
Run a program when a worksheet is activated
Disable selection of other worksheets
Worksbook events
Activate
Occurs when a worksheet is activated
AddinInstall
Occurs when the workbook is installed as an add-in
AddinUninstall
Occurs when the workbook is unloaded as an add-in
AfterXmlExport
Occurs after saving or exporting the XML data in the specified workbook
AfterXmlImport
Occurs after an existing XML data connection is refreshed or new XML data is imported into any open workbook
BeforeClose
Occurs before closing the workbook or, if changes have been made, before asking the user whether to save changes.
BeforePrint
Occurs before printing the specified workbook or any of its contents
BeforeSave
Occurs before saving the workbook
BeforeXmlExport
Occurs before saving or exporting the XML data in the specified workbook
BeforeXmlImport
Occurs before the existing XML data connection is refreshed or new XML data is imported into any open workbook
Deactivate
Occurs when a workbook transitions from active to inactive state
NewSheet
Occurs when creating a new worksheet in the workbook
Open
Occurs when opening a workbook
PivotTableCloseConnection
Occurs after the PivotTable connection is closed
PivotTableOpenConnection
Occurs after the PivotTable connection is opened
SheetActivate
Occurs when any worksheet is activated
SheetBeforeDoubleClick
Occurs when any worksheet is double-clicked (before the default double-click operation)
SheetBeforeRightClick
Occurs after right-clicking any worksheet or drawing changed data on the icon
SheetCalculate
Occurs when recalculating the worksheet or after plotting changed data on the chart
SheetChange
Occurs when a cell in any worksheet is changed
SheetDeactivate
Occurs when a worksheet changes from the active worksheet to the inactive worksheet
SheetFollowHyperlink
Occurs when any hyperlink in the workbook is clicked
SheetSelectionChange
Occurs when the selection changes on any worksheet (except chart worksheets)
Sync
Occurs when a local copy of a workbook that is part of a Document Workspace is synchronized with the server copy
WindowActivate
Occurs when any workbook window is activated
WindowDeactivate
Occurs when any workbook window changes from active to inactive.
WindowResize
Occurs when any workbook window is resized
automatic program
MouseMove event
Application method
OnKey
OnTime
Example: Typical techniques
Quickly enter data
Find data quickly
Automatically save files
user interface design
control
form control
Label
For entering and displaying static text
group box
Used to combine multiple other controls
button
Used to execute macro commands
checkbox
Selection control, multiple selections possible
option button
Selection, usually several option buttons are used together in a combo box, and only one of them can be selected at the same time
list box
Displays a list of multiple options from which to choose one
Combo Box
Provides multiple options to choose from, one of which can be selected
scroll bar
Includes horizontal and vertical
Spinner controls
Click the control's arrow to select a value
ActiveX control
User interaction
InputBox function
InputBox method of Application object
MsgBox function
show button
show icon style
Default button
Dialog type
return value
FindFile method of Application object
GetOpenFilename method of Application object
GetSaveAsFilename method of Application object
FileDialog method of Application object
userform
Add form
form properties
Add controls within the form
show form
Manual
code
model
close form
Manual
code
Use controls
Initialize form
command button
Data Entry
Keyboard control
Examples: Typical techniques and examples
Questionnaire design
Employee information management
Login form
Code debugging and optimization
mistake
Compile Error
Run error
logical error
program status
Design Patterns
operating mode
Interrupt mode
Debugging tools
Interrupt mode
Run error
Compile Error
interrupt execution
Breakpoint settings
Stop statement
Immediate window
local window
watch window
Error handling
Go Error GoTo Tags
On Error Resume Next
On Error GoTo 0
Make your code run faster
Variable settings
Declare variables to be of appropriate data type
Try not to use Variant data
Prevent variables from always being stored in memory
Avoid referencing the same object repeatedly
with statement simplified
Use variables to simplify referencing objects
Use functions as much as possible
Remove redundant activations and selections
Proper use of arrays
Turn off screen updates
About this map