逗号分隔字符串,输出分割后的值。
1
2
3
4
5
6
7
8
9Sub 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 SubExcel列字母和数字的相互转换,用于Cells函数取值。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15Sub 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打开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
15Sub 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