How do I make an integer to null in Excel VBA?

Question

I am trying to detect whether an integer was set, and if not, skip most of the code in the loop (using an if statement). Here is what I have so for.

Do While hws.Cells(r, 9).Value <> ""
    On Error Resume Next
    ar = Null
    ar = aws.Range("A:A").Find(hws.Cells(r, 2).Value).Row
    If Not IsNull(ar) Then
  'work with ar'
    End If
    r = r + 1
Loop

However, when I run it, ar = Null has problems. It says "Invalid use of null".

score 9 · Answer 1 · answered Oct 26 '10 at 12:17

Variables defined as Integer cannot be Null in VBA. You will have to find another way to do what you want. eg use a different data type or use a magic number to indicate null (eg -1).

In your example code, either ar will be assigned a Long value (Range.Row is a Long) or it will throw an error.

OlimilOops · Answer 2 · 2010-10-26T12:14:46.750

1

just use a variant and isempty:

Dim i

If IsEmpty(i) Then MsgBox "IsEmpty"
i = 10

If IsEmpty(i) Then
   MsgBox "IsEmpty"
Else
   MsgBox "not Empty"
End If
i = Empty
If IsEmpty(i) Then MsgBox "IsEmpty"
'a kind of Nullable behaviour you only can get with a variant
'do you have integer?
Dim j as Integer
j = 0
If j = 0 Then MsgBox "j is 0"

edited Oct 26 '10 at 12:14

answered Oct 26 '10 at 12:05

OlimilOops

6,747
6
26
36

No thanks, I like defining everything. Can I set it to empty again. – Arlen Beiler Oct 26 '10 at 12:06
as to the variant: no matter what you write: "Dim i as Variant" is the same as "Dim i" both times you have defined a Variants – OlimilOops Oct 26 '10 at 12:16

score 0 · Accepted Answer · answered Oct 26 '10 at 12:33

Find returns a range:

Dim rf As Range
With aws.Range("A:A")
    Set rf = .Find(hws.Cells(r, 2).Value)
    If Not rf Is Nothing Then
        Debug.Print "Found : " & rf.Address
    End If
End With

-- http://msdn.microsoft.com/en-us/library/aa195730(office.11).aspx

How do I make an integer to null in Excel VBA?

3 Answers3