Excel 2016 - VBA - Set Object Error 91
Clash Royale CLAN TAG#URR8PPP
Excel 2016 - VBA - Set Object Error 91
My code below is inserted into the "ThisWorkbook" Microsoft Excel Object. Why is it when the Workbook_Open sub ends my SET objects are changed to nothing?
'Declare WorkBook variables
Public wbI As Workbook, wbO As Workbook
Public wsData As Worksheet, wsMain As Worksheet, wsPForma As Worksheet
Public Sub Workbook_Open()
Application.Wait (10) 'Wait 0.1 seconds
Set wbI = ThisWorkbook
Set wsData = wbI.Sheets("Customs Details Sheet Data")
Set wsMain = wbI.Sheets("Customs Details Sheet")
Set wsPForma = wbI.Sheets("Manufacturer Pro-Forma")
End Sub
dVBnum
Try adding some “Msgbox Not wsData Is Nothing” to check for wsData having been properly set
– DisplayName
Aug 13 at 7:23
The only way this code could give error 91 is when
dVBnum is an object type variable that is Nothing. All other reasons would get you different errors.– GSerg
Aug 13 at 7:37
dVBnum
Nothing
@GSerg Not exactly. If
wsData is set to Nothing before the dVBnum line it will end with an error 91 too.– Abraxas
Aug 13 at 7:56
wsData
Nothing
dVBnum
What kind of variable is
DVBNum?– Foxfire And Burns And Burns
Aug 13 at 8:46
DVBNum
2 Answers
2
dVBnum is a Range. You never initialize it so it's Nothing.
dVBnum
Range
Nothing
The line
dVBnum = wsData.Cells(1, 5)
actually means "set dVBnum's .Value to be equal to wsData.Cells(1, 5)'s .Value".
Because dVBnum is Nothing, it does not have a Value.
dVBnum
.Value
wsData.Cells(1, 5)
.Value
dVBnum
Nothing
Value
If you meant to store a reference to the range wsData.Cells(1, 5) itself in a variable, you should have used
wsData.Cells(1, 5)
Set dVBnum = wsData.Cells(1, 5)
If you meant to store the value stored in the cell into a variable, you should have not declared the variable as Range:
Range
Dim dVBnum As Variant
dVBnum = wsData.Cells(1, 5)
Ok. My plan is to use dVBnum as a reference to the range but I thought it was the "wsData" part of the code causing me the problem. I've added the word "Set" in front of my dVBnum as suggested above and it is now working. Thank you
– Chris
Aug 13 at 10:17
I'm getting the same error come up in another SUB later in the code referencing dVBnum
– Chris
Aug 13 at 10:24
@Chris Put
Option Explicit on top of your code.– GSerg
Aug 13 at 10:30
Option Explicit
Adding it in now. It appears my SETS are changing to nothing after the Workbook_Open sub ends
– Chris
Aug 13 at 12:20
@Chris If your variables are local, most certainly.
– GSerg
Aug 13 at 13:13
If you wanna check that your variable has been properly set, you can use the VarTypefunction to test it.
VarType
It will return a value that indicates the type of your variable based on this table.
In your case, you would type :
MsgBox(VarType(wsData))
and if your has been set properly, you'll get a 9 which represents an object which is your worksheet.
If you're trying to get the location of the cell with dVBnum = wsData.Cells(1, 5) 'VB Number cell location on data sheet, then you're doing it wrong.
dVBnum = wsData.Cells(1, 5) 'VB Number cell location on data sheet
To get the address of a cell you have two options:
Get the address property of the cell
dVBnum = wsData.Cells(1, 5).Address
You'll get "$E$1" for dVBnum
"$E$1"
dVBnum
Get the location with row and column
dVBnum = wsData.Range("E1").row & ", " & Range("E1").Column
You'll get "1, 1" for dVBnum
"1, 1"
dVBnum
It's ok. I'm later using dVBnum.Value to take the value of that cell and assign it to another variable.
– Chris
Aug 13 at 9:32
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
What are you trying to achieve? What kind of data do you want to bind to your
dVBnumvariable? The content of the cell or its location?– Jérémy Gamba
Aug 13 at 7:20