目錄

1.各種數(shù)據(jù)類型

2.VBA定義公共數(shù)組（全局都可以使用）

3.讀寫文件

SJIS 寫入

UTF8 寫入

讀取 SJIS

讀取 UTF-8

4.異常處理

5.忽略錯誤

6.其他常用1

7.其他常用2：讀?。ǘ鄠€）Excel文件

8. ★★★ 各種常用操作總結(jié)　★★★

9.事件相關(guān)處理 （保存，離開單元格等操作時，觸發(fā)處理）

10.定義（調(diào)用）有返回值的函數(shù)

11.使用VBA操作IE瀏覽器

12.Variant   變量類型

13.調(diào)用bat （使用cmd，直接運行程序）

14.調(diào)用Jar,并獲取返回值

核心代碼

具體內(nèi)容

15.循環(huán)(For、While)

For

While

16.Excel-VBA：列號與字母（列名）的相互轉(zhuǎn)換 

17.使用VBA給單元格設(shè)置公式 :（ FormulaR1C1 )

18.XXX

---

1.各種數(shù)據(jù)類型

---

'* * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
'*
'*  自定義學(xué)生對象
'*  做成時間:2020/01/20
'*  做成者: sun
'*  跟新日:
'*  更新者:
'*
'* * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Type STUDENT_INFO
    '學(xué)號
    stuNo As String
    stuName As String
    stuAge As String
    stuSexCode As String
End Type
'* * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
'*
'*  定義各種數(shù)據(jù)類型，處理學(xué)生對象情報
'*  做成時間:2020/01/20
'*  做成者: sun
'*  跟新日:
'*  更新者:
'*
'* * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Sub getStudentInfo()
    ' 從Excel的Sheet頁中取得情報 例子
    ' Dim sheet As Worksheet
    ' Set sheet = Worksheets("testSheetName")
    ' cellValue = sheet.Cells(2, 3) '2C 單元格 "1001,張三,30,1、1002,李四,31,1、1003,XiaoHong,29,2"
    ' MsgBox cellValue
    ' 全部學(xué)生信息 以字符串形式保存
    Dim allStudentInfo As String
    allStudentInfo = "1001,張三,30,1、1002,李四,31,1、1003,XiaoHong,29,2"
    ' 全部學(xué)生信息 以數(shù)組形式保存【數(shù)組使用】
    Dim studentInfoList() As String
    studentInfoList = Split(allStudentInfo, "、")
    ' 全部學(xué)生姓名信息 以key-學(xué)號  value-姓名 的Map形式保存
    Dim studentDataList As Object '【數(shù)據(jù)字典使用-定義】
    Set studentDataList = CreateObject("Scripting.Dictionary")
    ' UBound 返回數(shù)組的上界 ，★★★不是數(shù)組的大小★★★比如數(shù)組大小時4，那么上屆是3
    For i = 0 To UBound(studentInfoList)
        ' 循環(huán)設(shè)定每個學(xué)生的信息
        Dim stuObject As STUDENT_INFO '【自定義數(shù)據(jù)類型使用】
        stuObject = setStudentInfo(studentInfoList(i))
        ' 以學(xué)號作為Key，存儲上面設(shè)定好的學(xué)生的信息
        ' studentDataList.Add stuObject.stuNo, stuObject  '不好用，無法保存自定義對象
        studentDataList.Add stuObject.stuNo, stuObject.stuName '【數(shù)據(jù)字典使用 - 設(shè)定值】
    Next
    ' 顯示學(xué)號是「1002」小明同學(xué)的信息
    If studentDataList.exists("1002") Then '【數(shù)據(jù)字典使用 - 取值】
        MsgBox studentDataList.Item("1002") '【數(shù)據(jù)字典使用 - 取值】
    End If
End Sub
'* * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
'*
'*  設(shè)定單個學(xué)生對象情報
'*  做成時間:2020/01/20
'*  做成者: sun
'*  跟新日:
'*  更新者:
'*
'* * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Public Function setStudentInfo(studentInfo As String) As STUDENT_INFO
    ' 單個學(xué)生的信息
    Dim infos() As String
    infos = Split(studentInfo, ",")
    ' 使用上面定義的學(xué)生對象
    Dim stuObject As STUDENT_INFO
    stuObject.stuNo = infos(0)
    stuObject.stuName = infos(1)
    stuObject.stuAge = infos(2)
    stuObject.stuSexCode = infos(3)
    ' 設(shè)定返回值
    setStudentInfo = stuObject
End Function

----

2.VBA定義公共數(shù)組（全局都可以使用）

public sqlList
public Function sql_init()
    ActiveWorkbook.sheets("sql").Activate
    ActiveWorkbook.sheets("sql").Select
    var1 = ActiveSheet.Range("C2").value
    var2 = ActiveSheet.Range("C3").value
    var3 = ActiveSheet.Range("C4").value
    sqlList= Array(var1, var2, var3)
End Function

擴展

「プロシージャの外では無効です」錯誤發(fā)生原因
只寫了End，而沒有寫 End Function

----

3.讀寫文件

ファイルの操作(Shift-JIS/UTF8対応)[ExcelのVBA]

SJIS 寫入

Private Sub CommandButton2_Click()
  Dim fileNo As Integer  ' ファイル番號
  ' ファイルを開く為のファイル番號を取得
  fileNo = FreeFile
  ' 指定されたファイルを開く(ない場合は作成する)
  Open "Sjisの書き込みテスト.txt" For Output As #fileNo
  ' 一行毎に文字列を書き込む
  Print #fileNo, "エクセル講座"
  Print #fileNo, "http://www./excel/excel.html"
  Close #fileNo
End Sub

UTF8 寫入

Private Sub CommandButton4_Click()
  Dim Stream As Object
  ' VB標(biāo)準(zhǔn)のADODB.Streamオブジェクトを作成する
  Set Stream = CreateObject("ADODB.Stream")
  ' ストリームの文字コードをUTF8に設(shè)定する
  Stream.Charset = "UTF-8"
  ' ファイルのタイプ(1:バイナリ 2:テキスト)
  Stream.Type = 2
  ' ストリームを開く
  Stream.Open
  ' ストリームの保存形式をテキスト形式にする
  Stream.WriteText "エクセル講座" & vbCrLf & "http://www./excel/excel.html"
  ' ストリームに名前を付けて保存する(1は新規(guī)作成 2は上書き保存)
  Stream.SaveToFile ("utf8の書き込みテスト.txt"), 2
  ' ストリームを閉じる
  Stream.Close
  Set Stream = Nothing
End Sub

--

讀取 SJIS

Option Explicit
Private Sub CommandButton1_Click()
  Dim fileNo As Integer  ' ファイル番號
  Dim buffer As String   ' 一時的に文字列を格納
  ' ファイルを開く為のファイル番號を取得
  fileNo = FreeFile
  ' 指定されたファイルを開く
  Open "C:\excel_vba_22_sjis.txt" For Input As #fileNo
  ' ファイルがEOF(ファイルの終端)になるまでループをする
  Do Until EOF(fileNo)
    ' ファイルから一行づつbufferに読み込む
    Line Input #fileNo, buffer
    ' 読み込んだ一行をイミディエイトに出力
    Debug.Print buffer
  Loop
  Close #fileNo
End Sub

讀取 UTF-8

Private Sub CommandButton3_Click()
  Dim buffer As String ' 一時的に文字列を格納
  Dim Stream As Object
  ' VB標(biāo)準(zhǔn)のADODB.Streamオブジェクトを作成する
  Set Stream = CreateObject("ADODB.Stream")
  ' ストリームの文字コードをUTF8に設(shè)定する
  Stream.Charset = "UTF-8"
  ' ファイルのタイプ(1:バイナリ 2:テキスト)
  Stream.Type = 2
  ' ストリームを開く
  Stream.Open
  ' ストリームにファイルを読み込む
  Stream.LoadFromFile ("C:\excel_vba_22_utf8.txt")
  ' ファイルの中身をbufferへ代入
  buffer = Stream.ReadText
  ' ストリームを閉じる
  Stream.Close
  ' イミディエイトへ出力  
  Debug.Print buffer
  Set Stream = Nothing
End Sub

--

4.異常處理

Function
    On Error GoTo err_handle
    。。。
   Exit Function
   err_handle:
       MsgBox "Error!"
End Function

---

5.忽略錯誤

Function
    On Error resume next
    。。。
End Function

resume [r??zju?m]  n. 摘要；簡歷   v. 重新開始，恢復(fù)；取回，重新占用

---

6.其他常用1

根據(jù)一覽，自動生成Sheet頁_sun0322-CSDN博客

7.其他常用2：讀取（多個）Excel文件

Excel VBA 循環(huán)讀取一個目錄下面多個文件的內(nèi)容，放到另外一個文件中_sun0322-CSDN博客_vba循環(huán)讀取文件夾下的文件

8. ★★★ 各種常用操作總結(jié)　★★★

' 有時即使光標(biāo)到了A1，因為滾動條的原因，顯示效果并不是在最上面，此時要使用下面語句
ActiveWindow.SmallScroll Down:=-2000
' 對公式進行計算
ActiveSheet.Calculate
’多個Excel直接發(fā)生切換時，使用下面的語句（參數(shù)是，要切換到的Excel的名字，不含路徑）
'  （對于不是通過代碼打開的 Excel文件）
Windows(operateFileName).Activate
' 對于代碼打開的Excel，直接使用下面語句即可切換
Set workbook2 = Workbooks.Open("pathAndName", UpdateLinks:=0)
workbook2.Activate
Sheets("xxx").Select
'關(guān)閉打開Excel （不保存）
workbook2.Close savechanges:=False
'【sheet】復(fù)制 
workbook2.Activate
Sheets("copySheet").Select
Sheets("copySheet").Copy After:workbook1.Sheets(10)
'【sheet】重命名
workbook1.Activate
Sheets("copySheet (2)").Select
Sheets("copySheet (2)").Name = copySheet_比較用
'【sheet】刪除
Sheets("copySheet_比較用").select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
’【單元格】復(fù)制，粘貼
Range("A1:C20").Select
Selection.copy
Range("E1").Select
ActiveSheet.Paste
’【單元格】復(fù)制，粘貼 【公式 ? 值】
Rang("A1:C20").Select
Selection.copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
’【單元格】刪除指定行 (例：刪除11到20行)
Rows("11:20").Select
Selection.Delete Shift:=xlUp
' goto A1 ' 遍歷所有Sheet頁 ' 保存
Dim sh As Worksheet
For Each sh In ActiveWorkbook.sheets
    sh.Activate
    sh.Range("A1").Activate
Next
ActiveWorkbook.Sheets(1).Activate
ActiveWorkbook.Save

===

9.事件相關(guān)處理 （保存，離開單元格等操作時，觸發(fā)處理）

指定單元格里面的，內(nèi)容發(fā)生變化，觸發(fā)事件，調(diào)用VBA代碼。_sun0322的博客-CSDN博客_單元格變化觸發(fā)vba

--

10.定義（調(diào)用）有返回值的函數(shù)

---

' 定義有返回值的函數(shù)
public Function checkValue() As Integer
    If XXX then
        ' 設(shè)定函數(shù)的返回值  ’×?。簊et checkValue = 1
        checkValue = 1
        Exit Function
    End If
    checkValue = 0
End Function
' 調(diào)用函數(shù)
flg = checkValue
If flg <> 0 Then
   。。。
End If

---

11.使用VBA操作IE瀏覽器

VBS操作IE ---（【當(dāng)不使用IE時】可以使用Chrome插件，自定義JS插件操作瀏覽器）_sun0322的博客-CSDN博客_vbs 瀏覽器

12.Variant   變量類型

Private Sub CommandButton1_Click()
'Declare 【Variant】 variable
Dim areaA As Variant
'Set 【Variant variable】Value '可以使用F3中定義的變量
areaA = Range("D6:F9")
'Use the 【Variant】 variable
var1 = areaA(2, 2)
var2 = areaA(4, 3)
MsgBox "var1:" & var1 & Chr(10) & "var2:" & var2
End Sub

--- Variant  [?veri?nt] 變種; 變體; 變形;  

 ---

13.調(diào)用bat （使用cmd，直接運行程序）

VBA調(diào)用bat，doc 命令行 窗口關(guān)閉之后，VBA代碼 再繼續(xù)執(zhí)行_sun0322的博客-CSDN博客_vba執(zhí)行bat命令

---

14.調(diào)用Jar,并獲取返回值

核心代碼

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
。。。
cmdStr = "java -jar c:\xxx\xxx\xx\tool.jar param1 param2"
Set WshShell = CreateObject("WScript.Shell")
Set oExec = WshShell.Exec(cmdStr)
Dim exitCode
exitCode = oExec.exitCode
' Java ソース中に、正常終了の場合、main関數(shù)の最後で、System.exit(666);
' Java ソース中に、異常終了の場合、System.exit(1);
do While exitCode = 0
    ' 遅延確認したいと、返卻値ずっと[0]です。
    Sleep 3000
    exitCode = oExec.exitCode
Loop
Set oStdOut = oExec.StdOut

具體內(nèi)容

https://blog.csdn.net/sxzlc/article/details/124742355

===

15.循環(huán)(For、While)

For

For i =12 To 150
    ' 第一次是，【5行，12列】中的數(shù)據(jù)
    cellValue = Cells(5, i).Value
    ' 空值的時候，退出
    IF cellValue = Empty Then
        Exit For
    End IF
Next

While

i = 2
cellValue = Cells(5, i).Value
Do While cellValue <> Empty
    i = i + 1
    cellValue = Cells(5, i).Value
Loop

===

16.Excel-VBA：列號與字母（列名）的相互轉(zhuǎn)換 

核心代碼

addrA1 = Replace(Cells(i, j).Address(0, 0), "1", "")

其它代碼

'列號轉(zhuǎn)字母（列名）
Function Num2Name(ByVal ColumnNum As Long) As String
    On Error Resume Next
    Num2Name = "" '超出范圍返回空，如調(diào)用Num2Name(100000)
    Num2Name = Replace(Cells(1, ColumnNum).Address(0, 0), "1", "")
    'Cell.Address用來返回單元格的地址，參數(shù)(0,0)返回相對地址A1，參數(shù)(1,1)返回絕對地址$A$1
End Function
'字母（列名）轉(zhuǎn)列號
Function Name2Num(ByVal ColumnName As String) As Long
    On Error Resume Next
    Name2Num = -1 '超出范圍返回0,如調(diào)用Name2Num("AAAA") ,EXCEL沒有那么多列
    Name2Num = Range("A1:" & ColumnName & "1").Cells.Count
End Function

17.使用VBA給單元格設(shè)置公式 :（ FormulaR1C1 )

?簡單例子

' C1單元格為"=$A$2+$E$3"
Range("C1").FormulaR1C1 = "=R2C1+R3C5"
' R2C1表示的是【第2行,第1列】的單元格即A2單元格

===

?具體例子

' one cell set formula
Range("B2").Select
Selection.NumberFormatLocal = "G/標(biāo)準(zhǔn)"
ActiveCell.FormulaR1C1 = XXXX  ' 設(shè)置公式的時候，錄制宏，從宏中取得公式即可
'set  all columns cell  formula
Range("B2").Select
Selection.AutoFill Destinaion:=Range("B2:Z2"), Type:xlFillDefault
'set  all rows cell  formula
Range("B2:Z2").Select
Selection.AutoFill Destinaion:=Range("B2:Z100"), Type:xlFillDefault
ActivateSheet.Calculate

===

獲取單元格的公式，下面代碼第三行

a = Range("B1").Value
b = Range("B1").Text
c = Range("B1").Formula

===

18.XXX

xxx