CODEWITHSUNDEEP
vbaexcel
R. Bourgeon
R. Bourgeon

Reputation: 1013

How to avoid zeros being treated as Empty in VBA

Context

I have some VBA code that allows me to insert a whole Excel table into a postgresql database.

The idea of the code is the following (the first row of the Excel table contains the column names, which are the same as the postgresql table's column names) : for each row, I execute a PostgreSQL INSERT query from VBA :

[code to connect to the postgresql database using the ADODB driver]

For r = 2 to row_max
       query = transform_row_into_list(r,1,col_max)
       connexion.execute(query)
Next r

The function transform_row_into_list(r,c1,c2) will take the values on the row r of the Excel table, located within the columns c1 and c2, and transform them into a proper PostgreSQL INSERT query, i.e. something like :

INSERT INTO mytable VALUES (val1, val2, ... ) ;

Question

The definition of my function transform_row_into_list uses another function I made, called format_sql, which aims to format correctly each value of the list so that it can properly be inserted inside a SQL INSERT query. In particular, the blank values are transformed into "NULL" so it can be inserted as a NULL in the PostgreSQL table.

If x = Empty Then
    format_sql = "NULL"
Exit Function
End If

(in this context, the variable x is a Variant).

The problem is, I noticed today that if a cells contains the value 0, the test x = Empty will return True.

You can reproduce the problem by :

  1. Create a blank Excel file and put 0 in cell A1, and leave cell A2 empty
  2. Create a sub with the following code :
test1 = (Range("A1") = Empty)

test2 = (Range("A2") = Empty)

test3 = (Range("A1") = 0)

test4 = (Range("A2") = 0)

Debug.Print test1

Debug.Print test2

Debug.Print test3

Debug.Print test4

(sorry to have misused the quoting feature, but SO wouldn't let met format this as code)

All these tests will return True while you would expect only test2 and test3 to be True.

Therefore, how to have a logical test that can return True for the values being actually empty and not for the cells containing the value 0?

Upvotes: 4

Views: 2377

Answers (2)

Storax
Storax

Reputation: 12167

In this case I would probably use the len function 

If len(x) = 0 then
    format_sql = "NULL"
end if

Upvotes: 4

Vityata
Vityata

Reputation: 43565

Write test1 like this:

test1 = (IsEmpty(Range("A1")))

It would be ok. Thus, you may change the If IsEmpty(x) Then

See this for Empty in VBA - What is the difference between =Empty and IsEmpty() in VBA (Excel)?

Upvotes: 2

Related Questions