Excel技能樹系列09:宏與VBA

2020-12-22 myEagleeyes

1970年，出現了一種專門為普通人而設計的簡單易學的新語言Visual Basic，簡稱VB。甚至只需要了解150個左右的英語單詞，就能輕易的學會使用它。後來微軟吸收了VB的特性，改進了這門語言，用在Office系列軟體中，稱之為Visual Basic for Applications，簡稱VBA。VBA專門用來自動化重複性工作和批量計算等。VBA簡單易學，只要安裝了Office比如Excel，Word等軟體，就可以打開VBE編輯器編寫VBA代碼。1993年，微軟發布了Excel 5.0，並且首次在Excel 5.0中加入了VBA，以處理繁瑣而重複的工作。後面隨著Excel新版本的發布，VBA也在每一次更新中逐步改進。

其實VBA最初設計出來就是為普通人解決重複性任務和工作的，非常簡單易學。通過簡單的學習了解，加上Excel本身的錄製宏代碼功能，應用上就完全沒問題了。本來VBA就是為普通人設計的，其實本系列並不是要講VBA，如果想要學習，這方面的教材不少。我僅僅是整理了一些通用的代碼，不需要懂編程和代碼就可以使用，可以大大方便Excel數據處理工作。結合前面8篇基礎進價到公式高手系列，你會愛上強大的Excel的。

在這篇文章中，鄙人將會列出一些非常實用的VBA代碼，讓你的Excel表格處理工作大大加快，節省下很多的時間用來幹更有意思的事情。主要還是Excel中的大量重複性工作都可以自動交由VBA來完成。這些都是非常實用的代碼，拿來稍微改一下就能夠勝任你的工作。整理在你的私人工具庫裡面，用的時候直接調用，大大方便自己的工作。

雖然Excel可以通過錄製宏來生產VBA代碼，但是通過錄製宏的方式得到的代碼摻雜了太多我們並不需要的代碼，從而影響到運行效率。鄙人會通過實例結合的方式，甚至會教你如何調用VBA，讓它幫你完成工作。即使你沒有接觸過任何程式語言，也能通過這篇文章調用VBA。在你體會到VBA帶來的方便的同時，我希望你能同時打開新技能的窗戶，通過簡單學習即可拷貝網上大部分VBA為你所用。想想以後重複性數據整理啥的工作都可以通過VBA解決，提高自身競爭力的同時還能剩下時間。即使不會代碼也能更快速的處理電子表格，僅僅需要簡單了解和修改，其實大部分重複性工作都能勝任了。

好了，廢話不多說，以下介紹的這些代碼後面會提供一份BAS格式的文件，下載一個notepad++或者直接用記事本打開，複製你需要的那一段代碼到VBE編輯器運行。因為這是Excel VBA導出格式，你也可以下載下來，導入進你的Excel就可以使用。還可以把這個文件保存到微盤或者網盤啥的，隨時隨地使用，工具本身就是為了簡化工作的。也可以收藏這幾篇文章以備不時之需。

開始之前先簡單介紹在Excel中怎樣使用VBA，要在Excel中使用VBA，需要以下幾個步驟：第一步：需要打開你想執行VBA的Excel文件；第二步：按下ALT + F11組合鍵，就可以打開VBE編輯窗口。第三步：在窗口上找到」插入」->」模塊」，在這彈出來的這個編輯器裡面複製粘貼你要用的代碼就行了。

複製完你需要的代碼之後，通過以下幾個步驟來執行VBA。第一步：回到Excel主界面，點擊菜單欄上的「開發工具」菜單，或者按Alt+ F8。第二步：點擊「宏」，在彈出的對話框選擇你需要執行的宏。第三步：點擊運行即可。

萬一你的Excel主界面沒有開發工具這個選項卡呢？很簡單，點擊Excel的「文件」菜單，找到「選項」，點擊進去，找到「自定義功能區」，點擊它。在右邊找到「開發工具」，在「開發工具」前面的小方框打鉤。回到主界面就可以看到開發工具出來啦。現在的已經講解完畢，開始實用VBA代碼的講解。讓我們一起來學習如何使用這些實用代碼來幫助自己做重複性工作以及節省時間吧。

注意事項：你會發現裡面有一些單引號後面有文字，這個是注釋。解釋這段代碼用途的，不會影響到代碼的運行。比如下面這段代碼第一的單引號後面跟的文字就是解釋這段代碼的用途的。

1、下面這段代碼顯示所有隱藏的工作表，因為有時候我們的工作表太多了，所有會隱藏一些工作表。但是顯示的時候只能用滑鼠一個個顯示，不方便。下面這段代碼顯示所有隱藏的工作表，非常方便。

'單引號後面是注釋，這段代碼顯示所有隱藏的工作表Sub UnhideAllWoksheets()Dim ws As WorksheetForEach ws In ActiveWorkbook.Worksheetsws.Visible = xlSheetVisibleNext wsEndSub

2、下面這段代碼隱藏除活動工作表以外的所有工作表。

'隱藏除活動工作表以外的所有工作表Sub HideAllExceptActiveSheet()Dim ws As WorksheetForEach ws In ThisWorkbook.WorksheetsIf ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHiddenNext wsEndSub

3、按照字符順序排序工作表。

'這段代碼按照字符順序排序工作表Sub SortSheetsTabName()Application.ScreenUpdating = FalseDim ShCount AsInteger, i AsInteger, j AsIntegerShCount = Sheets.CountFor i = 1To ShCount - 1For j = i + 1To ShCountIf Sheets(j).Name < Sheets(i).Name ThenSheets(j).Move before:=Sheets(i)EndIfNext jNext iApplication.ScreenUpdating = TrueEndSub

4、一次性保護工作簿中所有的工作表。

'一次性保護所有的工作表Sub ProtectAllSheets()Dim ws As WorksheetDim password AsStringpassword = "Test123"'將Test123替換成你自己的密碼ForEach ws In Worksheetsws.Protect password:=passwordNext wsEndSub

5、一次性取消保護所有的工作表。

'一次性取消保護所有工作表Sub ProtectAllSheets()Dim ws As WorksheetDim password AsStringpassword = "Test123"'將Test123替換成你設置的密碼即可ForEach ws In Worksheetsws.Unprotect password:=passwordNext wsEndSub

本篇就講這幾個吧，後面還會有1篇，總共有17段實用的代碼整理在這些文章中，全部放在一篇太長了，不方便查看。這幾個只是引子，後面會以目錄的形式排列，查看目錄是否有自己需要的代碼，然後進去複製粘貼使用即可。

相關焦點

  • excel VBA是什麼?VBA編程入門教程
    本篇將介紹excel vba是什麼？vba編程入門教程，有興趣的朋友可以了解一下！一、excel vba是什麼？VBA的英文全稱是Visual Basic for Applications，是一門標準的宏語言。VBA語言不能單獨運行，只能被office軟體（如：Word、Excel等）所調用。
  • 比較Excel中的宏和VBA
    在Excel中可以錄製一個宏來快速製作學生成績的抬頭，我們簡單回顧一下這個宏是如何錄製的：1) 首先點擊「開發工具」選項卡；2) 點擊「使用相對引用」；3) 在錄製開始前選中第一行的抬頭；4) 點擊「錄製宏」；5) 複製第一行；6) 隔兩行，也就是在第3行右鍵滑鼠，選中「插入複製的單元格」；7) 點擊「
  • 微軟Excel VBA宏的介紹及如何錄製宏
    宏是系列 Excel命令和指令組合在起，形成了個任務，以自動執行包含多個命令和指令的任務。可以極大提升Excel的效率。一，什麼是宏宏就是用VBA代碼保存下來的程序。宏的基本作就是：減少重複性的操作Excel宏是系列操作步驟的集合，如在單元格中輸數據，複製A3的數據到B4，對C1:D3求和等，這些操作步驟有簡單有複雜。當一些操作步驟需要不斷重複時，我們就要考慮錄製宏。現excel版本採的宏語是VBA（Visual Basic for Applications）。
  • WPS教程:excel新手入門VBA功能使用介紹
    Excel教學：今天和大家分享一下excel中vba的使用方法入門，相信很多使用該軟體的朋友們對此都很感興趣，下面就和大家來分享一下，希望可以幫助到大家。4.輸入如圖所示的代碼：5.保存文件，按下F5執行，找到我們編輯的這個宏,【計算銷售額】,然後點擊【運行】寫在末尾——總結一下WPS用起來還是十分簡單的，後面也會出更多的EXCEL系列教程和其他知識分享。
  • 你天天用的excel也可以用來畫畫,這個工具3分鐘搞定!
    最近小妞迷上了一位77歲日本老大爺，他從畫將近20年，讓我敬佩的是他畫畫的工具，居然是我們每天都在用的excel，這幾天小妞打算寫一篇老大爺的傳記給大家學習一下滴，記得隨時過來看看啦。今天就針對excel主題，告訴大家這個用excel就能畫畫的技能，這個說出都很有面兒有木有。
  • 用excel製作文件管理器,所有版本皆可使用
    Hello，大家好，之前跟大家分享了使用excel中的power query功能製作一個文件管理器，但是很多分析反應自己的excel版本不夠高，無法使用，今天就跟大家分享如何使用宏表函數製作文件管理器，他也是可以實現文件刷新的，這個的操作也不難，下面就讓我們來看下他是如何設置的一、什麼是宏表函數以及FILES函數
  • excel中2000行數據以行排序,你需要多長時間
    Hello，大家好，今天跟大家分享下我們如何使用錄製宏批量的按照行來對數據排序，這個之前發過視頻，但是很多粉絲表示沒看懂，今天就來詳細的介紹下這種方法一、宏是什麼宏本質上就是我們所說的vba代碼，錄製宏就是將我們的操作記錄下來，將其編譯為vba代碼，當我下次需要重複操作的時候只需要運行宏
  • Excel VBA 不可一世的宏中,基本數據構成你知道多少
    在上一期我們說到VBA的基本組織結構，那麼我們今天就來說說組成這些VBA代碼的磚瓦是由哪幾部分構成的，也就是說vba的處理數據的磚瓦。2.變量，這一次是很好理解，excel在處理的時候，他是可以按照一定規則去變化的。其實變量我們是看不到他變化，他的變化在計算機的內存之中。另外需要說明的一點是，變量的數據種類有很多。如下圖：我們在使用變量的時候，可以給變量定義一下他的數據類型，這樣的話我們程序執行的速度會快很多的，效率也會提升很多。
  • Excel vba 利用定義名稱實現用戶登錄功能
    文/江覓易見在vba 中實現用戶登錄，並對登錄用戶進行身份識別，然後對用戶進行分組管理，此類功能值得進一步思考。這裡介紹一種方法，利用Excel中的名稱管理來進行身份識別，起到用戶登錄次數以及增減用戶的功能。
  • excel中重複製作圖表如何錄製宏
    excel中重複製作圖表如何錄製宏在工作中執行多次重複操作，可以使用宏功能簡化操作，提高工作效率，宏是一系列存儲在VBA模塊中的命令和函數，一旦編輯就可以隨時調用減少執行重複操作的時間，現在簡單的用圖文描述一下錄製宏操作流程這邊給大家以製作圖表設置表樣式進行錄製宏的具體操作方法
  • excel的宏怎麼調用,怎麼運用?
    因為我們一般也不用，所以在excel中宏這個功能一般也不顯示出來，我們先把它給顯示出來，打開一個excel，點擊左上角的文件，在文件中找到選項，我們點擊選項，找到自定義功能區，並且找到開發工具把它添加到主選項卡中，把開發工具前面的對勾選項。
  • 隨心所欲讓excel做任何事——編寫宏
    【excel是什麼？】用excel表格來做計算，現在可以說是非常普遍了，其功能也是十分強大的。但是在我們實際應用的時候，不免會出現現有功能無法滿足我們的情況，比如下面的例子。在目前excel中則無法實現，幸運的是該產品針對特定需求提供了宏任務來解決【操作步驟】首先我們找到查看宏的地方：【視圖】-> 【宏】->【查看宏】輸入名稱後，點擊【創建】進入代碼界面開始編寫代碼，我們的代碼寫在
  • Excel技能樹系列10:拿來就用的宏代碼17條
    這裡有一部分拿來就用的宏代碼，可以先觀看目錄看看是否有需要的。文件將工作簿保存為一個PDF文件保護所有帶公式的單元格給選定區域交替高亮顯示，增加可讀性高亮顯示所有帶評論的單元格在選定區域內高亮顯示所有的空單元格重新調整所有的圖表為同樣大小給當前工作簿創建備份一次性關掉所有打開的工作簿將選中區域保存為PDF文件刪除選中單元格區域的空格字符將選定區域內的空白單元格以0填充合併多個工作表合併多個Excel文件對於Excel來說，只需要按住Alt + F11就可以打開宏編輯器
  • 用EXCEL製作一個簡單的五子棋遊戲
    今天，我們教愛學習的小朋友用excel製作一款非常簡單的雙人五子棋遊戲。其中涉及到的知識有：excel基礎操作vba基礎知識首先看看最終效果圖：首先我們製作棋盤因為excel的行列單位不一致，我們就用眼睛來調整行和列的大小，讓單元格差不多為正方形即可。然後我們為棋盤附上看起來不是很刺眼的黃色做為底色。最終我們就獲得了一個正式的棋盤了。
  • 工作中為什麼有必要學點VBA
    從office97開始，VBA作為MS的標準內置配置，彌補了excel內在的基本函數不足以支持的複雜計算，提供了一種相對輕量級的、所見即所得的解決方案，濤哥在21世紀初因內部工作需要（統計代碼行數記工作量）第一次接觸了VBA，並在以後的工作中多次出色的完成相關任務，甚至給濤嫂編的幾個小程序大大提升了她們的工作效率，還受到了時任行長的讚許，現在想來還歷歷在目。
  • Excel表格的宏操作教程
    諮詢學歷提升請加小編個人微信號：jzs0598 ❸ 長按右側二維碼，關注CAD吧excel表格的宏操作教程：　　宏操作步驟1：為了深入淺出，小編操作了一個最簡單的實操過程，希望大家不要。　　宏操作步驟2：其實，不用怕，我們要用到「宏」來幫助我們。首先，我們要錄製新的宏，簡單而言就是把我們要重複操作的步驟錄製起來哦! 方法是：點擊工具——宏——錄製新的宏。點擊後，你就開始進入宏的錄製了，把你步驟一步步錄起來!
  • 學習Excel VBA有什麼用處?
    excel有一個宏的概念，就是用戶使用宏錄製器錄下的一組操作。通過宏，實現把重複的工作簡單化。那麼我們怎麼錄製宏呢？錄製宏需要哪些步驟呢？我們一一解釋說明。第一步：錄製前的準備1、選中A1單元格，即工資表表頭所在行的第一個單元格2、依次執行工具--宏--錄製新宏菜單命令，打開錄製新宏對話框。
  • 你知道excel的宏可以事半功倍提升工作效率嗎
    工作中經常使用excel的同學會發現，如果說每天對對同樣的工作內容，表格的處理方法也是一樣的。有可能你還是會繼續重複「昨天的故事」，一次又一次的重複操作。這樣難免會有些許枯燥，這時候，如果你本身會excel的宏。
  • 「Excel技巧」用VBA法給單元格批量插入圖片批註
    上一篇文章跟大家介紹的是excel單元格如何手動插入圖片批註。但是在表格產品比較多，想要全部插入圖片批註的情況下，手動一個個插入的方法就不可取了。我們說了，只要思想不滑坡 辦法總比困難多。這時，我們就要考慮更高效的辦法，比如：批量插入圖片批註。關鍵如何實現？
  • Excel實現信息管理系統之數據錄入界面設計步驟詳解
    本身excel就是一款功能強大的數據信息管理和分析軟體，我們如果在它的基礎上在設計信息管理的功能感覺有點多此一舉。但是大家仔細想想，我們在excel錄入數據時，很多時候都是在單元格中直接輸入，亦或者使用excel記錄單錄入數據，然後再進行數據處理，這種方式對於大眾來講非常簡單快捷。但是如果我們處理的數據量大，錄入數據時有若干列，或者多人錄入時，這樣直接輸入真的會很便捷嗎？