VBA运行时错误'91'：对象变量或未设置块变量 [英] VBA run-time error ‘91’: Object variable or With block variable not set

查看：5196
本文介绍了VBA运行时错误'91'：对象变量或未设置块变量的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在这里是全新的，虽然我对Excel非常方便，但是对于VBA而言，我的知识知之甚少。我有一个Excel程序，我们在工作中使用的工作掉在我的腿上，因为它开始抛出多个错误，我是最好的工作的人:(



我已经搜索了这个最优秀的论坛和其他几个人，并且已经能够解决我的大部分问题，但现在出现了一个新的错误，我不知道为什么。



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



启动程序时弹出

调试器中突出显示的区域是：

 如果ActiveWorkbook.Name = wbkTT然后

我正在发布以下代码部分



非常感谢任何和所有的帮助！

 'TTV2.xlam版本5/15/12 14:00 
 
 Const wbkTT As String =Time Tracker ver2.xlsm
 Const RibbonxAddin As String =TTV2.xlam
 
 
选项显式
 Private Sub Workbook_Open（）
 
 Dim strName As String 
 Dim strPath As String 
 Dim strNewShow As String 
 Dim strDate As String 
 Dim strTime As String 
 Dim strDepartment As String 
 Dim strOS As String 
 Dim strVersion As String 
 Dim dtFormat As String 
 Dim myAddIn As AddIn 
 Dim wbkDateOrder As Integer 
 Dim timedropdown As Boolean 
 
 strVersion = Application.Version 
 strOS = Application.OperatingSystem 
 dtFormat = DateFormat 
 newTTwbk = wbkTT 
 
如果strVersion< 14然后
 MsgBox（你正在运行一个过时的Excel。& Chr（10）& _ 
时间跟踪器只能运行在MS Excel 2011 for Mac）
 ActiveWorkbook.Close 
退出Sub 
 Else 
 wbkDateOrder = Application.International（xlDateOrder）
 strTime =格式（时间，hhmm）
 strDate =格式（日期， mmdd）
如果ActiveWorkbook.Name = wbkTT然后
 strName =替换（工作簿（wbkTT）.Sheets（显示信息）。范围（A2）。值，， ）
 ActiveWorkbook.Sheets（Preferences）。Range（D2：D3）。NumberFormat = dtFormat 
 ActiveWorkbook.Sheets（Data）。Range（B：B）。NumberFormat = dtFormat 
 End If'
 AddMenu 
 ActiveWorkbook.Sheets（Preferences）。visible = True 
 timedropdown = ActiveWorkbook.Sheets（Preferences）。Range（E3） 
如果timedropdown然后
 ActiveWorkbook.Sheets（Data）。Range（G：L）。Validation.Delete 
 ActiveWorkbook.Sheets（Data）。Range（G： L）。瓦尔idation.Add Type：= xlValidateList，AlertStyle：= xlValidAlertStop，Operator：= xlBetween，Formula1：== Time
 Else 
 ActiveWorkbook.Sheets（Data）。Range（G：L ）.Validation.Delete 
 End If 
如果ActiveWorkbook.Sheets（显示信息）。范围（A2）。值=TTV2然后
如果strOS像* Win * 然后
 strPath = ThisWorkbook.Path& \
 Else 
 strPath = ThisWorkbook.Path& 
 End If 
 strName = InputBox（Prompt：=Show Name please。，Title：=ENTER SHOW NAME，默认值：=Show Name Here）
 strDepartment = InputBox（Prompt：=输入您的部门：，标题：=ENTER DEPARTMENT，默认值：=Department Here）
 showcrewform 
如果strName =Show Name Here或strName = vbNullString然后
退出Sub 
 Else 
 ActiveWorkbook.Sheets（Show Info）。Range（A2）。Value = strName 
 ActiveWorkbook.Sheets（Show Info）。 Range（B2）。Value = strDepartment 
如果不是strOS像* Mac *然后
 strNewShow = strPath& strName& _& strDate& _& strTime& .xlsm
 Else 
 strNewShow = strPath& strName& _& strDate& _& strTime& .xlsm
 End If 
 ActiveWorkbook.SaveAs strNewShow，FileFormat：= xlOpenXMLWorkbookMacroEnabled 
 MsgBox（你正在处理文件：& strNewShow）
 loadwbkName 
 ResetDataSheet 
退出子
结束如果
结束如果
如果strOS像* Mac *然后
 AddMenu 
结束If 
 ResetDataSheet 
 End If 
 End Sub


解决方案

添加了一些缺少的声明和End If语句...我没有得到任何运行时错误与下面。
（编辑：添加新代码）

 'TTV2.xlam版本5/15/12 14:00 
 
 Const wbkTT As String =Time Tracker ver2.xlsm
 Const RibbonxAddin As String =TTV2.xlam
 
'ADDED DECLARATIONS 
 Dim dtFormat As String 
 Dim DateFormat 
 Dim newTTwbk 
 
选项显式
 
 Private Sub Workbook_Open（）
 Dim strName As String 
 Dim strPath As String 
 Dim strNewShow As String 
 Dim strDate As String 
 Dim strTime As String 
 Dim strDepartment As String 
 Dim strOS As String 
 Dim strVersion As String 
 Dim dtFormat As String 
 Dim myAddIn As AddIn 
 Dim wbkDateOrder As Integer 
 Dim timedropdown As Boolean 
 
'ADDED DECLARATIONS 
 Dim WB As工作簿
 
'ADDED CODE 
设置WB = ActiveWorkbook 
 
 strVersion = Application.Version 
 strOS = Application.OperatingSystem 
 dtFormat = DateFormat 
 newTTwbk = wbkTT 
 
如果strVersion< 14然后
 MsgBox（你正在运行一个过时的Excel。& Chr（10）& _ 
时间跟踪器只能运行在MS Excel 2011 for Mac）
 WB关闭'SAVE？ （TRUE / FALSE）
退出Sub 
 Else 
 wbkDateOrder = Application.International（xlDateOrder）
 strTime =格式（时间，hhmm）
 strDate = Format日期，mmdd）
如果WB.Name = wbkTT然后
 strName =替换（工作簿（wbkTT）.Sheets（显示信息）。范围（A2）。值， ）
 WB.Sheets（Preferences）。Range（D2：D3）。NumberFormat = dtFormat 
 WB.Sheets（Data）。Range（B：B）。 NumberFormat = dtFormat 
 End If 
'AddMenu 
 WB.Sheets（Preferences）。Visible = True 
 timedropdown = WB.Sheets（Preferences）。Range（E3 ）
如果timedropdown然后
 WB.Sheets（Data）。Range（G：L）。Validation.Delete 
 WB.Sheets（Data）。Range（ G：L）Validation.Add类型：= xlValidateList，AlertStyle：= xlValidAlertStop，Operator：= xlBetween，Formula1：== Time
 Else 
 WB.Sheets（Data （G：L）。Validation.Delete 
 End If 
如果WB.Sheets（显示信息）。范围（A2）。Value =TTV2然后
如果strOS像* Win *然后
 strPath = ThisWorkbook.Path& \
 Else 
 strPath = ThisWorkbook.Path& 
 End If 
 strName = InputBox（Prompt：=Show Name please。，Title：=ENTER SHOW NAME，默认值：=Show Name Here）
 strDepartment = InputBox（Prompt：=输入您的部门：，标题：=ENTER DEPARTMENT，默认值：=Department Here）
'showcrewform 
如果strName =Show Name Here或strName = vbNullString然后
退出Sub 
 Else 
 WB.Sheets（显示信息）。范围（A2）。value = strName 
 WB.Sheets（显示信息） .Range（B2）。Value = strDepartment 
如果不是strOS像* Mac *然后
 strNewShow = strPath& strName& _& strDate& _& strTime& .xlsm
 Else 
 strNewShow = strPath& strName& _& strDate& _& strTime& .xlsm
 End If 
 WB.SaveAs strNewShow，FileFormat：= xlOpenXMLWorkbookMacroEnabled 
 MsgBox（你正在处理文件：& strNewShow）
'loadwbkName 
'ResetDataSheet 
退出Sub 
如果
结束If 
如果strOS像* Mac *然后
'AddMenu 
 End If 
'ResetDataSheet 
如果
 End Sub


I'm brand new here and although I'm super handy with Excel I have minimal knowledge when it comes to VBA. I've had an Excel program that we use at work dropped in my lap because it's started throwing multiple errors and I'm the "best" person for the job :(

I've searched this most excellent forum and a couple others and have been able to solve most of my problems, but now a new error is popping up and I cannot figure out why.

Run-time error ‘91’: Object variable or With block variable not set

It pops up when I start the program.

The highlighted area in debugger is:

If ActiveWorkbook.Name = wbkTT Then

I'm posting the section of code below

Thanks a lot for any and all help!

' TTV2.xlam version 5/15/12 14:00

Const wbkTT As String = "Time Tracker ver2.xlsm"
Const RibbonxAddin As String = "TTV2.xlam"


Option Explicit
Private Sub Workbook_Open()

Dim strName As String
Dim strPath As String
Dim strNewShow As String
Dim strDate As String
Dim strTime As String
Dim strDepartment As String
Dim strOS As String
Dim strVersion As String
Dim dtFormat As String
Dim myAddIn As AddIn
Dim wbkDateOrder As Integer
Dim timedropdown As Boolean

strVersion = Application.Version
strOS = Application.OperatingSystem
dtFormat = DateFormat
newTTwbk = wbkTT

If strVersion < 14 Then
    MsgBox ("You are running an outdated version of Excel." & Chr(10) & _
        "Time Tracker will only run on MS Excel 2011 for Mac")
    ActiveWorkbook.Close
Exit Sub
Else
    wbkDateOrder = Application.International(xlDateOrder)
    strTime = Format(time, "hhmm")
    strDate = Format(Date, "mmdd")
    If ActiveWorkbook.Name = wbkTT Then
        strName = Replace(Workbooks(wbkTT).Sheets("Show Info").Range("A2").Value, " ", "")
        ActiveWorkbook.Sheets("Preferences").Range("D2:D3").NumberFormat = dtFormat
        ActiveWorkbook.Sheets("Data").Range("B:B").NumberFormat = dtFormat
    End If '
AddMenu
    ActiveWorkbook.Sheets("Preferences").visible = True
    timedropdown = ActiveWorkbook.Sheets("Preferences").Range("E3")
    If timedropdown Then
        ActiveWorkbook.Sheets("Data").Range("G:L").Validation.Delete
        ActiveWorkbook.Sheets("Data").Range("G:L").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Time"
    Else
        ActiveWorkbook.Sheets("Data").Range("G:L").Validation.Delete
    End If
   If ActiveWorkbook.Sheets("Show Info").Range("A2").Value = "TTV2" Then
        If strOS Like "*Win*" Then
            strPath = ThisWorkbook.Path & "\"
        Else
            strPath = ThisWorkbook.Path & ":"
        End If
        strName = InputBox(Prompt:="Show Name please.", Title:="ENTER SHOW NAME", Default:="Show Name Here")
        strDepartment = InputBox(Prompt:="Enter your Department:", Title:="ENTER DEPARTMENT", Default:="Department Here")
        showcrewform
      If strName = "Show Name Here" Or strName = vbNullString Then
            Exit Sub
        Else
            ActiveWorkbook.Sheets("Show Info").Range("A2").Value = strName
            ActiveWorkbook.Sheets("Show Info").Range("B2").Value = strDepartment
            If Not strOS Like "*Mac*" Then
                strNewShow = strPath & strName & "_" & strDate & "_" & strTime & ".xlsm"
            Else
                strNewShow = strPath & strName & "_" & strDate & "_" & strTime & ".xlsm"
            End If
            ActiveWorkbook.SaveAs strNewShow, FileFormat:=xlOpenXMLWorkbookMacroEnabled
            MsgBox ("You are now working on the file: " & strNewShow)
            loadwbkName
            ResetDataSheet
            Exit Sub
        End If
    End If
    If strOS Like "*Mac*" Then
        AddMenu
    End If
    ResetDataSheet
End If
End Sub

解决方案

Added some missing declarations and End If statement... I don't get any run-time error with the below. (EDIT: Added new code)

' TTV2.xlam version 5/15/12 14:00

Const wbkTT As String = "Time Tracker ver2.xlsm"
Const RibbonxAddin As String = "TTV2.xlam"

'ADDED DECLARATIONS
Dim dtFormat As String
Dim DateFormat
Dim newTTwbk

Option Explicit

Private Sub Workbook_Open()
Dim strName As String
Dim strPath As String
Dim strNewShow As String
Dim strDate As String
Dim strTime As String
Dim strDepartment As String
Dim strOS As String
Dim strVersion As String
Dim dtFormat As String
Dim myAddIn As AddIn
Dim wbkDateOrder As Integer
Dim timedropdown As Boolean

' ADDED DECLARATIONS
Dim WB As Workbook

'ADDED CODE
Set WB = ActiveWorkbook

strVersion = Application.Version
strOS = Application.OperatingSystem
dtFormat = DateFormat
newTTwbk = wbkTT

If strVersion < 14 Then
    MsgBox ("You are running an outdated version of Excel." & Chr(10) & _
        "Time Tracker will only run on MS Excel 2011 for Mac")
    WB.Close 'SAVE? (TRUE/FALSE)
    Exit Sub
Else
    wbkDateOrder = Application.International(xlDateOrder)
    strTime = Format(Time, "hhmm")
    strDate = Format(Date, "mmdd")
    If WB.Name = wbkTT Then
        strName = Replace(Workbooks(wbkTT).Sheets("Show Info").Range("A2").Value, " ", "")
        WB.Sheets("Preferences").Range("D2:D3").NumberFormat = dtFormat
        WB.Sheets("Data").Range("B:B").NumberFormat = dtFormat
    End If
    'AddMenu
    WB.Sheets("Preferences").Visible = True
    timedropdown = WB.Sheets("Preferences").Range("E3")
    If timedropdown Then
        WB.Sheets("Data").Range("G:L").Validation.Delete
        WB.Sheets("Data").Range("G:L").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Time"
    Else
        WB.Sheets("Data").Range("G:L").Validation.Delete
    End If
    If WB.Sheets("Show Info").Range("A2").Value = "TTV2" Then
        If strOS Like "*Win*" Then
            strPath = ThisWorkbook.Path & "\"
        Else
            strPath = ThisWorkbook.Path & ":"
        End If
        strName = InputBox(Prompt:="Show Name please.", Title:="ENTER SHOW NAME", Default:="Show Name Here")
        strDepartment = InputBox(Prompt:="Enter your Department:", Title:="ENTER DEPARTMENT", Default:="Department Here")
        'showcrewform
        If strName = "Show Name Here" Or strName = vbNullString Then
            Exit Sub
        Else
            WB.Sheets("Show Info").Range("A2").Value = strName
            WB.Sheets("Show Info").Range("B2").Value = strDepartment
            If Not strOS Like "*Mac*" Then
                strNewShow = strPath & strName & "_" & strDate & "_" & strTime & ".xlsm"
            Else
                strNewShow = strPath & strName & "_" & strDate & "_" & strTime & ".xlsm"
            End If
            WB.SaveAs strNewShow, FileFormat:=xlOpenXMLWorkbookMacroEnabled
            MsgBox ("You are now working on the file: " & strNewShow)
            'loadwbkName
            'ResetDataSheet
            Exit Sub
        End If
    End If
    If strOS Like "*Mac*" Then
        'AddMenu
    End If
    'ResetDataSheet
End If
End Sub

这篇关于VBA运行时错误'91'：对象变量或未设置块变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
Office最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆