VBA运行时错误'91':对象变量或未设置块变量 [英] VBA run-time error ‘91’: Object variable or With block variable not set
问题描述
我已经搜索了这个最优秀的论坛和其他几个人,并且已经能够解决我的大部分问题,但现在出现了一个新的错误,我不知道为什么。
运行时错误'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屋!