如何創建Excel加載宏

如何創建Excel加載宏

Q：我看到過很多別人發布的Excel加載宏，能夠提供一些額外的功能，或者是一些專門的功能，極大地擴展并方便Excel的使用。Excel加載宏是怎么開發出來的呢？

A：下面以創建一個示例加載宏的完整過程來介紹如何構建自已的Excel加載宏。

第1步：確定目標

首先，我們需要確定加載宏應該執行的操作以及應具備的功能。

本示例加載項的主要目的是幫助用戶分析工作簿底層運作機制：

列出工作簿中所有的公式及相關信息

列出指定工作表中的條件格式設置信息

第2步：用戶界面設計及操作

加載宏需要一種與用戶交互的方式。這種方式可以是用戶窗體，可以是一個數據輸入和報表的工作表模板，可以是右鍵菜單命令，也可以是功能區選項卡中的命令。

本示例加載項將使用自定義功能區用戶界面。在功能區中添加一個名為“我的工具”的自定義選項卡，在其中添加功能命令，如下所示。

當用戶單擊“公式清單”后，會彈出一個消息框，列出了工作簿中所使用的全部公式、公式所在單元格及工作表。

當用戶單擊“條件格式清單”后，彈出一個用戶窗體，如下所示，在其中選擇工作表，在下方將列出該工作表中設置的條件格式信息。

第3步：編寫代碼

功能區用戶界面

新建工作簿并命名，然后關閉工作簿。在Custom UI Editor For Microsoft Office中打開該工作簿，單擊Insert——Office2 7 Custom UI Part，如所示。

在其中輸入下面的代碼：

label=”公式清單”

size=”large”

onAction=”rxSheetToolsbtnFormulaLists”

imageMso=”FunctionWizard” />

label=”條件格式清單”

size=”large”

onAction=”rxSheetToolsbtnCondFormat”

imageMso=”ConditionalFormattingsManage” />

驗證無誤后，保存代碼。單擊“Generate Callbacks”，復制產生的回調代碼，然后關閉Custom UI Editor。

VBA代碼

在Excel中打開該工作簿，按Alt+F11鍵打開VBE。

在VBE中插入標準模塊并命名為：modRibbonX，在其中粘貼剛才復制的代碼：

‘Callbackfor rxSheetToolsbtnConditionalFormat onAction

Sub rxSheetToolsbtnCondFormat(control As IRibbonControl)

End Sub

Sub rxSheetToolsbtnFormulaLists(control As IRibbonControl)

End Sub

插入一個標準模塊并命名為：modAFL，在其中輸入代碼：

‘列出所有公式

Sub AllFormulasLists()

Dim wks As Worksheet

Dim str As String

Dim rngUsedRange As Range

Dim rngFormulaRange As Range

Dim rng As Range

Dim lngLastRow As Long

str = “工作表名稱” & vbTab & “單元格地址” & vbTab & “公式文本” & vbCrLf

str = str &”———————————————————————–“& vbCrLf

‘遍歷工作簿中的工作表并獲取工作表中的公式及相關信息

For Each wks In ActiveWorkbook.Worksheets

Set rngUsedRange = wks.UsedRange

On Error Resume Next

Set rngFormulaRange =rngUsedRange.SpecialCells(xlCellTypeFormulas)

For Each rng In rngFormulaRange

str = str & wks.Name &vbTab & vbTab

str = str &Application.WorksheetFunction.Substitute(rng.Address, “$”,””) & vbTab & vbTab

str = str & Mid(rng.Formula, 2,(Len(rng.Formula))) & vbCrLf

Next rng

Set rngFormulaRange = Nothing

Set rngUsedRange = Nothing

Next wks

MsgBox str, , “本工作簿中的所有公式及對應的單元格地址”

End Sub

插入一個用戶窗體，在其中放置兩個標簽控件、一個組合框、一個列表框和一個命令按鈕，如下所示。

為用戶窗體編寫的代碼如下：

Private Sub UserForm_Initialize()

Dim wks As Worksheet

For Each wks In Sheets

cmbSheet.AddItem wks.Name

Next wks

End Sub

Private Sub cmdOK_Click()

lblCaption.Caption = “”

lstCondFormats.Clear

ListConditionalFormatting (cmbSheet.Text)

End Sub

Sub ListConditionalFormatting(wksName As String)

Dim cf As Variant

Dim rng As Range

Dim rngAll As Range

Dim colConFormat As Collection

Dim i As Long, j As Long

Dim var As Variant

Set colConFormat = New Collection

On Error Resume Next

Set rngAll = Worksheets(wksName).Cells.SpecialCells(xlCellTypeAllFormatConditions)

On Error GoTo

If rngAll Is Nothing Then

lblCaption.Caption = “工作表” & wksName & “中沒有設置條件格式.”

Exit Sub

End If

lblCaption.Caption = “工作表” & wksName & “中設置的條件格式如下:”

For Each rng In rngAll

For i = 1 To rng.FormatConditions.Count

With rng.FormatConditions

On Error Resume Next

colConFormat.Add .Item(i),FCSignature(.Item(i))

On Error GoTo

End With

Next i

Next rng

ReDim var(1 To colConFormat.Count + 1, 1 To5)

var(1, 1) = “類型”

var(1, 2) = “單元格”

var(1, 3) = “如果為真則停止”

var(1, 4) = “公式1”

var(1, 5) = “公式2”

For i = 1 To colConFormat.Count

Set cf = colConFormat.Item(i)

var(i + 1, 1) =FCTypeFromIndex(cf.Type)

var(i + 1, 2) = cf.AppliesTo.Address

var(i + 1, 3) = cf.StopIfTrue

On Error Resume Next

var(i + 1, 4) = “‘” &cf.Formula1

var(i + 1, 5) = “‘” &cf.Formula2

On Error GoTo

Next i

lstCondFormats.ColumnCount = 5

lstCondFormats.List = var

End Sub

Function FCSignature(ByRef cf As Variant) As String

Dim strResult(1 To 3) As String

strResult(1) = cf.AppliesTo.Address

strResult(2) = FCTypeFromIndex(cf.Type)

On Error Resume Next

strResult(3) = cf.Formula1

FCSignature = Join(strResult, vbNullString)

End Function

Function FCTypeFromIndex(lngIndex As Long) As String

Select Case lngIndex

Case 1: FCTypeFromIndex = “單元格值”

Case 2: FCTypeFromIndex = “表達式”

Case 3: FCTypeFromIndex = “色階”

Case 4: FCTypeFromIndex = “數據條”

Case 5: FCTypeFromIndex = “前1 ?”

Case 6: FCTypeFromIndex = “圖標集”

Case 8: FCTypeFromIndex = “唯一值”

Case 9: FCTypeFromIndex = “文本”

Case 1 : FCTypeFromIndex = “空值”

Case 11: FCTypeFromIndex = “時間段”

Case 12: FCTypeFromIndex = “高于平均值”

Case 14: FCTypeFromIndex = “非空值”

Case 16: FCTypeFromIndex = “錯誤”

Case 17: FCTypeFromIndex = “無錯誤”

Case Else: FCTypeFromIndex = “未知”

End Select

End Function

然后，回到modRibbonX模塊，完善代碼如下：

‘Callback for rxSheetToolsbtnConditionalFormat onAction

Sub rxSheetToolsbtnCondFormat(control As IRibbonControl)

frmCF.Show

End Sub

Sub rxSheetToolsbtnFormulaLists(control As IRibbonControl)

AllFormulasLists

End Sub

至此，本示例加載宏的編碼工作完成。

第4步：創建加載宏

創建加載宏很簡單，單擊“Office按鈕——另存為”（Excel 2 7）或者“文件——另存為”（Excel 2 1 ），在“保存類型”中選擇“Excel加載宏(*.xlam)”，如所示。

此時，Excel會自動導航到默認的加載宏文件夾，如所示。也可以選擇存在自已指定的其他文件夾中。

單擊“保存”，創建加載宏。

第5步：安裝加載宏

單擊功能區“開發工具——加載項”，打開如下所示的對話框。如果“可用加載宏”列表中沒有我們的示例加載宏，則可以單擊右側的“瀏覽”按鈕，導航到加載宏所在的文件夾并選擇加載宏文件，添加到列表中。

如所示，選取示例加載宏前面的復選框，單擊“確定”，完成加載宏安裝。此時，Excel工作簿中將會新增一個“我的工具”選項卡，如本文開頭的所示。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。