PDA

View Full Version : Solved: VBA Blank cell problem



golden14
09-11-2008, 11:57 AM
Hi,

I created a function for dealing with timecode conversion and it works just fine. The problem is that I have many cells with timecode and if one of the cells is blank, I get a #VALUE! error on the calculation for that cell. I would like the calculation to be 0 if a cell is blank.

I tried using an IF statement to check if the cell is blank, but either my syntax is incorrect or maybe I'm trying to pass data types incorrectly.

Here is an example of a cell value (with general formatting for the cell):

15:06:12:27

And here is the code snippet I'm testing with:


Function test(var1 As String, var2 As String)

Dim num1 As Integer
Dim num2 As Integer
Dim total As Double

num1 = Mid(var1, 1, 2)
num2 = Mid(var2, 1, 2)

If var1 = "" Then
total = 0
Else
total = num1 + num2
End If

test = total
End Function
Any ideas? Thanks for the help!

MaximS
09-11-2008, 01:38 PM
The reason why you getting #VALUE! error is because different format of calculated cells (i.e. text and date).

You can use simple excel function:


=IF(ISERROR(A1),0,A1)

golden14
09-11-2008, 03:12 PM
Thanks for the suggestion.

I have general formatting for all the cells. I tried changing the function to this:


Function test(var1 As String, var2 As String)

Dim num1 As Integer
Dim num2 As Integer
Dim total As Double

num1 = Mid(var1, 1, 2)
num2 = Mid(var2, 1, 2)

If IsError(var1) = True Then
total = 0
Else
total = num1 + num2
End If

test = total
End Function
...but it still isn't quite working. I also tried using the num1 variable with IsError but that didn't work either. Any other thoughts?

Thanks!

mikerickson
09-11-2008, 05:36 PM
Mid(vbNullString,1,2) = vbNullString.

num1 is type Integer, so num1 = Mid(var1,1,2) causes a type mismatch when var1 = vbNullString.

One fix is to put the If test before that line.

Another fix is to use the test
If IsNumeric(Mid(Var1,1,2)) And IsNumeric(var1,1,2)) which protects against other strings (like "abcd".) (You never can trust what a user will enter into a cell.)

Another fix is:


Function test(var1 As String, var2 As String) As Double
test = Val(Mid(var1,1,2)) + Val(Mid(var2,1,2))
End Function
Which treats all strings (including vbNullString) as 0. It also treats "3:23" the same as "03:23"

golden14
09-12-2008, 09:59 AM
The last fix that you mentioned worked perfectly for what I am doing.

Thanks so much!