Check if object is Null in Excel VBA

Question

I can't check if an imported value from SQL to Excel is Null. In debug mode I can se that the assigned value is Null, i.e. not the string "Null". Have tried is Nothing as in my code example, but also isEmpty and = "Null".

It's the line If dbList(2) Is Nothing Then in the code below that I'm having trouble with.

How can I check if the recordset is Null?

...
Dim CmdSP As New ADODB.Command
CmdSP.CommandType = adCmdText
CmdSP.CommandText = "SELECT FundName, FundId, SRL.Comment FROM XXX SRL ON XXX = XXX ORDER BY FundName ASC"

CmdSP.ActiveConnection = dbConn

Dim dbList As ADODB.Recordset
Set dbList = CmdSP.Execute

Dim row As Integer
row = 1
While Not dbList.EOF
    DataStorage.Range("dsFundsTopLeft")(row, 2) = dbList(0)
    DataStorage.Range("dsFundsTopLeft")(row, 3) = dbList(1)
    If dbList(2) Is Nothing Then
        DataStorage.Range("dsFundsTopLeft")(row, 4) = "No rating"
    Else
        DataStorage.Range("dsFundsTopLeft")(row, 4) = dbList(2)
    End If
    dbList.MoveNext
    row = row + 1
Wend
...

Before If dbList(2) Is Nothing Then put a Debug.Print TypeName(dbList(2)) - what is the output? — Robin Mackenzie
– Robin Mackenzie, Commented Jun 1, 2017 at 11:25

FunThomas · Accepted Answer · 2017-06-01 11:25:37Z

5

Try function isNull

If isNull(dbList(2)) Then

answered Jun 1, 2017 at 11:25

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Over a year ago

Feeling stupid now, but the function was not valid when i tried it before. Must have spelled it wrong!

Collectives™ on Stack Overflow

Check if object is Null in Excel VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related