Step 1：ThisWorkbook
为ThisWorkbook对象的open过程编写代码即可！步骤如下：
按ALT+F11，双击左边的ThisWorkbook,在右边的“对象框”选择Workbook，“过程框“选择Open，输入MsgBox "hello"，使代码窗口显示如下：
Private Sub Workbook_Open()
MsgBox "This document is copyrighted."
On Error GoTo ErrorHandler ' Enable error-handling routine.
ThisWorkbook.VBProject.References.AddFromFile "D:\src\vbadll\vbadll.dll"
'Call clearall
Dim ref As Object
'Note: No 'Microsoft Visual Basic For Application Extensibility' needed
'Loop through each added reference and print their Name & GUID into the immediate window
For Each ref In ThisWorkbook.VBProject.References
Debug.Print "Name: ", ref.Name
Debug.Print "GUID: " & ref.GUID
Debug.Print
MsgBox ref.Name & ref.GUID
Next ref
Exit Sub
ErrorHandler:
MsgBox "References.AddFromFile err." & Err.Number & Err.Description
End Sub
Step 2：
Private Sub Workbook_BeforeClose(Cancel As Boolean) '关闭文件之前卸载引用的DLL文件
ThisWorkbook.VBProject.References.Remove ThisWorkbook.VBProject.References("vbadll")
End Subsee：https://www.excelcise.org/add-or-remove-object-library-reference-via-vba/
Loger
利用Excel VBA代码编写日志
https://zhuanlan.zhihu.com/p/99798274
'-----------------------------------------------------------
' 记录vba 操作日志 到电子表格
'https://blog.csdn.net/qq_29234631/article/details/88524171
'-----------------------------------------------------------
Dim XX
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If Sh.Name <> "日志" And Target.Rows.Count = 1 Then
With Sheets("日志")
If XX <> Target Then
ROW1 = Sheets("日志").[A100000].End(xlUp).Row + 1
Cells(ROW1, 1) = Format(Now, "yyyy-mm-dd hh:mm:ss")
Cells(ROW1, 2) = Sh.Name
Cells(ROW1, 3) = XX
Cells(ROW1, 4) = Target.Value
Cells(ROW1, 5) = Target.Address
End If
End With
End If
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
XX = Target.Value
End Sub
How to remove missing references via script in Excel VBA?
I have excel that is use by many users. Some of them do not have installed all software's and therefore missing references. I am, trying to have script that remove all references that can not be found on C: drive.
I have this code in /Microsoft Excel Objects / ThisWorkbook but is not fully working. Anyone could help me with this.
Dim theRef As Variant, i As Long
' loop through all References in VB Project
For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
Set theRef = ThisWorkbook.VBProject.References.Item(i)
' if reference is "Missing" >> remove it to avoid error message
If theRef.isbroken = True Then
ThisWorkbook.VBProject.References.Remove theRef
End If
' just for Debug
' Debug.Print theRef.Description & ";" & theRef.FullPath & ";" & theRef.isbroken & vbCr
Next i