-1

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
Chris
  • 1
  • 2
  • What are you trying to achieve? What kind of data do you want to bind to your `dVBnum`variable? The content of the cell or its location? – Jérémy Gamba Aug 13 '18 at 07:20
  • Try adding some “Msgbox Not wsData Is Nothing” to check for wsData having been properly set – DisplayName Aug 13 '18 at 07: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 '18 at 07:37
  • @GSerg Not exactly. If `wsData` is set to `Nothing` before the `dVBnum` line it will end with an error 91 too. – Abraxas Aug 13 '18 at 07:56
  • What kind of variable is `DVBNum`? – Foxfire And Burns And Burns Aug 13 '18 at 08:46
  • I'm trying to SET the variables wbI, wsData, wsMain and wsPFroma but it looks like they are not being SET. dVBnum is a RANGE variable, sorry I forgot to include that bit – Chris Aug 13 '18 at 09:26
  • @Abraxas `wsData` is not set to `Nothing` in this code. The only *this code* could give error 91 is when `dVBnum` is an object type variable that is `Nothing`. Surely some other random code might cause all sorts of other errors. – GSerg Aug 13 '18 at 09:57
  • Possible duplicate of [What is a NullReferenceException, and how do I fix it?](https://stackoverflow.com/q/4660142/11683) – GSerg Aug 13 '18 at 09:58

2 Answers2

0

dVBnum is a Range. You never initialize it so it's 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.

If you meant to store a reference to the range wsData.Cells(1, 5) itself in a variable, you should have used

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:

Dim dVBnum As Variant
dVBnum = wsData.Cells(1, 5)
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • 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 '18 at 10:17
  • I'm getting the same error come up in another SUB later in the code referencing dVBnum – Chris Aug 13 '18 at 10:24
  • Adding it in now. It appears my SETS are changing to nothing after the Workbook_Open sub ends – Chris Aug 13 '18 at 12:20
  • @Chris If your variables are local, [most certainly](https://support.microsoft.com/en-gb/help/141693/scope-of-variables-in-visual-basic-for-applications). – GSerg Aug 13 '18 at 13:13
  • How do I make them global and not local? I have tried putting the delcarations into a module and tried having the SET commands in and out of this module with no luck – Chris Aug 13 '18 at 13:56
-1

If you wanna check that your variable has been properly set, you can use the VarTypefunction to test it.

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.

To get the address of a cell you have two options:

  1. Get the address property of the cell

    dVBnum = wsData.Cells(1, 5).Address

You'll get "$E$1" for dVBnum

  1. Get the location with row and column

    dVBnum = wsData.Range("E1").row & ", " & Range("E1").Column

You'll get "1, 1" for dVBnum

Jérémy Gamba
  • 70
  • 1
  • 10
  • 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 '18 at 09:32