时间戳

首页 > 解决方案 > 如何解决错误对象变量或未设置块变量？

excel - 如何解决错误对象变量或未设置块变量？

问题描述

代码如下。

运行时错误 91 对象变量或未设置块变量。

它有时会完美运行，但有时会显示给定的错误。

Option Explict 

Public wb As Workbook
Private rowMsg As Integer

Public Sub showMsg(pMsg As String, Optional pColorError As Boolean,
     Optional pColorSuccessful As Boolean)

    With wb.sheets("Setup")
        .Rows(rowMsg).HorizontalAlignment = xlLeft
        .Rows(rowMsg).VerticalAlignment = xlBottom
        .Rows(rowMsg).WrapText = True
        .Rows(rowMsg).Orientation = 0
        .Rows(rowMsg).AddIndent = False
        .Rows(rowMsg).IndentLevel = 0
        .Rows(rowMsg).ShrinkToFit = False
        .Rows(rowMsg).ReadingOrder = xlContext
        .Rows(rowMsg).MergeCells = True

        .Cells(rowMsg, 1).Value = Now & Space(3) & pMsg

        If pColorSuccessful Then
            .Cells(rowMsg, 1).Interior.ColorIndex = 43
        End If
        If pColorError Then
            .Cells(rowMsg, 1).Interior.ColorIndex = 3
        End If
    End With

    rowMsg = rowMsg + 1
End Sub

标签： excelvba

解决方案

问题可能是您没有初始化全局变量：

Public wb As Workbook
Private rowMsg As Long '<-- needs to be long not integer.

如果您showMsg第一次运行wbisNothing并且rowMsgis 0（这不是有效的行号，因为它以 row = 1 开头）。

因此，在您对变量运行任何代码之前，请测试您的变量是否已初始化。

Public Sub showMsg(pMsg As String, Optional pColorError As Boolean, Optional pColorSuccessful As Boolean)

    'test if global variables were initialized, if not do it.
    If wb Is Nothing Then 
        Set wb = ThisWorkbook
    End If

    If rowMsg = 0 Then rowMsg = 1

    'your code here …
End Sub

推荐阅读