Excel VBA(宏):添加宏
生活随笔
收集整理的這篇文章主要介紹了
Excel VBA(宏):添加宏
小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.
寫在前面:
1、編寫宏,打開VBA,雙擊ThisWorkbook對(duì)當(dāng)前工作薄進(jìn)行編寫宏;雙擊Sheet1,對(duì)整個(gè)sheet編寫宏;
或者創(chuàng)建模塊,在模塊里,編寫、調(diào)試代碼。
打開VBA的方法見第一講,結(jié)合常用窗口進(jìn)行編寫、調(diào)試。
2、部分對(duì)象有提示,如Dim a As,敲擊空格后有提示。
3、所有宏要運(yùn)行,必須啟動(dòng)宏。(2007版啟動(dòng)宏,點(diǎn)擊表格左上角 “excel選項(xiàng)” “信任中心” “信任中心設(shè)置” “啟用宏”)
4、“無法在未啟用宏的工作簿中保存以下功能”提示,選是后,宏會(huì)整個(gè)丟失。
2007版,由于VBA或者宏代碼有一定的危害性,為了提高安全意識(shí),凡是以XLSX為后綴名的文件都不能含有宏代碼,需要將后綴保存為XLSM
5、vba中_表示代碼連接符,注意空格
================================================================
運(yùn)行excel中的VBA代碼時(shí),需要啟用宏。VBA主要操作包含觸發(fā)、指定單元格:1.觸發(fā):選擇不同的觸發(fā)模式。2.指定單元格:指定cells和Range。
==================================================================
VBA代碼調(diào)試技巧
######################################################################
第一講:常見錯(cuò)誤:
1、編譯錯(cuò)誤:不正確的代碼,編譯工具能提示錯(cuò)誤
2、運(yùn)行時(shí)錯(cuò)誤,執(zhí)行不可能完成的錯(cuò)誤
3、邏輯錯(cuò)誤打開VBA方法:
ALT+F11 == 菜單 工具 宏 == 控件 右鍵 查看代碼 == excel visual basic常用窗口:在“視圖”中依次打開:
立即窗口:可輸入指令運(yùn)行
本地窗口:中斷過程可查看中間變量值內(nèi)容
查看窗口:查看運(yùn)行輸出結(jié)果Debug對(duì)象:Debug.print ... '打印內(nèi)容Debug.assert ... '暫停點(diǎn),中斷程序
VBA程序三種狀態(tài):設(shè)計(jì)、運(yùn)行、中斷模式時(shí)。調(diào)試工具: 視圖 工具欄 調(diào)試中斷程序的方法(3種):設(shè)置斷點(diǎn):調(diào)試 切換斷點(diǎn) == F9使用Debug.Assert方法添加監(jiān)視條件:代碼窗口(或監(jiān)視窗口) 右鍵 添加監(jiān)視實(shí)例:'VBA編輯器會(huì)根據(jù)自己提供的內(nèi)容及已創(chuàng)建變量等自動(dòng)調(diào)整大小寫,建議一次寫對(duì),避免類似變量過多時(shí)引入問題
'Sub是當(dāng)前頁面要執(zhí)行的方法,類似main函數(shù),Function為要調(diào)用的方法
'當(dāng)有多個(gè)Sub時(shí),選擇編輯框右上角選擇對(duì)應(yīng)demo名稱
Sub Demo()'Dim 定義變量 As Integer(整數(shù))
Dim i As Integer, iSum As Integer'for 循環(huán)語句For i = 1 To 10'斷言,每次都會(huì)被執(zhí)行,開始寫代碼可能會(huì)用到,之后,常用斷點(diǎn)方式調(diào)試代碼'Debug.Assert i < 9iSum = iSum + add(i)'vbTab 表示一個(gè)table(空字符),不同變量與字符間用","號(hào)分割,"'"表示注釋'Debug.Print vbTab, "i=", i, vbTab, "sum", iSumDebug.Print "i= ", i, " sum ", iSumNext i
End Sub
Function add(num As Integer) As Integer
add = num + num
End Function
######################################################################
第二講:
shape對(duì)象:自選對(duì)象圖片藝術(shù)字任意多邊形OLE對(duì)象(Object Linking and Embedding對(duì)象連接于嵌入)
常用shape對(duì)象(集合)Shapes集合:代表文檔中所有圖形對(duì)象ShapeRange集合:代表文檔中的圖形對(duì)象指定的子集(圖形對(duì)象的一部分)Shape:代表單個(gè)圖形對(duì)象實(shí)例:
'為了便于看到效果,需要在表格中插入"圖形"等,設(shè)置下輪廓,不要帶填充色
'包含多個(gè)Sub時(shí),在當(dāng)前編輯框右上角選擇執(zhí)行的demo
Sub demo1()
'選中所有的圖形對(duì)象
ActiveSheet.Shapes.SelectAll
'選擇第一個(gè)和第三個(gè)圖像對(duì)象,沒有找到圖形對(duì)象時(shí)報(bào)1004錯(cuò)誤
'代碼按序執(zhí)行,先選中所有,在選中1,3,最終選中效果為后者
ActiveSheet.Shapes.Range(Array(1, 3)).Select
End Sub
' 創(chuàng)建的圖像(如:圖形)最好不要帶填充色,設(shè)置下輪廓即可,不然很可能看不出效果
Sub demo2()
ActiveSheet.Shapes.Range(Array(1, 2, 3)).Select
'設(shè)置背景色為紅色(注意:有些圖形對(duì)象沒有屬性Forecolor等或者寫錯(cuò)了而找不到,報(bào)錯(cuò)438)
Selection.ShapeRange(1).Fill.BackColor.RGB = RGB(255, 0, 0)
End Sub為Shape對(duì)象指定宏代碼手工設(shè)置宏代碼:鼠標(biāo)有右鍵指定宏即可。vba設(shè)備宏代碼:OnAction為shape指定多個(gè)宏,見舉例一:使用全局變量實(shí)現(xiàn)相同效果,見舉例二:shape指定多個(gè)宏,舉例一:
Sub Macro1()
'ActiveSheet.Shapes 獲取所有的圖形對(duì)象
Dim i As Integer
For Each Shape In ActiveSheet.shapes
i = i + 1
'將每一個(gè)值圖形對(duì)象的值寫入表格(這里表格開始位置為<0,0>)
Cells(i, 1).Value = Shape.Name
Next
'vbInformation后邊是彈出框title
MsgBox "Welcome First!", vbInformation, "hello"
'通過獲取的所有對(duì)象名稱,點(diǎn)擊某個(gè)已知對(duì)象
ActiveSheet.shapes("缺角矩形 8").OnAction = "Macro2"
End Sub
Sub Macro2()
MsgBox "Hello!Second!", vbInformation, "Second"
ActiveSheet.shapes("缺角矩形 8").OnAction = "Macro1"
End Sub
'創(chuàng)建私有方法
Private Sub CommandButton1_Click()
Dim i As Integer
For Each Shape In shapes
i = i + 1
Cells(i, 1).Value = Shape.Name
Next
End Subshape指定多個(gè)宏,舉例二:
'設(shè)置全局變量
Public iFlag As Boolean
'對(duì)于私有方法的調(diào)用,1、右鍵圖形對(duì)象,指定宏,將私有方法名稱協(xié)商即可點(diǎn)擊
Private Sub CommandButton1_Click()
If iFlag ThenMsgBox "first", vbInformation, "one"
ElseMsgBox "secondd", vbInformation, "two"
End If
iFlag = Not iFlag
End Sub
######################################################################
第三講
制作帶自殺功能的工作薄文件自殺功能制作原因:文件保護(hù),限制未授權(quán)的人查看實(shí)現(xiàn)步驟:1、用VBA設(shè)置工作薄屬性為只讀2、使用VBA文件操作指令進(jìn)行自刪除
舉例(手工點(diǎn)擊按鈕刪除);
'圖像對(duì)象添加這個(gè)宏之前,一定備份一份,這個(gè)方法會(huì)刪除當(dāng)前文件
Sub KillThisworkBook()With ThisWorkbook'設(shè)置默認(rèn)保存,不提示是否保存.Saved = True'設(shè)置工作薄只讀
.ChangeFileAccess xlReadOnly'讀寫狀態(tài),寫的密碼,不可被訪問是否提示(默認(rèn)true)'.ChangeFileAccess(Mode,WritePassword,Notify)'除當(dāng)前文件,Kill是自帶的方法,FullName表示文件的完整路徑(它們之間有個(gè)空格)
Kill .FullName.CloseEnd With
End Sub
舉例(工作薄打開時(shí)刪除,這里宏是針對(duì)ThisWorkbook的,不是寫在sheet、模塊里的);
'首先點(diǎn)擊編輯框左上角,選擇worksheet,然后選擇open方法
Sub KillThisWorkbook()With ThisWorkbook'設(shè)置默認(rèn)保存,不提示是否保存.Saved = True'設(shè)置工作薄只讀
.ChangeFileAccess xlReadOnly'讀寫狀態(tài),寫的密碼,不可被訪問是否提示(默認(rèn)true)'.ChangeFileAccess(Mode,WritePassword,Notify)'除當(dāng)前文件,Kill是自帶的方法,FullName表示文件的完整路徑(它們之間有個(gè)空格)
Kill .FullName.CloseEnd With
End Sub
Private Sub Workbook_Open()
'調(diào)用刪除方法
Call KillThisWorkbook
End Sub
'2007版,由于VBA或者宏代碼有一定的危害性,為了提高安全意識(shí),凡是以XLSX為
'后綴名的文件都不能含有宏代碼,需要將后綴保存為XLSM,執(zhí)行正確
刪除條件,列:非指定用戶,計(jì)算機(jī),路徑,時(shí)間,打開次數(shù)讀寫注冊(cè)表讀寫指定文件讀寫指定單元格讀寫隱藏名稱讀寫文檔屬性
打開次數(shù)舉例--隱藏名稱:
'2003是在插入-名稱-自定義
'2007使用默認(rèn)快捷鍵(與2003一樣):Ctrl+F3
'在文檔中Ctrl+F3插入名稱:引用名稱,opentimes;位置,工作薄;引用,=0(表常量)
Sub ReadOpentimer()Dim OTimer As Integer'opentimes為表格文檔中插入的名稱'Evaluate將名稱獲取對(duì)應(yīng)值OTimer = Evaluate(ThisWorkbook.Names("opentimes").RefersTo)OTimer = OTimer + 1If OTimer > 3 Then'Call KillThisWorkbookMsgBox "這里調(diào)用要執(zhí)行的刪除操作:KillThisWorkbook!!!"ElseThisWorkbook.Names("opentimes").RefersTo = "-" & OTimerEnd If
End Sub
Sub HideNames()'設(shè)置名稱不可見'ThisWorkbook.Names("opentimes").Visible = FalseThisWorkbook.Names("opentimes").Visible = True
End Sub
Sub AddHiddenNames()
'添加引用的名稱,并且設(shè)置為0
ThisWorkbook.Names.Add Name:="opentimes", RefersTo:="=0", Visible:=flase
End Sub'調(diào)用
Private Sub Workbook_Open()Call ReadOpentimer
End Sub
Sub KillThisWorkbook()With ThisWorkbook.Save = True.ChangeFileAccess xlReadOnlyKill .FullName.CloseEnd With
End Sub打開次數(shù)舉例--文檔屬性:
'2003 文件 屬性 自定義 名稱,opentimes;取值,0
'2007 準(zhǔn)備 屬性 (左側(cè)小三角) 高級(jí)屬性 自定義 名稱,opentimes;取值,0
Sub ReadOpentimer()Dim opentimes As IntegerWith Meopentimes = .CustomDocumentProperties("opentimes").Value + 1opentimes = opentimes + 1If opentimes > 3 Then'Call KillThisWorkbookMsgBox "這里調(diào)用要執(zhí)行的刪除操作:KillThisWorkbook!!!"Else.CustomDocumentProperties("opentimes").Value = opentimes.SaveEnd IfEnd With
End Sub
Private Sub Workbook_Open()Call ReadOpentimer
End Sub
Sub KillThisWorkbook()With ThisWorkbook.Saved = True.ChangeFileAccess xlReadOnlyKill .FullName.CloseEnd With
End Sub
'代碼添加文檔屬性
Sub AddCustomDocumentProperties()ThisWorkbook.CustomDocumentProperties.Add _Name:="opentimes_1", _LinkToContent:=False, _Type:=msoPropertyTypeNumber, _Value:=8888
End Sub
######################################################################
第四講加載宏的概念和分類加載宏是一類程序,他們可以擴(kuò)張Excel的功能,為Excel提供可用于公式
中的新的工作表函數(shù)(自定義函數(shù))、提那家可選指令和功能宏的加載方式3中,Excel、com加載宏和自動(dòng)化加載宏Excel加載宏:是Excel編寫VBA編寫的程序是Excel文檔
Excel加載宏特點(diǎn):共享性,便于擴(kuò)張;隱藏性,運(yùn)行時(shí)是隱藏的;不受宏安全級(jí)限制
場合:多個(gè)工作薄使用使用步驟:1、制作Excel加載宏文檔,結(jié)合舉例看1.1、創(chuàng)建普通Excel文檔1.2、將上步普通Excel另存為xls(加載宏的默認(rèn)格式)。2003版,格式后綴加載宏xls;2007版后綴加載宏xla保存時(shí),便于查看,最好是將宏和要使用改宏的文檔放在同一目錄下2、加載Excel加載宏文檔3、卸載加載宏,工具 加載宏,去掉宏的選中狀態(tài)宏不想保留,修改注冊(cè)表法和刪除宏文檔法(立即窗口輸入AddIns("文檔宏名稱").FullName)
加載方式步驟:1、手工加載: 工具 加載宏2、安裝加載:瀏覽 添加自定義的宏 選中(2007 Excel選項(xiàng) 加載項(xiàng) 轉(zhuǎn)到 瀏覽)
自動(dòng)加載保存在特定的位置,會(huì)自動(dòng)讀取
舉例:
'新建空文檔創(chuàng)建宏方法
'Function xxx 括號(hào)里的是傳入?yún)?shù)及類型,括號(hào)是返回值類型
Function CustomDefineAdd(char As Integer) As Integer'將最終結(jié)果返回到CustomDefineAddCustomDefineAdd = char + char
End Function定制加載宏:創(chuàng)建加載宏信息在創(chuàng)建加載宏文檔時(shí)定義信息,文檔屬性(準(zhǔn)備 屬性) 摘要(標(biāo)題 和 備注)修改已存在的宏信息需要在VBA視圖修改可編輯的狀態(tài)值,編輯完代碼后,在改成不可編輯的
######################################################################
第5講ADO應(yīng)用技巧制作相片的學(xué)生證1、制作學(xué)生證常規(guī) 方法常規(guī)方法獲取數(shù)據(jù)庫中國的圖片步驟:獲取數(shù)據(jù)庫中的圖片資料的二進(jìn)制數(shù)組使用open語句把二進(jìn)制數(shù)組寫入臨時(shí)文件使用LoadPicture函數(shù)把圖片顯示在控件上使用kill語句刪除臨時(shí)文件2、制作學(xué)生證 api自編函數(shù)API(應(yīng)用程序編程接口)將二進(jìn)制數(shù)組轉(zhuǎn)換為可以是Image控件接受的Picture對(duì)象把Picture對(duì)象顯示在Image控件中
此節(jié),需要單獨(dú)了解把圖片保存到數(shù)據(jù)庫(mdb,ldb)以及實(shí)現(xiàn)步驟中用到的方法
######################################################################
第6講
Access數(shù)據(jù)庫中的鏈接表鏈接表:類似快捷方式手工創(chuàng)建鏈接表:文件|空白右鍵選擇鏈接表代碼創(chuàng)建鏈接表: Catalog對(duì)象建立table對(duì)象設(shè)置ParentCatalog屬性值和Properties集合的各項(xiàng)值把Table對(duì)象添加到Catalog對(duì)象的Tables集合中
略。
######################################################################
第7講
Excel VBA類的初步應(yīng)用子類化實(shí)現(xiàn)控件數(shù)組控件數(shù)組是指具有相同名稱和類型并且具有相同的事件過程的一個(gè)或者多個(gè)控件
查詢窗體實(shí)例任務(wù)背景常規(guī)設(shè)計(jì)方法(處理重復(fù)的內(nèi)容時(shí),需要寫很多遍,如:很多個(gè)按鈕)子類化方法:通過使用類技術(shù),把相同的時(shí)間過程寫在一個(gè)類模塊中,使多個(gè)相同類型的控件可以共同調(diào)用改事件的過程的方法子類化實(shí)現(xiàn)控件數(shù)組類:類是一個(gè)模塊;類是對(duì)象的模板,可以被多次調(diào)用,生成相同性質(zhì)的對(duì)象子類化的步驟:1、創(chuàng)建類:插入 類(name:CommandWithEw)2、編寫類代碼:3、調(diào)用類代碼WithEvents變量說明:1、withEvents變量不能是派生對(duì)象變量,即不能把它聲明為As Object--當(dāng)聲明該變量時(shí)必須制定類名2、不能把WithEvents聲明為As New,必須明確地創(chuàng)建事件源對(duì)象,并把它賦給WithEvents變量3、不能在標(biāo)準(zhǔn)模塊中聲明WithEvents變量,只能在類模塊、窗體模塊以及其他定義類的模塊中聲明類模塊中的公共變量--添加類屬性Public WithEvents cmd As MSForms.CommandButton
界面實(shí)現(xiàn)步驟:1、打開VBA,右鍵工作薄,創(chuàng)建form;修改名稱:userQuery;2、添加組件:A,顯示提示信息;ab,文本輸入框;其他,將鼠標(biāo)放在“Toolbox”上懸停,即可看到相關(guān)組件提示信息。界面顯示大致效果:要查詢的ID: "這里是輸入框(name:txbID)""按鈕,顯示1(name:cmd1)" "按鈕,顯示2(name:cmd2) ..."按鈕,顯示6(name:cmd6)" "按鈕,顯示7(name:cmd7)" ..."按鈕,顯示查詢(name:cmdQuery)" "按鈕,顯示取消(name:cmdCancel)"說明:通過按Ctrl+鼠標(biāo)左鍵拖動(dòng),可復(fù)制一個(gè)或多個(gè)(選中多個(gè)拖動(dòng)),按鈕修改顯示字符,先選中按鈕組件,過1秒,在點(diǎn)擊按鈕一次。
常規(guī)方法舉例:
'將name為cmd0組件的caption頭,追加到txbID組件中
'txbID.Text = txbID.Text & cmd0.Caption
'點(diǎn)擊運(yùn)行,每點(diǎn)擊一個(gè)按鈕,輸入框中都會(huì)多一個(gè)數(shù)字
Private Sub cmd0_Click()
txbID.Text = txbID.Text & cmd0.Caption
End SubPrivate Sub cmd1_Click()
txbID.Text = txbID.Text & cmd1.Caption
End SubPrivate Sub cmd2_Click()
txbID.Text = txbID.Text & cmd2.Caption
End SubPrivate Sub cmd3_Click()
txbID.Text = txbID.Text & cmd3.Caption
End SubPrivate Sub cmd4_Click()
txbID.Text = txbID.Text & cmd4.Caption
End SubPrivate Sub cmd5_Click()
txbID.Text = txbID.Text & cmd5.Caption
End SubPrivate Sub cmd6_Click()
txbID.Text = txbID.Text & cmd6.Caption
End SubPrivate Sub cmd7_Click()
txbID.Text = txbID.Text & cmd7.Caption
End SubPrivate Sub cmd8_Click()
txbID.Text = txbID.Text & cmd8.Caption
End SubPrivate Sub cmd9_Click()
txbID.Text = txbID.Text & cmd9.Caption
End SubPrivate Sub cmdCancel_Click()'本節(jié)不處理
Unload Me
End SubPrivate Sub cmdQuery_Click()MsgBox "這是一個(gè)測(cè)試!"
End Sub子類化方法舉例:
'創(chuàng)建一個(gè)類(name:CommandWithEvents)
Option Explicit
'WithEvents變量可以被公共調(diào)用,類屬性cmd
Public WithEvents cmd As MSForms.CommandButton
'在對(duì)象列表(編輯框左上角)選擇cmd,過程列表(編輯框右上角)選擇click事件
Private Sub cmd_Click()'按鈕被點(diǎn)擊時(shí),將點(diǎn)擊內(nèi)容添加都輸入框userQuery.txbID.Text = userQuery.txbID.Text & cmd.Caption
End Sub'在窗體userQuery中填寫如下代碼
'添加聲明,模塊級(jí)變量
Option Explicit
'與按鈕組件數(shù)一致,注意arrCmd和cmdb對(duì)應(yīng)的i保持一致
Dim arrCmd(0 To 9) As CommandWithEventsPrivate Sub cmdCancel_Click()'不處理
Unload Me
End SubPrivate Sub cmdQuery_Click()MsgBox "這是一個(gè)測(cè)試!"
End SubPrivate Sub UserForm_Click()End Sub
'選擇userform窗體對(duì)象后,右上角選擇Initialize方法
Private Sub UserForm_Initialize()Dim i As IntegerDim cmdb As CommandWithEventsFor i = 0 To 9'創(chuàng)建新對(duì)象Set cmdb = New CommandWithEventsSet cmdb.cmd = Me.Controls("cmd" & i)Set arrCmd(i) = cmdb'釋放變量Set cmdb = NothingNext i
End Sub子類化方法舉例--代碼循環(huán)創(chuàng)建組件:
'將所有的組件,剪貼掉,然后添加組件frame(name:frame1),
'第一次創(chuàng)建默認(rèn)是frame1,通過循環(huán)就不需要在關(guān)注內(nèi)部按鈕名稱了
'添加聲明,模塊級(jí)變量
Option Explicit
'與按鈕組件數(shù)一致,注意arrCmd和cmdb對(duì)應(yīng)的i保持一致
Dim arrCmd(0 To 9) As CommandWithEventsPrivate Sub cmdCancel_Click()'不處理
Unload Me
End SubPrivate Sub cmdQuery_Click()MsgBox "這是一個(gè)測(cè)試!"
End SubPrivate Sub UserForm_Click()End Sub
'選擇userform窗體對(duì)象后,右上角選擇Initialize方法
Private Sub UserForm_Initialize()Dim i As IntegerDim cmdb As CommandWithEventsFor i = 0 To Me.Frame1.Controls.Count - 1 '9,注意取值范圍'創(chuàng)建新對(duì)象Set cmdb = New CommandWithEventsSet cmdb.cmd = Me.Frame1.Controls(i) 'Me.Controls("cmd" & i)Set arrCmd(i) = cmdb'釋放變量Set cmdb = NothingNext i
End Sub
'舉例測(cè)試--控件事件本身的運(yùn)行,先于子類化:
'在上邊代碼的基礎(chǔ)上,添加
Private Sub cmd1_Click()
MsgBox "控件事件本身的運(yùn)行,先于子類化!"
End Sub
######################################################################
第八講控件的應(yīng)用輸入時(shí)逐步提示信息(下拉菜單等)作用,提供輸入效率和正確率實(shí)現(xiàn),在工作薄中建立基礎(chǔ)數(shù)據(jù)表1、將中文轉(zhuǎn)換為拼音首字母2、輸入時(shí)自動(dòng)轉(zhuǎn)換使用文本框控件和列表框控件控件的顯示和隱藏輸入時(shí)逐步提示將提示信息寫入工作表步驟;
產(chǎn)品名稱 輔助列
食品 sp
... ...需要方法:將中文轉(zhuǎn)換為拼音首字母、輸入時(shí)自動(dòng)轉(zhuǎn)換(發(fā)生時(shí)間,工作薄內(nèi)容
發(fā)生改變時(shí))######################################################################
參考網(wǎng)址:
http://video.1kejian.com/video/?67331-0-1.html
http://www.feiesoft.com/vba/word/
其他參考:
文檔打開時(shí),工作薄最大化
'對(duì)Thisworkbook添加open時(shí)事件
Private Sub Workbook_Open()Application.WindowState = xlMaximizedActiveWindow.WindowState = xlMaximizedWorksheets("Sheet1").ActivateRange("A1").Select
End Sub
?
轉(zhuǎn)載于:https://www.cnblogs.com/leonlipfsj/p/6361147.html
總結(jié)
以上是生活随笔為你收集整理的Excel VBA(宏):添加宏的全部內(nèi)容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 使用ajax获取用户所在地的天气
- 下一篇: 数据挖掘资料