Excel VBA 常用操作 2023

  1. 逗号分隔字符串,输出分割后的值。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    Sub splitStr()
    Dim msg As String

    splitedArr = Split(Cells(1, 1).Value, ",")
    For i = LBound(splitedArr) To UBound(splitedArr)
    msg = msg & splitedArr(i) & vbNewLine
    Next i
    MsgBox msg
    End Sub
  2. Excel列字母和数字的相互转换,用于Cells函数取值。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    Sub convertColNumAndLetter()
    Dim col As String
    Dim colInNum As Long
    Dim colInLetter As String
    Dim msg As String

    col = "AA"

    colInNum = Range(col & 1).Column
    colInLetter = Split(Cells(1, colInNum).Address, "$")(1)

    msg = col & " 's number is " & colInNum & vbNewLine & colInNum & " 's letter is " & colInLetter

    MsgBox msg
    End Sub
  3. 打开Excel文件的指定sheet。
    On Error Resume Next 可以规避【expecting object to be local】错误

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    Sub openExcelFile()
    Dim sheetName As String

    sheetName = "result"

    On Error Resume Next
    With Workbooks.Open("D:\tmp\tmp.xlsx")
    With .Worksheets(sheetName)
    .Activate
    Cells(1, 1).Value = 1
    End With
    .Save
    .Close
    End With
    End Sub