Case Else problem with #VALUE appearing in cells

[Guest]

Hello Guys,

I have the following function procedure:

Function TotalCode(ByVal vCode As String, ByVal VB As Single, ByVal CAD As
Single, ByVal hours As Single, ByVal Roster As Single, ByVal ADVUren As
Single) As Single
Dim CodeNormal, CodePlus, CodeIll
CodeNormal = Array("ADV", "ANC", "CP", "EDUC", "ELF", "FAM", "FD", "JV",
"KV", "OA", "SOL", "ST", "SV", "TA", "TK", "V35", "VB", "VC", "VFD", "ZZ")
CodePlus = Array("ADV+", "ANC+", "CP+", "OA+", "SOL+", "SV+", "TK+", "V35+",
"VB+", "VC+", "Z+")
CodeIll = Array("AO", "Z")
Select Case vCode
Case cNormal
If vCode = CodeNormal Then
TotalCode = VB + ADVUren + CAD
End If
Case cPlus
If vCode = CodePlus Then
TotalCode = Rooster + ADVUren + CAD
End If
Case cIll
If vCode = CodeIll Then
TotalCode = Rooster + VB + ADVUren + uren + CAD
End If
Case Else
TotalCode = Rooster + VB + ADVUren + CAD
End Select
End Function

So I start using the function as a formula into a cell (=TotalCode(A1; B1;
C1...)

The problem seems to arise here:
If vCode = CodeNormal Then
TotalCode = VB + ADVUren + CAD
End If

When vCode is not filled in, the formula keeps giving me #VALUE! into the
cell. If I get rid of the arrays, and use the codes itself (example):

If vCode = ADV then
TotalCode = VB + ADVUren + CAD
End If

It seems to work okay.

Any suggestions to get rid of the #VALUE! in the cell when nothing is filled
in?

Thanks in advance guys!

With kind regards,

Memento

 

[Bob Phillips]

Function TotalCode(ByVal vCode As String, ByVal VB As Single, _
ByVal CAD As Single, ByVal hours As Single, _
ByVal Roster As Single, ByVal ADVUren As Single) _
As Single
Dim CodeNormal, CodePlus, CodeIll
CodeNormal = Array("ADV", "ANC", "CP", "EDUC", "ELF", "FAM", "FD", "JV", _
"KV", "OA", "SOL", "ST", "SV", "TA", "TK", "V35", _
"VB", "VC", "VFD", "ZZ")
CodePlus = Array("ADV+", "ANC+", "CP+", "OA+", "SOL+", "SV+", "TK+", _
"V35+", "VB+", "VC+", "Z+")
CodeIll = Array("AO", "Z")
Select Case vCode
Case cNormal
If Not IsError(Match(vCode, CodeNormal, 0)) Then
TotalCode = VB + ADVUren + CAD
End If
Case cPlus
If Not IsError(Match(vCode, CodePlus, 0)) Then
TotalCode = Rooster + ADVUren + CAD
End If
Case cIll
If Not IsError(Match(vCode, CodeIll, 0)) Then
TotalCode = Rooster + VB + ADVUren + uren + CAD
End If
Case Else
TotalCode = Rooster + VB + ADVUren + CAD
End Select
End Function

What is cNormal, cPlus etc.?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hello Bob,

Thanks for your solution, however: i've managed to narrow this down quite a
bit. I noticed I shouldn't use arrays for this simple purpose. So I used the
following, with success (the #VALUE! is also gone with this, since this gets
caught by the Case Else instruction).

Thanks anyway.

Function Total(ByVal vCode As String, ByVal VB As Single, ByVal CAD As
Single, ByVal hours As Single, ByVal Rooster As Single, ByVal ADVUren As
Single) As Single
Select Case vCode
Case "ADV" To "ZZ"
Totaal = VB + ADVUren + CAD
Case "ADV+" To "Z+"
Totaal = Rooster + ADVUren + CAD
Case "AO", "Z"
Totaal = Rooster + VB + ADVUren + hours + CAD
Case Else
Totaal = Rooster + VB + ADVUren + CAD
End Select
End Function