Common Excel VBA programming code

Anyone who has used VB should know how to declare variables. The declaration variables in VBA are exactly the same as those in VB!

Use Dim statements
Dim a as integer declares a as an integer variable.
Dim a' declares a as a variant variable
Dim a as string 'declares a as a string variable
Dim a as currency, B as currency, c as currency 'declares a, B, and c as currency Variables
......
Description variables can be Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (not supported currently), Date, String (variable-length String only), String * length (Fixed length String), Object, Variant, user-defined type or Object type.

Force declare variable
Option Explicit
Note: This statement must appear in the module before any process.

Declare Constants
Used to replace the text value.
Const

'Constant is Private by default.
Const My = 456

'Declare the Public constant.
Public Const MyString = "HELP"

'Declare the Private Integer constant.
Private Const MyInt As Integer = 5

'Declare multiple constants in the same row.
Const MyStr = "Hello", MyDouble As Double = 3.4567.

Select the region where the current cell is located

In EXCEL97, there is a very good function. It is to place the mouse in a valid data cell and execute the code section, you can select all the pieces of data that are connected together. You only need to add the code section to your module.
Sub My_Select
Selection. CurrentRegion. Select
End sub

Returns the value after spaces before and after data is deleted in the current cell.
Sub my_trim
Msgbox Trim (ActiveCell. Value)
End sub

Cell displacement
Sub my_offset
ActiveCell. Offset (0, 1). Select 'the current cell moves one cell to the left
ActiveCell. Offset (0,-1). Select 'the current cell moves one cell to the right.
ActiveCell. Offset (1, 0). Select 'the current cell moves down a grid
ActiveCell. Offset (-1, 0). Select 'the current cell moves up a grid
End sub
If the above program produces an error because the cell cannot be moved, we can
Add a code under sub my_offset on error resume next

 

Note that the following code does not add sub "code name" or end sub. Please add it yourself!

Assign a value to the current cell
ActiveCell. Value = "Hello !!! "

Assign a value to a specified Cell
For example, the content of cell A1 is set to "HELLO"
Range ("a1"). value = "hello"

Another example: If your current workbook is on sheet1, You need to insert "HELLO" to the A1 cell of sheet2"
1.
Sheets ("sheet2"). select
Range ("a1"). value = "hello"
Or
2.
Sheets ("sheet1"). Range ("a1"). Value = "hello"

Note:
1. sheet2 is selected, and then "HELLO" is assigned to cell A1.
2. If sheet2 does not need to be selected, "HELLO" can be assigned to the A1 cell of sheet2.

Hide a worksheet
'Hide the sheet 1 worksheet.
Sheets ("sheet1"). Visible = False

'Show SHEET1 this worksheet
Sheets ("sheet1"). Visible = True

Print preview
Sometimes we want to print and preview all the SHEET in EXCEL. Please use this section of code, which will be in your existing workbook Loop Until the end of the last workbook.
Dim my As Worksheet
For Each my In Worksheets
My. PrintPreview
Next my

Obtain the address of the current cell.
Msgbox ActiveCell. Address

Obtain the current date and time.
Msgbox date & chr (13) & time

Protect workbooks
ActiveSheet. Protect

Unprotect workbooks
ActiveSheet. Unprotect

Rename the activity worksheet "liu"
ActiveSheet. Name = "liu"

Open an application
AppActivate (Shell ("C: \ WINDOWS \ CALC. EXE "))

Add a worksheet
Worksheets. Add

Delete an activity Worksheet
Activesheet. delete

Open a workbook File
Workbooks. Open FileName: = "C: \ My Documents ents \ Book2.xls"

Close activity window
ActiveWindow. Close

Cell format
Left alignment of selected cells
Selection. HorizontalAlignment = xlLeft

Center selected cells
Selection. HorizontalAlignment = xlCenter

Right alignment of selected cells
Selection. HorizontalAlignment = xlRight

The selected cell is in percent format.
Selection. Style = "Percent"

The selected cell font is in bold.
Selection. Font. Bold = True

The selected cell font is italic.
Selection. Font. Italic = True

The font of the selected cell is 20 20 20.
With Selection. Font
. Name = ""
. Size = 20
End

With statement

With object
. Description
End

Clear Cells
ActiveCell. clear' deletes all text, comments, and formats

Returns the number of rows in the selected region.
MsgBox Selection. Rows. Count

Returns the number of columns in the selected region.
MsgBox Selection. Columns. Count

Returns the address of the selected region.
Selection. Address

Ignore all errors
ON ERROR RESUME NEXT

Jump in case of an error
On error goto err_handle
'Other code in the middle
Err_handle: 'tag
'Code after redirection

Delete an object
Kill "c: \ 1.txt"

Customize your own status bar
Application. StatusBar = "current Time:" & Time

Restore the status bar
Application. StatusBar = false

Execute a macro using code
Application. Run macro: = "text"

Scroll the position of the window to a1
ActiveWindow. ScrollRow = 1
ActiveWindow. ScrollColumn = 1

Custom system date
Dim MyDate, MyDay
MyDate = #12/12/69 #
MyDay = Day (MyDate)

Returns the time of the current day.
Dim MyDate, MyYear
MyDate = Date
MyYear = Year (MyDate)
MsgBox MyYear

Inputbox <input box>
XX = InputBox ("Enter number of months to add ")

Get a file name
Dim kk As String
Kk = Application. GetOpenFilename ("EXCEL (*. XLS), *. XLS", Title: = "Tip: open an EXCEL file :")
Msgbox kk

Open zoom dialog box
Application. Dialogs (xlDialogZoom). Show

Activate font dialog box
Application. Dialogs (xlDialogActiveCellFont). Show

Open another dialog box
Dim kk As String
Kk = Application. GetSaveAsFilename ("excel (*. xls), *. xls ")
Workbooks. Open kk