excel - VBA 加载项问题

Question

我在 Excel 2010 中遇到了 VBA 加载项问题。

我创建了一些代码来解析我的 excel 数据。我把它做成了一个插件。

但是，当我加载加载项并运行时，会发生错误。

错误消息说:runtime error 91 object variable or With block variable not set
错误指向rowSize = ActiveSheet.Rows.Count .

有谁知道如何解决这个错误？

这是代码，

Private Sub Workbook_Open()

Dim counter As Long
Dim rowSize As Long
Dim userId As String
Dim answers As String
Dim vals As String

Dim i As Integer

rowSize = ActiveSheet.Rows.Count
counter = 1



'Create Column

ActiveSheet.Cells(1, 7).Value = "Country"
ActiveSheet.Cells(1, 8).Value = "State"
ActiveSheet.Cells(1, 9).Value = "Age"

ActiveSheet.Cells(1, 7).Font.Bold = True
ActiveSheet.Cells(1, 8).Font.Bold = True
ActiveSheet.Cells(1, 9).Font.Bold = True

ActiveSheet.Cells(1, 7).HorizontalAlignment = xlCenter
ActiveSheet.Cells(1, 8).HorizontalAlignment = xlCenter
ActiveSheet.Cells(1, 9).HorizontalAlignment = xlCenter

ActiveSheet.Cells(1, 7).Borders().LineStyle = xlContinuous
ActiveSheet.Cells(1, 8).Borders().LineStyle = xlContinuous
ActiveSheet.Cells(1, 9).Borders().LineStyle = xlContinuous

'Set Value
Do While counter < rowSize

If ActiveSheet.Cells(counter, 1).Value = Null Then Exit Do
If ActiveSheet.Cells(counter, 4).Value = "3" Then

    userId = ActiveSheet.Cells(counter, 2).Value
    vals = ActiveSheet.Cells(counter, 6).Value
    'MsgBox (vals)

    temp = Split(vals, ",")
    i = 0

    Do While i < 10
        targetCell = counter + i
        If ActiveSheet.Cells(targetCell, 2).Value = userId Then
           ActiveSheet.Cells(targetCell, 7).Value = temp(0)
           ActiveSheet.Cells(targetCell, 8).Value = temp(1)
           ActiveSheet.Cells(targetCell, 9).Value = temp(2)

           ActiveSheet.Cells(targetCell, 7).HorizontalAlignment = xlCenter
           ActiveSheet.Cells(targetCell, 8).HorizontalAlignment = xlCenter
           ActiveSheet.Cells(targetCell, 9).HorizontalAlignment = xlCenter

           ActiveSheet.Cells(targetCell, 7).Borders().LineStyle = xlContinuous
           ActiveSheet.Cells(targetCell, 8).Borders().LineStyle = xlContinuous
           ActiveSheet.Cells(targetCell, 9).Borders().LineStyle = xlContinuous
        End If
        i = i + 1
    Loop
    temp = Null
   'parsing_question_1(vals, userId)
End If

counter = counter + 1
Loop
End Sub

谢谢!

Answer 1

加载项只是代码 - 没有用户界面。由于没有用户界面，因此从技术上讲，作为 ActiveSheet 的插件文件中没有工作表。加载项中实际上有工作表，但它们都不能是“事件的”。

如果要在加载项中使用工作表，则需要以不同的方式引用这些工作表。例如，如果您想使用加载项中的第一张工作表，您可以使用如下代码

Me.Sheets(1).Rows.Count

Me 关键字指的是您所在的类。在本例中，您位于插件的 ThisWorkbook 模块中，因此 Me 指的是作为插件的 Workbook 对象。

如果要处理不在加载项中的特定工作表，可以在打开的事件中打开该工作簿并引用该工作表。如

Dim sh As Worksheet

Set sh = Workbooks.Open("C:\MyPath\MyBook.xls").Sheets(1)

rowSize = sh.Rows.Count

最后，如果您想在任何工作簿打开时运行代码，您必须创建一个自定义类模块来监听应用程序级事件。首先创建一个自定义类模块调用 CAppEvents。在那个自定义类模块中，把这段代码

Private WithEvents mApp As Application

Public Property Set App(oApp As Application)
    Set mApp = oApp
End Property

Public Property Get App() As Application
    Set App = mApp
End Property

Private Sub mApp_WorkbookOpen(ByVal wb As Workbook)
    FormatWorkbook wb
End Sub

'or to limit which workbook it runs on - in this example based on the path
'but you may use some other condition like the existence of a particular
'custom document property
Private Sub mApp_WorkbookOpen(ByVal wb As Workbook)
    If wb.Path = "\\Server1\mypath" Then
        FormatWorkbook wb
    End If
End Sub

在标准模块中，放置此代码

Public clsAppEvents As CAppEvents

Sub Auto_Open()

    Set clsAppEvents = New CAppEvents
    Set clsAppEvents.App = Application

End Sub

Sub FormatWorkbook(wb As Workbook)

    Dim sh As Worksheet

    Set sh = wb.Sheets(1)

    'do stuff here

End Sub