實現工作表按名稱排序
大家好,我們今日講解“VBA資訊獲取與處理”教程中第十三個專題“VBA如何讓Excel工作表按指定的順序排列”的第一節“實現工作表按名稱排序”,這個專題是非常實用的知識點,希望大家能掌握利用。
在資訊獲取後,我們往往把資訊放在工作表內,有的時候我們希望工作表是按照我們給出的順序排序的,這樣我們在跟蹤這些資訊,查詢這些資訊的時候可以快速、方便。
但我們知道,Excel不提供對工作簿中的工作表排序或排序的命令。為了達到我們的目的,我們可以藉助VBA來完成。這個專題中我們將討論這個問題,並提供給讀者很多實際可以利用的函式,讀者可以利用這些函式對工作表進行排序操作。這個專題中提供的方法有:按照工作表的名稱排序,按指定的名稱順序排序,按照顏色排序等等。透過本專題的學習,讓大家實現一種排序的自由。
第一節 實現工作表按名稱排序
大家好,我們先從按工作表名稱排序講起,先看一下下面的截圖:
這個截圖中,第一個工作表我稱之為功能工作表頁,其餘的工作表從第2個到第7個是資料儲存的工作表,順序是沒有規律的,我要讓各個工作表自動地進行排序處理,程式碼該怎麼寫呢?
1 工作表按名稱排序的實現思路
我們要藉助於函式來實現我們的目的,這個函式就是完成排序的工作。談到排序,肯定會涉及到升序和降序,另外還有數字和文字的比較方案,對於錯誤的處理。對於工作表的排序還涉及到工作表的移動,按名稱的順序進行工作表的移動即可。
1)升序和降序就是大小比較的方案,注意在實現的時候處理的順序
2)數字和文字的比較方案,如果是文字的比較,要設定為vbTextCompare的比較方式。
3)工作表的排序還涉及到工作表的移動,就是工作表的。Move before:語句了,根據比較的結果移動工作表的位置。
4)我們進行數字的比較,就要求工作表的名稱是數字,判斷數字用到IsNumeric函式。
5)對於參與排序的工作表,我們要作為引數進行錄入,當然要進行必要的判斷,不能超過所有工作表的範圍。
思路有了,我們看看實現的過程。
2 工作表按名稱排序的程式碼實現過程
先給出我的程式碼:
Public Function SortWorksheetsByName(ByValFirstToSort As Long, _
ByValLastToSort As Long, _
ByRefErrorText As String, _
Optional ByValSortDescending As Boolean = False, _
Optional ByVal Numeric As Boolean = False) As Boolean
‘FirstToSort:需要排序的第一個工作表
’FirstToSort:需要排序的最後一個工作表
‘FirstToSort與LastToSort如果都為0,那麼表示所有的工作表都要參與排序
’ErrorText為接收可能發生的任何錯誤的文字描述
‘SortDescending升序還是降序,預設是升序
’Numeric 按數字還是文字排序,如果按數字則要求工作表名需是數字
Dim WB As Workbook
Dim B As Boolean
‘返回指定工作表物件的父物件,即工作薄
Set WB = Worksheets。Parent
ErrorText = vbNullString
’判斷工作簿中的工作表是否處於保護狀態,如果是則該屬性值為 True。
If WB。ProtectStructure = True Then
ErrorText = “工作薄處於保護狀態,無法排序”
SortWorksheetsByName = False
End If
If (FirstToSort = 0) And (LastToSort = 0) Then
FirstToSort = 1
LastToSort = WB。Worksheets。Count
Else
B = TestFirstLastSort(FirstToSort, LastToSort, ErrorText)
If B = False Then
SortWorksheetsByName = False
MsgBoxErrorText
Exit Function
End If
End If
‘對工作表名稱進行判斷,如果有不是數字的則退出排序
If Numeric = True Then
For N = FirstToSort To LastToSort
If IsNumeric(WB。Worksheets(N)。Name) = False Then
ErrorText = “有名稱不為數字的工作表!”
SortWorksheetsByName = False
MsgBoxErrorText
Exit Function
End If
Next N
End If
’排序
For M = FirstToSort To LastToSort
For N = M ToLastToSort
If SortDescending = True Then
If Numeric = False Then
If StrComp(WB。Worksheets(N)。Name, WB。Worksheets(M)。Name, vbTextCompare) > 0 Then
WB。Worksheets(N)。Move before:=WB。Worksheets(M)
End If
Else
If CLng(WB。Worksheets(N)。Name) >CLng(WB。Worksheets(M)。Name) Then
WB。Worksheets(N)。Move before:=WB。Worksheets(M)
End If
End If
Else
If Numeric = False Then
If StrComp(WB。Worksheets(N)。Name, WB。Worksheets(M)。Name, vbTextCompare) < 0 Then
WB。Worksheets(N)。Move before:=WB。Worksheets(M)
End If
Else
If CLng(WB。Worksheets(N)。Name) WB。Worksheets(N)。Move before:=WB。Worksheets(M) End If End If End If Next Next SortWorksheetsByName = True End Function Private Function TestFirstLastSort(FirstToSort As Long, LastToSort As Long, _ ByRefErrorText As String) As Boolean ‘對於輸入引數的驗證,要求FirstToSort要小於FirstToSort,同時大於0 ’LastToSort要小於或等於最大的工作表數 ErrorText = vbNullString If FirstToSort<= 0 Then TestFirstLastSort = False ErrorText = “起始工作不能是負數” MsgBoxErrorText Exit Function End If If FirstToSort>Worksheets。Count Then TestFirstLastSort = False ErrorText = “起始工作不能大於總的工作表數” MsgBoxErrorText Exit Function End If If LastToSort<= 0 Then TestFirstLastSort = False ErrorText = “結尾的工作數不能是負數” MsgBoxErrorText Exit Function End If If LastToSort>Worksheets。Count Then TestFirstLastSort = False ErrorText = “結尾的工作數不能大於總工作表數” MsgBoxErrorText Exit Function End If If FirstToSort>LastToSort Then TestFirstLastSort = False ErrorText = “第一個工作表數要小於結尾的工作表數” MsgBoxErrorText Exit Function End If TestFirstLastSort = True End Function Sub mynz() Sheets(“SHEET1”)。Select UU = SortWorksheetsByName(2, 7, “ErrorText”, “TRUE”, True) If UU = True Then MsgBox “排序完成!” Else MsgBox “排序錯誤!” End If Sheets(“SHEET1”)。Select End Sub 程式碼截圖: 程式碼的講解: 1)上述程式碼實現了按名稱進行工作表排序的過程。整個過程中利用了兩個函式,一個是SortWorksheetsByName函式,是主函式,實現排序;一個是TestFirstLastSort函式,用於驗證錄入的引數是否正確。 2)需要錄入的引數有: FirstToSort:需要排序的第一個工作表 LastToSort:需要排序的最後一個工作表 ErrorText為接收可能發生的任何錯誤的文字描述 SortDescending升序還是降序,預設是升序 Numeric 按數字還是文字排序,如果按數字則要求工作表名需是數字 3)Set WB = Worksheets。Parent‘返回指定工作表物件的父物件,即工作薄。這種方法是十分方便的,我們在後續的程式碼中還要用到工作薄的工作表個數 4)If WB。ProtectStructure = True Then’判斷工作簿中的工作表是否處於保護狀態,如果是則該屬性值為 True。 5)StrComp(WB。Worksheets(N)。Name, WB。Worksheets(M)。Name, vbTextCompare) > 0 StrComp為字串比較的函式,返回值型別Variant (Integer)。返回值是-1,0,1 6)CLng(WB。Worksheets(N)。Name) >CLng(WB。Worksheets(M)。Name) clng函式可以將字串輸出成長整型資料,用於比較。 其它的程式碼比較簡單就不再多講解了。 3 工作表按名稱排序的程式碼實現效果 我們點選執行按鈕,看下面的排序效果: 在主程式中我給出的引數是:UU = SortWorksheetsByName(2, 7, “ErrorText”, “TRUE”, “TRUE”) 也就是說要實現從第2個到第7個工作表的降序按數字比較排序。 好了,這節我們就講到這裡,下節繼續。 本節知識點回向: ① 如何實現工作表的按名稱排序? ② 實現工作表按名稱排序的各個引數的意義是什麼? 本講程式碼參考檔案:013工作表.xlsm 積木程式設計的思路內涵: 在我的系列書籍中一直在強調“搭積木”的程式設計思路,這也是學習利用VBA的主要方法,特別是職場人員,更是要採用這種方案。其主要的內涵: 1 程式碼不要自己全部的錄入 。你要做的是把積木放在合適的位置然後去 修正程式碼 ,一定要複製,從你的積木庫中去複製,然後修正程式碼,把時間利用到高效的思考上。 2 建立自己的“積木庫” 。平時在學習過程中,把自己認為有用的程式碼放在一起,多積累,在用到的時候,可以隨時拿來。你的積木庫資料越多,你做程式的思路就會越廣。 VBA的應用界定及學習教程: VBA是利用Office實現個人小型辦公自動化的有效手段(工具)。 這是我對VBA的應用界定。在取代OFFICE新的辦公軟體沒有到來之前,誰能在資料處理方面做到極致,誰就是王者。其中登峰至極的技能非VBA莫屬! 我記得20年前自己初學VBA時,那時的資料甚少,只能看原始碼自己琢磨,真的很難。20年過去了,為了不讓學習VBA的朋友重複我之前的經歷,我根據自己多年VBA實際利用經驗,推出了六部VBA專門教程: 第一套:VBA程式碼解決方案 是VBA中各個知識點的講解,教程共147講,覆蓋絕大多數的VBA知識點,提供的程式檔案更是一座不可多得的程式碼寶庫,是初學及中級人員必備教程;目前這套教程提供的版本是修訂第二版, 程式檔案透過32位和64位兩種OFFICE系統測試。 第二套:VBA資料庫解決方案 資料庫是資料處理的專業利器,教程中詳細介紹了利用ADO連線ACCDB和EXCEL的方法和例項操作,適合中級人員的學習。目前這套教程提供的是修訂第一版教程, 程式檔案透過32位和64位兩種OFFICE系統測試。 第三套:VBA陣列與字典解決方案 陣列和字典是VBA的精華,字典是VBA程式碼水平提高的有效手段,值得深入的學習,是初級及中級人員程式碼精進的手段。目前這套教程提供的版本是修訂第一版, 程式檔案透過32位和64位兩種OFFICE系統測試。 第四套:VBA程式碼解決方案之影片 是專門面向初學者的影片講解,可以快速入門,更快的掌握這門技能。這套教程是第一套教程(修訂一版)的影片講解,聽母音更易接受。這套教程還會額外提供透過32位和64位兩種OFFICE系統測試的程式檔案。 第五套:VBA中類的解讀和利用 這 是一部高階教程,講解類的虛無與肉身的度化,類的利用雖然較少,但仔細的學習可以促進自己VBA理論的提高。這套教程的領會主要是讀者的領悟了,領悟一種佛學的哲理。 第六套教程:《VBA資訊獲取與處理》 , 這 是一部高階教程,涉及範圍更廣,實用性更強,面向中高階人員。教程共二十個專題,包括:跨應用程式資訊獲得、隨機資訊的利用、電子郵件的傳送、VBA網際網路資料抓取、VBA延時操作,剪下板應用、Split函式擴充套件、工作表資訊與其他應用互動,FSO物件的利用、工作表及資料夾資訊的獲取、圖形資訊的獲取以及定製工作表資訊函式等等內容。 上述教程的學習順序: 1→3→2→6→5 或者 4→3→2→6→5 。提供的程式檔案更是一座巨大的程式碼庫,供讀者使用,如需要可以WeChat: NZ9668 學習VBA是個過程,也需要經歷一種枯燥的感覺 “眾鳥高飛盡,孤雲獨去閒。相看兩不厭,只有敬亭山”。學習的過程也是修心的過程,修一個平靜的心。在程式碼的世界中,心平靜了,心情好了,身體自然而然就好。心靜則正,內心裡沒有那麼多邪知邪見,也就沒有那麼多妄想。利人就是利己。我的教程助力給正在努力的朋友。 “水善利萬物而不爭”,綿綿密密,微則無聲,巨則洶湧。學習亦如此,知道什麼是自己所需要的,不要蜷縮在一小塊自認為天堂的世界裡,待到暮年時再去做自欺欺人的言論。要努力提高自己,用一顆充滿生機的心靈,把握現在,這才是進取。越是有意義的事情,困難會越多。願力決定始終,智慧決定成敗。不管遇到什麼,都是風景。看淡紛爭,看輕得失。學習時微而無聲,利用時則巨則洶湧。“路漫漫其修遠兮,吾將上下而求索” 每一分收穫都是成長的記錄,怎無憑,正是這種執著,成就了朝霞的燦爛。最後將一闕詞送給致力於VBA學習的朋友,讓大家感受一下學習過程的枯燥與執著: 浮雲掠過,暗語無聲, 唯有清風,驚了夢中啼鶯。 望星,疏移北斗, 奈將往事雁同行。 阡陌人,昏燈明暗, 忍顧長亭。 多少VBA人, 暗夜中,悄聲尋夢,盼卻天明。 怎無憑! 分享我多年工作實際經驗的成果,隨喜這些有用的東西,給確實需要利用VBA的同路人。迴向學習利用VBA的歷歷往事,不勝感慨,謹以這些文字以紀念, 分享成果,隨喜正能量