Underlake Reading Room

这里是图书馆外的阅览室🌟

0%

关于Excel的笔记 - VBA

Posted on Edited on In Disqus: 15k 13 mins.

VBA它来了.

Excel的笔记.

File > option > customize ribbon > click developer in main tabs.
添加后在developer > visual basic就可以to the developer window.
When saving, save file as macro-enabled workbook.



Macro

The easiest way to create VBA code is by recording a macro.

Developer > View> Macro > record macro

After recording the macro, go to macro tab, then find the macro’s name (that you recorded).
有了macro之后可以在developer -> control ribbon下面 -> insert添加buttom -> assign macro.
Right click button 可以edit text.

Use relative references 是一个选择(boolean),如果不选就会默认record macro的时候使用absolute references.

Macro names cannot include spaces. Use an underscore or camel case.



Set up

Immediate Window(ctrl + G),可以看到variable更改值的地方,很方便.
可以当作的terminal用.

打开visual basic后,在modules中的才是co macro码的地方.
如果record一个macro,会默认存到module1(module2, etc)中.
Visual basic里的window如果不小心关了再打开是view -> priority window.

Shortcut

The keyboard shortcut to open the VB Editor in any Windows version of Excel is Alt+F11.

用debug功能可以run your code one line by one line(按F8切下一行).

Ctrl+Y deletes the line of code that the text cursor is in.
没有undo shortcut,得手动edit -> redo typing.

Ctrl+i displays the screentip that you see writing or running code. This allows you to see all the parameters in a property, method, or function.





Data type

Data type Description
Integer Store values between -32,768 and 32,767, No decimal places
Long Store values between -2,147,483,648 to 2,147,483,647, No decimal places
Single -3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values, Numbers with decimal places
Double -1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values, Numbers with decimal places
String
Date
Variant Open to any data type

顺便一提只有string是(在cell中)靠左,其他value都是靠右.
日期会识别成数字type.甚至可以date+100来计算100天后的日期.

Strings are variable length by default. This code declares a fixed-length String of 20 characters:

NB: Once a fixed string has been declared, it cannot be resized

1
Dim myFixedString As String * 20


Subroutine

Most of the code are contain in Subroutines.
我把它当作像代码里function一样的存在.需要手动跑.

注:Excel中的function这个词,指的是前端,在cell内用的function,比如sum,max等.

1
2
3
4
5
Sub my_Macro()

	code goes here…

End Sub

使用variable要跟c一样declare和type.

1
2
3
4
5
Sub my_Macro()
	Dim myVariable As Integer
	myVariable = 20

End Sub

Variables can be created and assigned values without declaring them first, but, just don’t.



You can pass values (arguments) into a subroutine by way of parameters
Declared when the Sub is defined:

1
2
3
4
5
Sub changeFontSize(theCell As String, newFontSize As Integer)

    Range(theCell).Font.Size = newFontSize

End Sub

Arguments are supplied by the code calling the sub:

1
2
3
4
5
Sub myMacro()

	Call changeFontSize("A1", 20)

End Sub


Function

可以用在前端的function.
Defined with the Function statement, and should include a data type for the return value:

1
2
3
4
5
Function myFunction() As Integer

	myFunction = return value

End Function

Arguments are passed by the calling code:

1
2
3
4
5
Sub myMacro()

	MsgBox sayHello("John")

End Sub


Array

Arrays in VBA can be dynamic (the size is set after creation) or static:

1
2
Dim myArray1()   ' dynamic array
Dim myArray2(3)  ' static array of 3 elements

Arrays are of type Variant by default, but can be explicitly typed:

1
Dim myArray1(3) As Integer

Use the ReDim (redimension) statement to assign a size to a dynamic array:

1
ReDim myArray1(3)

Once a dynamic array is redimensioned, it becomes static. You cannot redimension a static array, or change its data type.

Assign values to an array by using index numbers:

1
2
3
4
5
Dim fruitArray(3) As String

fruitArray(0) = "Apple"
fruitArray(1) = "Orange"
fruitArray(2) = "Banana"

Arrays can also be populated with the Array VBA function:

1
2
Dim fruitArray As Variant
fruitArray = Array("Apple","Orange","Banana")

NB: If using the Array function, arrays are not declared with brackets and must be declared as Variant

如果要run through the array,得提前造好一个pointer.

1
2
3
4
5
6
Dim curShippingCharges(5) As Currency
Dim iCounter As Integer

For iCounter = 0 To 5
  curShippingCharges(iCounter) = ActiveCell.Offset(iCounter, 1).Value
Next iCounter


ActiveCell

Returns a Range object that represents the selected (active) cell in the displayed worksheet
Represents a single cell within the current selection, if more than one cell is selected
To refer to the ActiveCell on a different worksheet, activate the worksheet first. Example - to get the value from the active cell on Sheet2 (if Sheet2 is not the active worksheet):

1
2
3
4
5
6
7
8
Sub ActiveCellExample()

'How to activate a cell

Range("C31").Active
ActiveCell.Value = "Finance"

End Sub

Change value.

1
2
3
4
5
6
7
8
9
Sub IntegerData()

'Example of an integer

Dim ExampleData Name As Integer
ExampleData = 75
Cells(15, 2) = ExampleData '在row15columnB中写入ExampleData

End Sub


ctrl + shift + 8 -> 选择相近(包含)的active cell current region.

在VBA中全取一片相邻的active cell(data region)的话.

1
2
3
Dim strAddress As String

strAddress = ActiveCell.CurrentRegion.Address


Filter

1
2
3
4
5
6
7
8
9
Dim strAddress As String

'turn autofilter no or off,不会被先前原本的filter所干扰
Selection.AutoFilter

'选区
strAddress = ActiveCell.CurrentRegion.Address
'在选区下加第2列(column counting from left to right)的idaho的filter
ActiveSheet.Range(strAddress).AutoFilter Field:=2, Criterial:="Idaho"


当选择两项的情况.

1
2
3
4
5
6
7
8
9
10
11
12
Sub JustTwoItems()

Dim strAddress As String

Selection.AutoFilter

strAddress = ActiveCell.CurrentRegion.Address

ActiveSheet.Range(strAddress).AutoFilter Field:=2, Criteria1:="=Arizona", _
   Operator:=xlOr, Criteria2:="=California"

End Sub

空格和下划线好像是代码分行用的.

多项.

1
2
3
4
5
6
7
8
9
10
11
Sub MultipleItems()

Dim strAddress As String

Selection.AutoFilter

strAddress = ActiveCell.CurrentRegion.Address
ActiveSheet.Range(strAddress).AutoFilter Field:=2, Criteria1:=Array( _
        "Arizona", "California", "Oregon"), Operator:=xlFilterValues

End Sub


使用AdvancedFilter 来find unique.

1
2
3
4
5
6
7
8
9
10
11
Sub UniqueItems()

Dim strAddress As String

Selection.AutoFilter

strAddress = ActiveCell.CurrentRegion.Address

Range(strAddress).AdvancedFilter Action:=xlFilterInPlace, Unique:=True

End Sub

顺便一提可以去home > sort&filter > clear取消filter.

在worksheet里我们已经有unique这个function.但不推荐在vba中使用worksheet的unique.

Remove filter.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub RemoveFilter()

Dim strAddress As String

strAddress = ActiveCell.CurrentRegion.Address

ActiveSheet.Range(strAddress).AutoFilter Field:=2
'因为输入的filter是null 所以会remove filter
'remove第二列的所有filter

End Sub

Sub ClearAllFilters()

ActiveSheet.ShowAllData

End Sub


Offset

Returns a Range object that is offset from a specified cell or range by a given number of rows and/or columns
Positive row numbers move the offset down, negative numbers move the offset up
Positive column numbers move the offset to the right, negative numbers move the offset to the left

选择其他cell根据与offset的距离.

This example selects the cell that is 2 rows down and 3 columns to the right of the active cell:

1
ActiveCell.Offset(2,3).Select

This example enters a value of 25 in the cell 3 rows up from the active cell:

1
ActiveCell.Offset(-3,0).Value = 25


Field

在excel里面每一个cell都是一个object,有不同的field.
If you are writing code to work with a number of properties and/or methods of the same object, it is more efficient to use a With…End With block.

1
2
3
4
5
6
With Range("A1").Font
	.name = "Arial"
	.Size = 16
	.Bold = True
  .Color = vbRed
End With

Colour properties can be set in one of two ways:
ColorIndex: uses an index number between 1-56 to refer to a specific colour (0 means “no colour”)
Color: set by using the RGB function or a built-in constant

Excel里面也有build in constraint,一般以vb开头,比如.

1
Range("A1").Interior.Color = vbYellow


Range

An object that represents a single cell (e.g. A1) or a range of cells (e.g. A1:C10)
Name of the Range must be in A1-format, or a user-defined range name
Name can include the range operator (colon), intersection operator (space) or union operator (comma),

跟offset不同,可以选多个active cell.

This example enters a value of 25 in the cell B3 of the current worksheet:

1
Range("B3").Value = 25

This example changes the background colour of a user-defined range named redFill:

1
Range("redFill").Interior.Color = vbRed


Cell

另一种选区的方式.

Returns a Range object that represents all the cells on the worksheet (not just the cells that are currently in use)
Accepts Row Index and Column Index parameters to refer to specific cells

Example - this inserts a value of 100 in cell D5:

1
Cells(5, 4).Value = 100

A single numeric parameter will count from left to right, starting at 1 (cell A1) to 16384 (cell XFD1), and then wrap around to 16385 (cell A2)
If no argument is supplied, Cells will refer to all cells in the current worksheet. This example will colour the whole worksheet background yellow:

1
Cells().Interior.Color = vbYellow

Caution: Cells() will affect 17,179,869,184 individual cells!



Selection

Returns the currently selected object
If the selection is a range of cells, they can be manipulated as a block, or individually (with a For Each loop)

Examples: this will delete the contents of all cells in the selected range:

1
Selection.Clear

This will write the row number of the cell into each cell in the selection:

1
2
3
4
5
Dim myCell As Range

For Each myCell In Selection
	myCell.Value = myCell.Row
Next myCell


SpecialCells

选特定条件的cell.

Returns a Range object that represents all the cells that match a specific type
SpecialCells accepts a mandatory Type constant and an optional Value constant (if Type refers to formulas or constants).

Example: this will apply a yellow fill to all cells containing a formula:

1
Cells.SpecialCells(xlCellTypeFormulas).Interior.Color = vbYellow

This will apply bold text to all formulas returning a number:

1
Cells.SpecialCells(xlCellTypeFormulas, xlNumbers).Font.Bold = True


If condition

Condition里让我很痛苦的一点,是用于比较的=和assign value的=是一样的.
没有那样的==.
竟然没有.

1
2
3
If [logical test] Then
	code to run if true
End If
1
2
3
4
5
If [logical test] Then
	code to run if true
Else
	code to run if false
End If
1
2
3
4
5
6
7
If [logical test1] Then
	code to run if test1 is true
ElseIf [logical test2] Then
	code to run if test2 is true
Else
	code to run if both tests are false
End If


Select Case Statement:

1
2
3
4
5
6
7
8
9
10
Select Case [value]
	Case [pattern1]
		code to run if [value] matches [pattern1]
	Case [pattern2]
		code to run if [value] matches [pattern2]
	Case [pattern3]
		code to run if [value] matches [pattern3]
	Case Else
		code to run if [value] does not match any pattern
End Select


Loop

For

A For loop repeats a block of code until it meets a predefined end value. This example would run the code 5 times:

1
2
3
4
5
6
7
8
9
10
Dim startPoint As Integer
Dim endPoint As Integer

endPoint = 5

For startPoint = 1 To endPoint

	code to run

Next startPoint

Must assign a value to a variable as the starting point for the loop
Next increments the counter variable by 1 as default, but can be changed by adding Step [number] after the ending value on the For…To line



ForEach

Normally used with collections (e.g. a range of cells) and arrays
Cycles through each individual item in the collection/array

Example: This fills the range A1:A20 with the numbers 1 to 20:

1
2
3
4
5
6
7
8
9
Sub my_loop()
    Dim i As Integer
    i = 1

    For Each myCell In Range("A1:A20")
        myCell.Value = i
        i = i + 1
    Next myCell
End Sub


DoWhile

Includes a test condition, and loops while condition is true
Don’t need to set a start condition - just the end condition
Needs a line of code to increase the loop variable value, otherwise would create an infinite loop

This fills the range A1:A20 with the numbers 1 to 20:

1
2
3
4
5
6
7
8
9
Sub my_Loop()
Dim i As Integer
i = 1

    Do While i < 21
        Cells(i, "A").Value = i
        i = i + 1
    Loop    
End Sub


DoUntil

This fills the range A1:A20 with the numbers 1 to 20:

1
2
3
4
5
6
7
8
9
Sub my_Loop()
Dim i As Integer
i = 1

    Do Until i > 20
        Cells(i, "A").Value = i
        i = i + 1
    Loop    
End Sub


Exit

Exit statement followed by loop type will end the loop
Example: Exit For will end a For or For Each loop, Exit Do will end a While or Until loop

This fills the range A1:A10 with the numbers 1 to 10, even though endPoint is set to 20:

1
2
3
4
5
6
7
8
9
10
11
Sub my_Loop()
Dim startPoint As Integer
Dim endPoint As Integer
endPoint = 20
For startPoint = 1 To endPoint
    Cells(startPoint, "A").Value = startPoint        
        If startPoint = 10 Then
        Exit For
        End If
Next startPoint
End Sub


Event



Message box

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
Sub useMsgBox()
MsgBox "Hello world"

MsgBox "Hello " & ActiveCell.Value

MsgBox "The Sheet name is " & ActiveSheet.Name

MsgBox Range("b2") & ",  " & Range("C2")
End Sub

Sub useNewLine()
MsgBox "Top score" & vbNewLine & "Smith: 102"

End Sub

Sub useSpaceUnderscore()
MsgBox "this line goes on and on off the page so space and underscore " _
                     & " makes it easier to read "

Worksheets("revenue").Range("A1").CurrentRegion.Rows(1).Copy _
                       Destination:=Range("M2")

ActiveCell.Offset(0, 10).Value = _
       "Sentences which don't fit on a line can be continued on the next line"

End Sub

The MsgBox statement accepts additional optional constants to display icons and different sets of buttons
Example: this code generates a message box with Abort/Retry/Ignore buttons and a Critical Message icon. The user’s response is stored in the variable response:

1
2
3
4
5
6
7
8
9
10
11
Dim response
response = MsgBox("Something went wrong!", vbAbortRetryIgnore + vbCritical)

Select Case response
	Case vbAbort
		code to run when user chooses Abort
	Case vbRetry
		code to run when user chooses Retry
	Case Else
		code to run when user chooses Ignore
End Select


InputBox

Displays a dialog box with a textbox and OK/Cancel buttons
Value entered by the user is written to a variable

1
2
Dim userInput As Variant
userInput = InputBox("Please enter a value")

Can also display a custom title, and a default value

1
2
Dim userInput As Variant
userInput = InputBox("Please enter a value", "Input", "Hello")


Run a procedure when you open / close / save a workbook

Auto_Open是excel build in 的function(可以理解成unity的update那样的存在).
(不过是一个很老很老的functionality)
需要private key word(只在这个workbook打开时发生).
同样还有before_close.

在thisworkbook module里.

1
2
3
4
5
6
7
8
9
Private Sub Workbook_BeforeClose(Cancel As Boolean)

If WorkSheets("CorporateSales").Range("F5").Value = "" Then
  MsgBox("Please enter a commission rate before closing the workbook.")

Cancel = True
End If

End Sub
1
2
3
4
Private Sub Workbook_BeforeSave(ByVal SaveUI As Boolean, Cancel As Boolean)
  Answer = MsgBox("Do you really want to save the workbook?", vbYesNo)
  If Answer = vbNo Then Cancel = True
End Sub


Use worksheet function in macro

application.WorkSheetFunction.XXX



On error

有三种on error模式.

  • On error goto 0 (which is the default)
  • On error goto handler
  • On error resume next
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub CheckError()

Dim lngNumber As Long
Dim lngResult As Long

On Error GoTo Handler:

lngNumber = InputBox("Enter a number.")
lngResult = lngNumber + 3

MsgBox(lngResult)
Exit Sub

Handler:
MsgBox "This is not a number!"

End Sub

这段代码要求用户输入一个数字,如果不是数字,那就会识别错误.
如果出错,就跳到handler的this is not a number.

在其他模式,On error goto 0会跳出type错误的default error messenge
On error resume next continue on the best it can as if the error did not occur.
And in this case, it will return a 3.





UserForm

让user input的弹窗.可以在不碰worksheet的情况下向user要input.

在VBA editor里面 > insert > userform

caption: 弹窗的左上角名称或文本本身.
可以在view > properties window中check userform的属性.基本可以在这里更改不同element的属性.
RowSource: 需要在worksheet里面先把data做成一个table,再ref 用table name.

点击toolbox,就可以编辑userform.

1
theUserFormName.show

就可以run了.



Check

Check workbook open.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Private Function CheckIfOpen(strFileName As String) As Boolean

Dim w As Workbook
On Error Resume Next

Set w = Workbooks(strFileName)
If Err Then CheckIfOpen = False Else CheckIfOpen = True

On Error GoTo 0

End Function

Sub CheckIfWkbkOpen()

'Add name of file you want to check
bIsOpen = CheckIfOpen("Target.xlsx")

MsgBox (bIsOpen)

End Sub


用代码打开一个workbook.

1
2
3
4
Sub OpenAWorkbook()

Workbooks.Open Filename:="C:\Users\theAddress.xlsx"
End Sub

Close the workbook.

1
2
3
4
5
6
7
Sub CloseAWorkbook()
'保存
ActiveWorkbook.Save
'Close it
ActiveWindow.Close

End Sub

也可以手动turn on & off alert.

1
2
3
Application.DisplayAlerts = False
'记得关闭后要再turn on
Application.DisplayAlerts = True


Check worksheet exist.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Private Function WkshtExists(strSheetName) As Boolean

On Error Resume Next

WkshtExists = Sheets(strSheetName).Name <> ""
'if worksheet exist, then the test here not equal to zero is true

On Error GoTo 0

End Function

Sub CheckForSheet()

Dim bIsThere As Boolean

bIsThere = WkshtExists("March")
MsgBox (bIsThere)

End Sub