空白目錄 · VBA

# vba網易課堂第14、15、18回要認真看 # 1 VBA 代碼分為： - 事件編程代碼——比如彈窗等； - 窗體代碼 - 標準代碼——模塊（比如單擊運行等） - 類代碼 # 2 ?強制聲明變量在“模塊”的最上面寫 `option explicit` ![](https://box.kancloud.cn/ee6ff1e2bc28af7e3b1ccd3c119c07fd_312x150.png) # 3 for循環 ``` for i=1 to 20 step 1 cells(i,5) = cells(i,1) + cells(i,3) next i ``` next i 可以簡寫成 next，但如果有很多循環的時候，一般不好觀察，所以還是用 i 表示 step1 也可以省略，因為默認步距是1 ### 倒序刪除 ``` for i = g to 2 step -1 if cells(i,2) = "" then row(i).delete end if next i ``` # if 語句 ``` option explicit // 強制執行變量的定義 sub 檢查() dim i = cells(2,2) if(i > 30) then cells(3,3) = "good" else cells(3,3) = "fail" end if end sub ``` ![](https://box.kancloud.cn/115e9e2679975d51ac109407efe0216e_218x160.png) ### elseif 語句相當于 swich ``` if (sore >=85) then cells(3,3) = "A" elseif (score > 75) then cells(3,3) = "B" elseif (score > 60) then cells(3,3) = "c" end if ``` # 4 邏輯運算 and if or ` if cells(1,1) = "rmb" or cells(1,1) = "人民幣" or cells(1,1) = CNY then ` # 5 程序調試設置斷點 ——按程序左邊的紅色小點，然后`F8`執行單步操作，接著會看到程序往下運行了一行，到了黃色區，我們這時候想知道變量 i 的值，只要鼠標放到 i 上面點一下就可以知道。 ![](https://box.kancloud.cn/ddefcbde5dcd79a84df29b4d9e8a543c_330x75.png) 如果想知道此時 cells 的值，也只要點擊 cells 即可。 ![](https://box.kancloud.cn/e5bb946aaaf3ddd84bae3a73ccf54eab_333x77.png) 黃色的表示有問題的代碼或者是當前運行的代碼 # 6 錄制宏例：希望在成績中找到小于60的，標記成紅色如果不知道如何編寫，則可以直接錄制一段宏，然后復制其中的代碼即可。 ``` option explicit sub 檢查是否及格() dim i for i=2 to 8 if cells(i,2) < 60 cells(i,2).select with selection.font .color = -16776961 .TintAndShade = 0 end with end if next i end sub ``` ![](https://box.kancloud.cn/77fd984ded54de8011aee17e1a0feb65_178x171.png) ![](https://box.kancloud.cn/fe8575f01d521198e110293d232cead2_343x166.png) // 如果是 Rows("6:6")，表示第六行，那么當在 if i 循環中，可以寫成 `Rows( i &":" &i )` 不能直接寫成"i:i"，因為會變成一個字符串"i:i"，不是循環的 i 變量。 ![](https://box.kancloud.cn/4783344b2b6668be5c344773cd4e3de0_211x159.png) # 7 類對象、屬性、方法 VBA也是面向對象的編程，所以有類。幾個常用的： - Application 代表EXCEL這個程序本身，如果要執行關閉，打開等操作，就在這里； - Workbook 代表已打開的EXCEL文件。比如：“項目管理臺賬.xlsx”就是一個workbook類； - worksheet 代表工作表； - range 代表單元格范圍。從屬關系： 1、每個application 默認有個workbooks對象，指代很多個WorKBOOK 2、每個workbook 默認有個worksheet對象，指代很多個worksheet 3、每個worksheet 默認有個cells對象或range兌現，指代很多個cell。 # 8 子程序 vba 視頻教程第15回 ``` sub All() call 客戶信息 // 直接調用 call 用戶密碼 end sub ``` # 9 函數 vba 視頻教程第15回關鍵字function。主程序可以調用函數。 **在表格中可以調用函數作為公式，函數中也可以調用公式。** # 10 值傳遞 ![](https://box.kancloud.cn/d7788bde25cf4fc8ba0e3657c8d03ef0_480x757.jpg) # 11 字符串檢查 len() trim() replace() left() right() mid() 從中間開始查找分解字符串 instr （s, "翔安") 意思是，查找s字符串里面，“翔安”這兩個字從第幾個字符開始。 # 12 統計行數 ``` Dim j j = ActiveSheet.UsedRange.Rows.Count ``` # 13 range 格式 ``` dim w as workbook set w = workbooks.add dim r as range set r = range("A2:B5") r = 5 r.clearcontents //清除單元格格式 ``` - range.clearcontents //清除內容，保留格式 - range.clearfomats //清除所有格式，保留內容 - range.clear //清除所有格式以及內容 - r.font.size - range.interior.color //表示單元格內部的背景色 - range.merge //合并單元格 - range.unmerge //拆分所有單元格 ``` with r.font .color = rgb(255,0,0) .bold = true .size = 12 end with ``` # 14 Application vba視頻第21回 Application.ActiveWorkBook //當前處于激活狀態的工作簿book Application.ActiveSheet //當前處于激活狀態的工作表sheet 可以將公式調用到代碼中 `application.worksheetfunction` `m = application.worksheetfunction.max(range("A2:B5"))` > **范圍要寫max(range("A2:B7"))，不能只寫MAX("A2:B7")** - Application.displayAlerts = false 禁止彈窗 - Application.displayAlerts = true 允許彈窗但完了以后要記得再設置成true，以免所有的提示都沒彈窗 # 表格結構不一樣時，比如第一個表格第一行為“張三”，第二個表格第二行為“張三”，如何解決 > **不用參數的傳遞** **vba視頻第18回** # VBA使用vlookup查找可直接錄制一個宏，然后復制代碼，或者： ``` dim i,j i=2 ??????j = activesheets.usedrange.rows.count for i=2 to j cells(i,5) ="=iferror(vlookup(cells(i,1),[填寫_回款登記.xlsx]回款登記!c1:c3,3,false),0)" ``` # 判斷有幾行 j = activesheets.usedrange.rows.count 或者是指定 dim w as worksheet, set w = workbook("過程資料.xlsx").worksheet(1) 然后可以直接調用w.userange.rows.count 比如： ``` g = w預測.UsedRange.Rows.Count '得出來的值就是這個數字 w預測.Range("E2:F" & g).ClearContents ``` j =Range("A1048576").end(xlup).row xlToLeft ：向左移動，相當于在源區域按Ctrl+左[方向鍵] 　　xlToRight：向右移動，相當于在源區域按Ctrl+右方向鍵。　　xlUp：向上移動，相當于在源區域按Ctrl+上方向鍵。　　xlDown：向下移動，相當于在源區域按Ctrl+下方向鍵。 # 13 數組 ### excel中要表示數組：在某一格輸入`=`，然后點擊范圍， ![](https://box.kancloud.cn/f0afb2fa15dcf86109a47cb197710e17_259x165.png) 然后直接按F9， ![](https://box.kancloud.cn/309d74333e7cb725d54ae8c7d56ccf81_183x55.png) 此時表示的就是數組的形式。 ### v轉置選中相關4個單元格，然后輸入`=transpose`， ![](https://box.kancloud.cn/cf70f8731a78a2b54a49c718d0be8813_473x194.png) 同時按ctrl+shift+enter，即可 ![](https://box.kancloud.cn/5bf5c0251e93848a95e4bfb5a45e1215_501x216.png) ### VBA里面都是一維橫向數組，無縱向數組，會默認變為二維數組，但可以用transpose轉置，如下 `range("A60:E60") = application.worksheetfunction.transpose[{1;2;3;4;5}]` ### **常用直接引用區域產生數組** 引用選中區域，再賦予一個變量。 ### EVALUATE 輸入數組 `range("A2:B5") = evaluate("{1,2,3,4,5}")` 括號里面要再雙引號。 ![](https://box.kancloud.cn/e65ed7afbf6808844c835cdfa6dcd355_409x58.png) 如果是文本，則每個文本旁邊都要輸入兩個雙引號 `range("a1:a5") = evaluate("{ ""姓名"", ""性別"" }")` ![](https://box.kancloud.cn/163ac782603bc60616abaebae4b05d72_347x29.png) 如果是列方向，就用分號 `range("a1:a5") = evaluate("{ ""姓名""; ""性別"" }")` ![](https://box.kancloud.cn/1865aa57d5fe3501aabba8823a5964c7_116x114.png) ### array函數只能產生橫向數組，不能縱向數組，所以不能用分號`;` `range("") = array(1,2,3,4)` `range("") = array("a","f","w")` # 14 VBA 常量 https://zhidao.baidu.com/question/195111214.html LookIn:=xlValues,按值查找 LookAt:=xlWhole，全部匹配，xlPart部分匹配 SearchOrder:=xlByRows，按行查找 SearchDirection:=xlNext，查找方向，向下查找 # 15Excel強制類型轉換有一些頑固的數值類型無法通過設置單元格格式轉成文本，這時候可以用強制類型轉換選中某一列 - 數據 - 分組，直接點擊下一步，到最后一步，選擇格式，即可完成強制轉換。 # 16 數組內的元素連接成字符串使用join 關鍵字即可 ``` dim arr() ... cells(i,j) = join(arr, "@") ``` # 17 邏輯變量 exit、goto、not true **vba 高級第6回** 想寫一個表達式：逐行查找，一旦找到第一個符合邏輯的變量以后，則標記為黃色，并且退出循環，不再繼續循環。跳出循環： ``` sub findFirstData() dim i as long, found as boolean i = 3: found = false do while not found and trim(cells(i,2)) <>"" if inStr(cells(i,6) , "施工圖") > 0 then ' 意思是，這個單元格中的字符串包括一個或多個“施工圖”字樣 range(cells(i,2),cells(i,6)).interior.color = vbyellow found = true '可以及時退出循環，不用執行后續啰嗦代碼 end if i = i+1 loop end sub ``` ## exit do 或者 exit for ![](https://box.kancloud.cn/672deca30e945215f24176ecf940b3c8_636x311.png) 可以直接用EXIT DO退出do 循環，用 exit for 退出 for 循環，不用再寫邏輯判斷 > 但只能用于do while 循環，而不能用 while 循環 ``` sub find() dim i as long do while trim(cells(i,2)) <>"" if instr(cells(i,2),"施工圖")>0 then range(cells(i,2), cells(i,6)).interior.color = vbyellow exit do '只要寫這句話即可退出 do 循環，一找到就退出，不用再一直循環 end if i = i + 1 loop end sub ``` ![](https://box.kancloud.cn/1e7202edb5d41aa0f7882c8ccca3b394_579x360.png) ## exit sub 退出子過程 ``` sub exitText() msgbox "找到了" exit sub msgbox "沒找到" end sub ``` exit 只能跳出自己所在的循環，而不能所有都跳出，除非多個 exit 組合使用。或者用 goto # 18 goto / on error resume next goto 不經常使用，但可以直接跳到某個標簽中。標簽用`啦啦啦:` 文字加冒號來表示 ``` sub goto() do while if xxxxx= false goto 啦啦啦: end if loop 啦啦啦: msgbox "跳轉成功" end sub ``` ![](https://box.kancloud.cn/9490a3d700c454f8cafe43b4de2bdfd6_234x294.png) 用于錯誤處理 ``` sub goto() dim i i = cells(i,2) on error goto myError: i = 2 / i '由于這個地方有可能 i 會變成0，使得結果出錯，所以我們可以直接 goto，這樣就避免別人在使用我們的程序的時候無法調試 myError: msgbox "if error, please call me!" end sub ``` 但此時程序還是會往下運行。如何只在需要的時候運行：此時可以用 exit sub ![](https://box.kancloud.cn/690a47175d1170bde9b743ed71d86f5d_582x329.png) ``` sub goto() dim i i = cells(i,2) on error goto myError: i = 2 / i '由于這個地方有可能 i 會變成0，使得結果出錯，所以我們可以直接 goto，這樣就避免別人在使用我們的程序的時候無法調試 exit sub myError: msgbox "if error, please call me!" end sub ``` ![](https://box.kancloud.cn/29492ce12adedb3dea2af57844f17c27_584x357.png) ## on error resume next 在vba 中引用 excel 函數的時候經常會遇到#N/A的情況，此時就可以用這句話執行。 ![](https://box.kancloud.cn/1678c533673b3ea09934c91078d22c1e_304x473.png) ![](https://box.kancloud.cn/bfb7b7f72f38ad89ff115aad403b051c_343x423.png) # 19 判斷類型以及類型轉換 vba 高級第7回 isdate() isNumeric() '不是 numberic typename() 可以告訴你輸入的是什么類型，返回一個字符串 ![](https://box.kancloud.cn/ca55ce8e8037deaf2b626543c37fc8d4_593x472.png) # 20 ASCII 編碼 ``` sub ascii() dim i a = asc("A") '返回65 s = chr(65) '返回 A end sub ``` 可以用ASCII 編碼表示回車和換行符 chr(13) 意思是回車 chr (10)意思是換行 ![](https://box.kancloud.cn/be605be541651d47e247d55898912c3c_703x366.png) # 21 數組下標： ``` dim arr(), for i = Lbound() to ubound(arr) ``` split拆分數組后，返回的是一個數組，于是另外寫函數，讓 a(i)顯示出來。 ``` sub split() dim a() as string, i as long, k as long a = split(cells(i,2),",") k = 3 for i = lbound(a) to ubound(a) cells(k,4) = a(i) k = k+1 next i end sub ``` 或者用for each x in a ``` dim i , x '如果要用 x 來指代數組中的某個值，則 x 必須為變體類型，不能指定類型。 i = 3 for each x in a cells(i,4) = x i=i+1 '這樣寫了就不用在寫 for i = 3 to m ``` ## 動態數組 redim() # 21 vba 操作文本 vba 高級第11回 ## 讀取文件：四步： 1. 打開文本。但不會顯示在屏幕上，而是后臺打開。指定到底是 input 讀取（從文本文件中讀取并保存到 vba 中）還是 output 輸出（把 vba 中的內容輸入到 txt 文件中），并指定唯一的數字編號#1，以及：`open "d:\demo\客戶信息.txt" for input as #1` 2. 讀取內容：` line input # 1, s` 從#1 號文本文件中，讀取文本中的一行，并保存成字符串，賦值給 s 變量 3. 判斷是否到達末尾用`EOF(1)`表示（end of file）。括號中的1為文件編號 #1 4. 關閉文件 `close(1)` > 一定要記得 close ``` sub txt() dim s as tring, i as long open "d:\demo\hello.txt" for input as #1 i = 1 do while EOF(1) = false '當代號為1的文件，還沒有到達末尾的時候，執行 do，或者直接寫成not EOF(1) line input #1, as s ' line input 函數作為從上到下讀取 txt 文件 cells(i,1) = s '賦值給 s 變量 i = i+1 loop close #1 end sub ``` ## 寫入文件三步：打開文件寫入一行關閉文件 > for output 如果沒有這個文件，則新建一個。如果已經有了，則覆蓋原有文件內容。 > 如果不想覆蓋而想新建：改成open for append追加 ``` sub txt() dim i as integer open "d:\demo\hello.txt" for output as #2 print #1, trim(cells(3,3)); print #1, trim(cells(3,4)) print #1, trim(cells(3,5)); '有分號，表示這一行跟下一行是連續的，如果沒分號，則是換行的。 print #1, trim(cells(3,6)) close #1 end sub ``` # 22 事件函數原來寫在模塊中的是要點擊運行才會運行，如果要自動運行的話，需要寫在“事件”里面。也就是 sheet1或者 sheet2里面。但只會對當前的 sheet 起作用，如果要對所有 sheet 起作用，則可以在模塊中編輯，然后在 sheet 中調用。 > 注意函數參數的傳遞。 ![](https://box.kancloud.cn/19867e08396a25f759f186bd20752595_768x368.png) ![](https://box.kancloud.cn/0b9583402b83ec0f5f4b413890ee2045_812x408.png) # 23 窗體事件 `form.show` 顯示窗體 `form.hide` 隱藏窗體 `unload form` 完全卸載，之前填寫的數據都清空 # 24 全局變量如何表達被選中單元格（被選中的單元格）也就是說，如果選中了某個記錄，想修改后保存，如何處理？這里涉及到不同事件的跳轉。也就是說，當選中某個記錄以后，其實系統內這個事件就結束了，需要轉到另一個事件中處理。但我們仍需要把現在這個事件中選中的行號傳入下一個事件才行。這時候有兩種方法：方法 1，用application.selectcell來記錄這個被選中的單元格。 ## 方法 1：直接寫成`application.activecell`即可 ![](https://box.kancloud.cn/66be332d4214022a1bc810bede0ff3ad_520x352.png) ``` dim r as range set r = application.activecell cells(r.row,2) = txtName.text cells(r.row,3) = txtAge.text ``` ![](https://box.kancloud.cn/cdbe0ddf96db8b0f83d2e7e49ef665a1_296x224.png) ## 方法 2 全局變量在標準模塊中定義一個全局變量 `public rowNumber as range`，在雙擊事件中，將該事件的行號直接保存入全局變量rowNumber，接著在第二個事件中，就可以直接使用 iRow 了。但要記住，要寫成`模塊 1.rowNumber`才可以。 - 先在模塊 1 中定義全局變量。 ![](https://box.kancloud.cn/8792e329f1d8e3e69d935e809a838e46_556x264.png) - 再從 sheet1 雙擊事件中，將該行號先暫存到rowNumber 中。 ![](https://box.kancloud.cn/a75540357c38bdbbb2e0030d62a388a9_925x201.png) - 最后當單機“修改”按鈕時，將 `i = 模塊1.rowNumber`即可 ![](https://box.kancloud.cn/a81fd1c7532afed30070011c81339d3c_835x477.png) # 25 字典！！！字典是個外部對象。 ## 1. 創建字典 ``` dim i as integer, k as string, myDic as object //字典是個外部對象，所以要用 object set myDic = createobject("scripting.dictionary") ``` ![](https://box.kancloud.cn/ad6ccf20582eb7efa6b3e152eece621a_1190x554.png) > 一個字典'可以對應一個item對象，這個ITEM可以是字符串，也可以是數組。 ## 2. 幾種常用方法 `dim k as integer, myDic as dictionary // k表示字典中的關鍵字 key` `myDic.add k, cells(i,4).value` //add為向字典內添加內容的方法 `myDic.key(k)` `myDic.item(k)` ``` myDic.key("hello") = "hallo" //myDic.key(k)關鍵字用來查找 ``` `myDic.key("喬峰") = '蕭峰'` //因為喬峰姓名是關鍵字，所以要用 myDic.key來處理 `myDic.remove("喬峰")` `myDic.removeAll` `myDic.count(k) //統計字典里面有多少個元素` `myDic.exists(k) //判斷字典中是否包含某個字` ![](https://box.kancloud.cn/46a3e589a107428d6883c9e8bd2c07d1_382x229.png) ``` Sub 不重復統計() //1.創建字典外部對象 Dim i As Integer, k As String, myDic As Object, j As Integer, a(), b() //k表示字典當中的key關鍵字 Set myDic = CreateObject("scripting.dictionary") //2.將原表格中符合條件的信息存放到字典myDic中 For i = 2 To 5 k = Cells(i, 1) //用來記錄A列中每找到的項目編號 If Not myDic.Exists(k) Then myDic.add k, Cells(i, 2) //add后面跟一個keyword關鍵字，以及我們需要計入的item，item表示對象，可以是字符串，也可以是數字，也可以是數組 Else myDic.Item(k) = myDic.Item(k) + Cells(i, 2).Value // 這里必須是 value End If Next i //3.把字典里面的東西寫入excel中 j = myDic.Count //count用來統計字典里面到底有幾組數據 If j > 0 Then Dim m As Integer m = j + 1 a = myDic.keys() //注意是keys復數 b = myDic.Items() Range("F2:G" & m).ClearContents Range("F2:F" & m) = Application.Transpose(a) //一定要記得轉置，因為數組是一維橫向的，而我們要填的 excel 表格是縱向的 Range("G2:G" & m) = Application.Transpose(b) End If End Sub ``` 也可以簡寫寫成myDic(k)，默認就是myDic.item(k)，這樣的話，如果字典中沒存在這個關鍵字，就可以幫我們創建一個，從而也不用再寫if else 了。 ![](https://box.kancloud.cn/ec7ad39e616aa5e1edba82f4004839f5_437x101.png) ``` //整段替換成 myDic(k) = myDic(k) + cells(i,2).value ``` ## 3. key可以是字符串，也可以是一個range 所以如果上一句代碼寫成了`myDic(cells(i,2))`則會出錯，因為不同的 range 被當成了不同的對象，所以就無法去重，非要寫的話只能寫成 `myDic(cells(i,2).value)` ![](https://box.kancloud.cn/64cd68f9f23e5e169d5fbe63d18fe8f7_801x46.png) # 25 打開 workbook 一般用法是： ``` dim w as worksheet set w = workbook.open("d:\……") ``` **一旦 open 了以后一定要記得寫 `w.close`** 另一種辦法是：用 `getobject` ``` dim i as long, doc as object for i = 1 to 8 //依次打開文件夾下面的所有文件，這些文件命名為1.docx、2.docx……8.docx set doc = GetObjecy("d:\演示\" & i & ".docx") //將文檔中的文字寫入 excel 中，第 i 行。 cells(i,1) = doc.range.text //關閉文檔 doc.close ``` ``` Sub create() Dim i As Long, doc As Object, m As Worksheet For i = 1 To 3 //依次打開文件夾下面的所有文件，這些文件命名為1.docx、2.docx……3.docx Set doc = GetObject("\\Mac\Home\Desktop\" & i & ".docx") //將文檔中的文字寫入 excel 中，第 i 行。 Cells(i, 1) = doc.Range.Text //記得關閉文檔 doc.Close Next i End Sub ``` # 26 VBA 做網絡爬蟲 ``` Sub APIpachong() Dim s As String, xh As Object Set xh = CreateObject("Microsoft.XMLHTTP") xh.Open "GET", "http://www.boc.cn/sourcedb/whpj", False xh.send Cells(1, 1) = xh.responsetext End Sub ``` 則會在相關 cells 中顯示網頁源代碼。再通過正則表達式的識別，就可以抓取相關數據信息了。 ![](https://box.kancloud.cn/f69d471fa91bd8bf82d1194d61c0d1fb_923x556.png) ![](https://box.kancloud.cn/f36ac80f8a75eca76f16b1184f941c36_710x401.png)