Function data type issue

[jwise]

Hello,

I don't know how to setup the proper data definitions. I think you can see from the code what I'm trying to do. Often, one or both of the variables will be nil. If both are nil, I want to return nil. If one is nil, I want to return the other. If both are numbers, I want to return the sum. Usually the numbers are currency, but could be integer or mixed currency and integer.

The code goes to the return in the function and gets an error. I don't understand the error.

Thanks for your help.


Public Function Add_Blanks(var1 As Variant, var2 As Variant) As Variant
'
' Add numbers even if blank
'
Dim sum1 As Variant

If var1 = "" And var2 = "" Then
Add_Blanks = ""
Exit Function
End If

If var1 = "" Then
Add_Blanks = var2
Exit Function
End If

If var2 = "" Then
Add_Blanks = var1
Exit Function
End If

Add_Blanks = var1 + var2

End Function

Private Sub tadd()
'
' test use only
'

Dim abc As Long

abc = Add_Blanks("", "")

MsgBox ("Answer is ... " & abc)
End Sub

[Jomathr]

From my experience VBA seem to have a problem with assigning "" to a long/interger/byte variable type but doesn't have any proble if you replace it with vbNull

In your code you are trying to assign "" to abc once you get out of your function and you are probably getting a type mismatch error

[jwise]

Thanks for the reply. I used your idea, but get strange results. Here's my test cases:

10, 20
Ans 30

VbNull, 20
Ans is 20

10,vbNull
Ans is 11

vbNull,vbNull
Ans is 1

Obviously, only the first two are correct. I get no error indication. I changed all statements to use "vbNull" where I previously had '""'

Thanks for your help.

[Jomathr]

oh yeah forgot about that little detail, my bad

try this:



Public Function Add_Blanks(var1 As Variant, var2 As Variant) As Variant
'
' Add numbers even if blank
'
Dim sum1 As Variant


If var1 = vbNull And var2 = vbNull Then
Add_Blanks = vbNullString
Exit Function
End If


If var1 = vbNull Then
Add_Blanks = var2
Exit Function
End If


If var2 = vbNull Then
Add_Blanks = var1
Exit Function
End If


Add_Blanks = var1 + var2


End Function

along with:



'
' test use only
'
Dim x As Byte
Dim y As Byte
Dim abc As Variant


x = vbNull
y = vbNull


abc = Add_Blanks(x, y)


MsgBox ("Answer is ... " & abc)


reading over your code again made me realise that you can't use Long for the type since it will either be a string or a number (Nil, var1, var2 or sum).

The corrections are the abc variable type and Add_Blanks = vbNullString in the function

[jwise]

Thanks again for your reply.

Unfortunately, I must leave for a few hours before I can test, but I'm sure you've solved the problem. I probably should have used some type of floating point because the numbers are supposed to be currency. I lack full control over the data (which I import), and sometimes the dollar amounts are already rounded and might be interpreted as Integer.

[jwise]

I'm still struggling with this issue. I can't solve the problem by using zero as the value because it would throw off averages and medians for this data. I do not understand why case 2 would get the correct answer but case three the wrong answer. It's like vbNull has the value of '1'.

[Paul_Hossler]

Not sure I understand what you're trying to do. but Excel can be pretty smart sometimes:



Option Explicit
Public Function Add_Blanks(Optional var1 As Variant = 0#, Optional var2 As Variant = 0#) As Double
If IsNumeric(var1) And IsNumeric(var2) Then
Add_Blanks = CDbl(var1) + CDbl(var2)
ElseIf IsNumeric(var1) And Not IsNumeric(var2) Then
Add_Blanks = CDbl(var1)
ElseIf Not IsNumeric(var1) And IsNumeric(var2) Then
Add_Blanks = CDbl(var2)
Else
Add_Blanks = 0#
End If
End Function

Private Sub tadd()
Dim D As Double
Dim L As Long

MsgBox ("Answer is ... " & Add_Blanks(10, 20))
MsgBox ("Answer is ... " & Add_Blanks(, 20))
MsgBox ("Answer is ... " & Add_Blanks(10))
MsgBox ("Answer is ... " & Add_Blanks)

MsgBox ("Answer is ... " & Add_Blanks(10, vbNull))


MsgBox ("Answer is ... " & Add_Blanks("-10.222", "-20.111"))

ActiveSheet.Cells(1, 1).Value = 1234
ActiveSheet.Cells(1, 2).Value = 5678
ActiveSheet.Cells(1, 3).Value = "CAT and DOG"


MsgBox ("Answer is ... " & Add_Blanks(ActiveSheet.Cells(1, 1).Value, ActiveSheet.Cells(1, 2).Value))
MsgBox ("Answer is ... " & Add_Blanks(ActiveSheet.Cells(1, 1).Value, ActiveSheet.Cells(1, 2).Value))
MsgBox ("Answer is ... " & Add_Blanks(ActiveSheet.Cells(1, 1).Value, ActiveSheet.Cells(1, 3).Value))

End Sub




I'm guessing from the way I read your code that you want to add numbers, even if one or both is 'blank'


What is it you're trying to do in words (not VBA) please

Paul

[Paul_Hossler]

It's like vbNull has the value of '1'.


It does

Paul

[snb]

Function Add_Blanks(var1, var2)
Add_Blanks = ""
If Val(var1) + Val(var2) > 0 Then Add_Blanks = Val(var1) + Val(var2)
End Function


Private Sub tadd()
MsgBox ("Answer is ... " & Add_Blanks("", ""))
End Sub

[jwise]

Thanks for the reply, SNB. Your solution seems to work. I admit I'm unfamiliar with the Val function.

Paul, thanks for your assistance, too.

The purpose was not to add blanks at all. The purpose was to sum where one or both of the addends were blank. My 'plain vanilla' code didn't like it when one was blank... in fact it stopped. So if both were numbers, the sub worked great, but I couldn't get it to handle one "no value". Putting a zero in the row was not acceptable because it would change the averages (the difference between "count" and "counta" functions). So I tried to come up with a routine which would ignore the blanks... but I couldn't get it to work. It is important for my averages to reflect only the cases which had numbers.

I hope this makes sense!

I will look up Val.

[Paul_Hossler]

@jwise -- glad you got something works

I think there might be some assumptions that might cause an issue:

1) Negative numbers
2) Text within a parameter. Val() can be a little over enthusiastic sometimes
3) the function returns a null string which is Text if the Val()'s fail

If these are not a concern, then snb's code is the way to go



Option Explicit
Function Add_Blanks(var1, var2)
Add_Blanks = ""
If Val(var1) + Val(var2) > 0 Then Add_Blanks = Val(var1) + Val(var2)
End Function


Private Sub tadd()
Dim v As Variant

v = Add_Blanks("", "")
MsgBox VarType(v) '8 = String

MsgBox "Answer is ... " & Add_Blanks("", "")
MsgBox "Answer is ... " & Add_Blanks(1234, -5678)
MsgBox "Answer is ... " & Add_Blanks("AABBCC 10", 10)
MsgBox "Answer is ... " & Add_Blanks("10 AABBCC 44", 10)
MsgBox "Answer is ... " & Add_Blanks("", "")
End Sub




You didn't provide much detail about what the overall problem was but it might be worth considering looking at all the data as a single range and not using VBA to loop and add. For instance...



Option Explicit
Function MySpecialTotal(TheData As Range) As Double
Dim rTemp As Range
Dim dTemp As Double

On Error Resume Next
With TheData
dTemp = 0#
dTemp = Application.WorksheetFunction.Sum(.SpecialCells(xlCellTypeConstants, xlNumbers))
dTemp = dTemp + Application.WorksheetFunction.Sum(.SpecialCells(xlCellTypeFormulas, xlNumbers))
End With
On Error GoTo 0

MySpecialTotal = dTemp
End Function

Sub drv()
MsgBox MySpecialTotal(ActiveSheet.Range("A1:A30"))
End Sub






Paul

[jwise]

Thanks Paul for you interest. I apologize for a limited explanation.

Each data row contains twelve monthly expenses for a particular category which is implemented via an expense code. All January expenses are contained in the same column, and so for each respective month. For unknown reasons, the requirement is to add some of these rows, i.e. add by month the expenses for the year to create a new category.

When the data is loaded, there are many rows which are blank because there is no expense for this particular code, i.e. it's blank instead of zero. This fact is used to make the means and medians to be influenced only by the records which have data. The problem is there is no way to tell in advance if either or both of these codes are blank. If both are blank, I want the "sum" to be blank; if only one of the two, the sum is the non-blank value, and finally if both are populated, the sum of these numbers. You are correct in assuming these numbers can be negative-- whatever that means to the accountants. If negative, I need the algebraic sum.

Thanks again.

[Paul_Hossler]

I apologize for a limited explanation.


No need. I apologize for a limited understanding.

My experience is that the fastest way to get the bestest answer is to include a small, but realistic workbook with the before and after.

If you still have questions, start another thread.

Paul