基于Excel VBA的財(cái)務(wù)自動(dòng)化 課件全套 張澤華 第1-15章 VBA入門知識(shí) - vba與財(cái)務(wù)機(jī)器人

第1章

VBA入門知識(shí)excel中的重復(fù)性操作，需要使用VBA來(lái)進(jìn)行excel的自動(dòng)化處理VBA(VisualBasicForApplication)是一種實(shí)現(xiàn)自動(dòng)化的編程語(yǔ)言1.1VBAVBA中一段完整的可以執(zhí)行的語(yǔ)句就是過(guò)程1.2子過(guò)程錄制宏來(lái)提升excel的自動(dòng)化程度1.2.1錄制宏表單控件中有命令按鈕、文本框、列表框、選項(xiàng)按鈕等每個(gè)按鈕指定不同的宏以完成不同的任務(wù)1.2.2使用表單控件關(guān)聯(lián)宏錄制宏的過(guò)程就是將我們對(duì)excel表格的操作，記錄為可執(zhí)行VBA代碼語(yǔ)句的過(guò)程1.2.3什么是宏for循環(huán)幫助我們解決重復(fù)操作1.3For循環(huán)：批量制作工資條結(jié)構(gòu)：For計(jì)數(shù)器=初值to終值[Step步長(zhǎng)值]

循環(huán)體(要重復(fù)執(zhí)行的操作或計(jì)算)Next1.4ForNext語(yǔ)句1.4.1ForNext的語(yǔ)法1.4.2ForNext語(yǔ)句的執(zhí)行過(guò)程Fori=5To1Step-1……Next1.4.3特殊的步長(zhǎng)值本章介紹了VBA概念、VBA的編程界面-VBE、宏的錄制、宏的運(yùn)行、宏代碼的查看等基本內(nèi)容1.5本章小結(jié)第2章

程序設(shè)計(jì)的基本知識(shí)VBE窗口是編寫VBA程序的地方2.1VBA的集成開發(fā)環(huán)境-VBE代碼編輯區(qū)就是顯示以及編輯VBA代碼的窗口，是VBE最主要的功能之一2.1.1代碼編輯區(qū)2.1.2萊單欄2.1.3工具欄2.1.4工程資源管理器VBA代碼基本構(gòu)成要素有常量、變量、表達(dá)式、語(yǔ)句和函數(shù)等2.2程序的基本要素2.2.1基本數(shù)據(jù)類型根據(jù)數(shù)據(jù)的值在程序運(yùn)行過(guò)程中是否可以變化，可將其劃分為常量和變量2.2.2常量與變量1.算術(shù)運(yùn)算符2.比較運(yùn)算符3.邏輯運(yùn)算符4.連接運(yùn)算符2.2.3運(yùn)算符MsgBox函數(shù)用于輸出一個(gè)對(duì)話框InputBox函數(shù)將打開一個(gè)輸入對(duì)話框作為輸入數(shù)據(jù)的界面2.2.4交互函數(shù)程序控制結(jié)構(gòu)主要有三種方式：順序結(jié)構(gòu)、循環(huán)結(jié)構(gòu)和分支結(jié)構(gòu)2.3程序控制結(jié)構(gòu)1.ForEach……Next語(yǔ)句ForEach變量1In集合名稱或數(shù)組名稱語(yǔ)句塊1Next2.Do循環(huán)Do<While|Until>循環(huán)條件循環(huán)體Loop2.3.1循環(huán)結(jié)構(gòu)①單分支結(jié)構(gòu)：If...Then結(jié)構(gòu)②雙分支結(jié)構(gòu)：IF...Then...Else語(yǔ)句③多分支結(jié)構(gòu)：由IF...Then...Elseif語(yǔ)句2.3.2分支結(jié)構(gòu)第3章

ExcelVBA的重要概念VBA中提供了100多個(gè)對(duì)象打開、關(guān)閉工作簿，增加、刪除工作表都是操作Excel的對(duì)象3.1Excel的對(duì)象'在A2單元格中寫入“銀行存款”Application.Workbooks("理解對(duì)象的作用.xlsm").Worksheets("Sheet1").Range("A2")="銀行存款"3.1.1VBA代碼與VBA對(duì)象3.1.2對(duì)象的層次結(jié)構(gòu)對(duì)象集合是對(duì)相同類型對(duì)象的統(tǒng)稱3.1.3對(duì)象集合及對(duì)象的基本表示方法對(duì)象變量的設(shè)置需要兩個(gè)步驟：1.使用聲明語(yǔ)句將變量指定為對(duì)象類型Dim變量名as對(duì)象型變量2.實(shí)例化變量，給對(duì)象變量賦值 Set對(duì)象變量=具體對(duì)象3.1.4對(duì)象變量對(duì)象的屬性、方法和事件3.2對(duì)象的屬性、方法、事件屬性是對(duì)象的一種特征每個(gè)對(duì)象都可能有多個(gè)屬性對(duì)象和屬性之間用英文符號(hào)“.”連接對(duì)象.屬性=屬性值3.2.1對(duì)象的屬性方法是作用于對(duì)象的操作每個(gè)對(duì)象都可能有多個(gè)方法對(duì)象和方法之間也用英文句點(diǎn)“.”連接對(duì)象.方法【參數(shù)列表】3.2.2對(duì)象的方法【屬性/方法】列表列表中帶綠色圖標(biāo)的項(xiàng)是方法帶手形灰色圖標(biāo)的是屬性3.2.3怎么分辨屬性與方法對(duì)于對(duì)象而言，事件就是發(fā)生在該對(duì)象上的事情或消息3.2.4對(duì)象的事件①Activate事件②Click事件③DblClick事件④Load事件⑤MouseDown事件3.2.5對(duì)象常用的事件本章主要介紹了利用Excel4大對(duì)象Range、Worksheet、Wordbook、Application各自屬性和方法來(lái)操作各個(gè)對(duì)象3.2.6本章小結(jié)第4章

VBA操作主要對(duì)象Excel對(duì)象模型最頂端的是Application對(duì)象4.1操作應(yīng)用程序?qū)ο驡etOpenFilename方法獲取該文件的全路徑名稱4.1.1獲取文件全路徑名稱4.1.2Application對(duì)象常用屬性Workbooks.AddWorkbooks.AddTemplate:="D:\新建文件夾\備忘工作簿.xlsm"Workbooks.Add"D:\新建文件夾\備忘工作簿.xlsm"4.2操作工作簿對(duì)象4.2.1新建工作簿W(wǎng)orkbooks對(duì)象的Open方法打開工作簿W(wǎng)orkbooks.OpenFilename:="含路徑的名稱"4.2.2打開工作簿W(wǎng)orkbooks.CloseThisWorkbook.Save4.2.3關(guān)閉與保存工作簿W(wǎng)orkbooks("工作簿1").Activate4.2.4激活工作簿W(wǎng)orksheets.AddWorksheets.Addbefore:=Worksheets(1)Worksheets.Addafter:=Worksheets(1)4.3操作工作表對(duì)象4.3.1新建工作表Worksheets("sheet1").Delete4.3.2刪除工作表Worksheets(1).CopyWorksheets(1).Move4.3.3復(fù)制和移動(dòng)工作表Worksheets("sheet1").ProtectPassword:=123Worksheets(1).Visible=False4.3.4保護(hù)與隱藏工作表Name屬性該屬性返回工作表的名稱Count屬性該屬性返回指定集合中對(duì)象的數(shù)目4.3.5工作表的其他常用屬性Range對(duì)象是ExcelVBA中使用最頻繁的對(duì)象4.4操作單元格對(duì)象range引用單元格range引用單元格區(qū)域range引用多區(qū)域單元格range的嵌套使用4.4.1引用單元格利用Cells引用單元格利用Offset引用相對(duì)位置的單元格利用Resize調(diào)整引用區(qū)域大小利用End獲取區(qū)域最后的單元格4.4.1引用單元格引用整行單元格

ActiveSheet.Rows.Select引用整列單元格

ActiveSheet.Columns.Select引用工作表中已經(jīng)使用的區(qū)域

ActiveSheet.UsedRange.Select4.4.2引用相關(guān)區(qū)域利用Copy方法復(fù)制利用Cut方法剪切PasteSpecial選擇性粘貼4.4.3剪切、復(fù)制、粘貼Range("A1").Value="固定資產(chǎn)“Range("B4").Delete4.4.4修改、刪除單元格Formula屬性就是以A1方法表示FormulaR1C1屬性則是以R1C1方法表示4.4.5Formula與FormulaR1C1屬性第5章

函數(shù)簡(jiǎn)稱工作表函數(shù)指可以在Excel工作表界面直接使用的函數(shù)5.1工作表內(nèi)置函數(shù)VBA自身內(nèi)置的函數(shù)數(shù)學(xué)函數(shù)轉(zhuǎn)換函數(shù)字符串處理函數(shù)日期函數(shù)5.2VBA內(nèi)置函數(shù)Function函數(shù)名(變量)…Endfunction5.3自定義函數(shù)5.3.1設(shè)計(jì)自定義函數(shù)1.宏更新函數(shù)值2.Application.Volatile5.3.2更新自定義函數(shù)值5.3.3自定義人民幣轉(zhuǎn)大寫函數(shù)第6章

對(duì)賬業(yè)務(wù)6.1銀企對(duì)賬6.1.1銀企對(duì)賬系統(tǒng)的設(shè)計(jì)6.1.2銀企對(duì)賬系統(tǒng)的應(yīng)用效果步驟：

1.往來(lái)賬勾對(duì)工作表設(shè)計(jì)2.確定兩筆分錄的比對(duì)思路6.2往來(lái)賬勾對(duì)6.2.1往來(lái)賬勾對(duì)宏的設(shè)計(jì)CurrentRegion屬性確定數(shù)據(jù)區(qū)域6.2.2往來(lái)賬勾對(duì)宏的說(shuō)明6.2.3往來(lái)賬勾對(duì)宏的應(yīng)用效果第8章

統(tǒng)計(jì)報(bào)表的批量生成與分發(fā)根據(jù)已有的模板工作表生成當(dāng)月每日的空白日?qǐng)?bào)表8.1根據(jù)模板批量生成日?qǐng)?bào)表統(tǒng)計(jì)了日?qǐng)?bào)表后需要將日?qǐng)?bào)表數(shù)據(jù)進(jìn)行匯總統(tǒng)計(jì)8.2將日?qǐng)?bào)表數(shù)據(jù)匯總將日?qǐng)?bào)表按日期拆分為不同的文件并將拆分出的文件以工作表名字命名8.3將日?qǐng)?bào)表按日期拆分為不同文件根據(jù)配置表的形式批量生成日?qǐng)?bào)表8.4根據(jù)模板批量生成部門日?qǐng)?bào)表刪除工作表、刪除range對(duì)象、刪除行從后往前刪8.5批量刪除日?qǐng)?bào)表先鎖定整張工作表的單元格取消鎖定要輸入數(shù)據(jù)的單元格或區(qū)域8.6分發(fā)工作表的填寫范圍保護(hù)設(shè)計(jì)用戶名與密碼比對(duì)模塊根據(jù)不同用戶顯示不同工作表8.7為用戶顯示個(gè)性化工作表第9章

日記賬數(shù)據(jù)拆分到多表首先，按日記賬的D列部門名稱新建工作表其次，去重建表9.1去重名生成部門表將日記賬中的數(shù)據(jù)拆分到對(duì)應(yīng)名稱的部門表中9.2拆分?jǐn)?shù)據(jù)到部門表運(yùn)行sub過(guò)程之前應(yīng)將各部門表中的原有記錄清空，以避免數(shù)據(jù)冗余9.3填寫前清空已有記錄【篩選】功能由AutoFilter方法實(shí)現(xiàn)FieldCriteria19.4利用篩選優(yōu)化拆分?jǐn)?shù)據(jù)使用Inputbox函數(shù)由用戶輸入按哪一列進(jìn)行拆分9.5按需選擇拆分?jǐn)?shù)據(jù)列第10章

跨文件匯總報(bào)表Dir函數(shù)的基本用法是Dir(pathname)10.1認(rèn)識(shí)Dir函數(shù)SubdirTest()MsgBox(Dir("d:\1\廣東分公司.xlsx"))EndSub10.2判斷分支機(jī)構(gòu)上報(bào)文件是否存在Dir(ThisWorkbook.Path&"\*.*")'匹配文件所在文件夾內(nèi)所有文件10.3獲取文件夾內(nèi)的所有文件Sub跨文件操作通用模板()DimstrAsStringstr=Dir("d:\1\*.xls*")DoWhilestr<>""‘這里可寫入針對(duì)文件進(jìn)行操作的所有語(yǔ)句

str=DirLoopEndSub10.4跨文件操作的通用模板Dir("d:\1\example\*.xls*")DoWhilestr<>""

’復(fù)制

’重命名Loop10.5跨文件匯總部門日?qǐng)?bào)表相比10.5節(jié)的sub過(guò)程增加一個(gè)ForEach循環(huán)遍歷新打開文件的每一張工作表10.6跨文件匯總所有報(bào)表Left()、Len()構(gòu)建填充公式FormulaR1C1屬性進(jìn)行公式填充10.7將跨文件報(bào)表數(shù)據(jù)匯總第11章

數(shù)組及其財(cái)務(wù)應(yīng)用數(shù)組：速度快非數(shù)組：速度慢11.1數(shù)組與非數(shù)組處理文件數(shù)組的維度：一維數(shù)組、二維數(shù)組11.2認(rèn)識(shí)數(shù)組數(shù)組的聲明：Dim數(shù)組名（上界）as數(shù)據(jù)類型Dim數(shù)組名（下界to上界）as數(shù)據(jù)類型11.2認(rèn)識(shí)數(shù)組動(dòng)態(tài)數(shù)組：Dim數(shù)組名（）as數(shù)據(jù)類型11.2認(rèn)識(shí)數(shù)組數(shù)組的賦值A(chǔ)rr(1)=3;Arr(3)=811.2認(rèn)識(shí)數(shù)組數(shù)組與Range對(duì)象：arr=Range("a1:a2")11.2認(rèn)識(shí)數(shù)組方式：錄制宏For循環(huán)數(shù)組11.3查找各分支機(jī)構(gòu)收入最高商品11.4記錄拆分成借貸不同行第7章

發(fā)票湊數(shù)7.1發(fā)票湊數(shù)7.1.1發(fā)票湊數(shù)宏的設(shè)計(jì)調(diào)用過(guò)程Sub執(zhí)行另一個(gè)過(guò)程()Call過(guò)程名(參數(shù)1,參數(shù)2，...)EndSub7.1.2發(fā)票湊數(shù)宏的說(shuō)明遞歸SubFac(nAsInteger)Ifn=1ThenFAC=1Else:FAC=n*FAC(n-1)EndIfEndSub7.1.2發(fā)票湊數(shù)宏的說(shuō)明7.1.3發(fā)票湊數(shù)宏的應(yīng)用效果第12章

字典及其財(cái)務(wù)應(yīng)用字典的調(diào)用引用法：

DimdicAsNewDictionary直接創(chuàng)建法：DimdicAsObjectSetdic=CreateObject("scripting.dictionary")12.1認(rèn)識(shí)字典字典的屬性與方法：6種方法：Add、Keys、Items、Exists、Remove、RemoveAll4個(gè)屬性：count、Key、Item、CompareMode12.1認(rèn)識(shí)字典思路：字典去重若沒有存貨編碼，則會(huì)自動(dòng)新建本行存貨代碼的key若已有存貨編碼，則不會(huì)新建12.2批量查找最近日期的價(jià)格思路：字典去重客戶名稱設(shè)定為字典的key發(fā)貨數(shù)量設(shè)定為字典的item12.3批量匯總客戶累計(jì)發(fā)貨數(shù)量思路：字典

“A列+B列”為key特定行號(hào)賦值為item12.4批量匯總客戶按月累計(jì)發(fā)貨數(shù)量第13章

窗體與控件13.1初識(shí)窗體與控件插入窗體步驟：①創(chuàng)建一個(gè)新的工作簿②【工具】→【宏】→【VisualBasic編輯器】③【插入】→【用戶窗體】13.2用戶窗體13.2.1創(chuàng)建用戶窗體及給窗體添加對(duì)象對(duì)象之間的對(duì)齊改變對(duì)象大小控制對(duì)象的間距修改對(duì)象的Tab鍵次序13.2.2調(diào)整窗體中的對(duì)象名稱屬性Caption屬性Left、Top、Height、Width屬性Enabled屬性13.2.3窗體常用的屬性對(duì)象激活(Activate)事件單擊(Click)事件雙擊(DblClick)事件對(duì)象變?yōu)榉腔顒?dòng)(Deactivate)事件改變窗體大小(Resize)事件Show/Hide常用方法13.2.4窗體的常用事件和方法13.3控件13.3.1標(biāo)簽13.3.2文本框13.3.3命令按鈕13.3.4列表框與復(fù)合框13.3.5復(fù)選框與選項(xiàng)按鈕第14章

憑證處理與賬簿查詢憑證處理模塊