Custom Function Returning #VALUE!

[ExperTeece]

I have created a custom function do perform concatenation of 16 cells that may not all have values in them. The first cell is required so I pass its value seperately. The remainder I pass as a range. The function works fine during testing but then once I save the file and reopen it the function ends up with the #VALUE! error. I have somewhat resolved the issue by putting a default value in the first cell rather than leaving it blank but would really like to figure out why the function is not working. Thanks in advance for any answers you may have. Below is the code:

Function DescBuild(Optional Class As String, Optional AttRng As Range) As String
Dim FinalString As String
Dim attCnt As Integer
 
DescBuild = ""
FinalString = ""
'If Class = "" Then
 '   GoTo MyExit
'End If
FinalString = Class
For attCnt = 1 To 15
    If Not AttRng(attCnt).Value = "" Then
        FinalString = FinalString & ", " & AttRng(attCnt).Value
    End If
Next
MyExit:
DescBuild = FinalString
End Function

 

[JackDanIce]

It may be because Class is already a reserved VBA word so you should use something different as your variable name, e.g. iClass?

 

[ExperTeece]

It may be because Class is already a reserved VBA word so you should use something different as your variable name, e.g. iClass?

Thanks for the feedback but changing the name to InvClass did not resolve the issue.

 

[mikerickson]

What is the code for AttRng ?

if it is using .Find or .Special cells, it will return #VALUE when called from a worksheet formula.

 

[ExperTeece]

What is the code for AttRng ?

if it is using .Find or .Special cells, it will return #VALUE when called from a worksheet formula.

AttRng is just the range of values passed to the code. On the spreadsheet the call to the function is =DescBuild(B6,B7:B21). The values in B7:B21 are passed to AttRng in the function.

 

[mikerickson]

1) If any cell in AttRng contains an error (e.g. #DIV/0) then the function will error.
2) If the optional AttRng argument is omitted, the function will error.

I am a bit puzzled, it looks like the function is just returning the comma delimited string formed by the values in AttRng (15 cells in a column) (omitting blanks) with the string Class as a prefix.
Have you tried replacing AttRng(AttCount) with AttRng.Cells(AttCount, 1) ?

 

[ExperTeece]

1) If any cell in AttRng contains an error (e.g. #DIV/0) then the function will error.
2) If the optional AttRng argument is omitted, the function will error.

I am a bit puzzled, it looks like the function is just returning the comma delimited string formed by the values in AttRng (15 cells in a column) (omitting blanks) with the string Class as a prefix.
Have you tried replacing AttRng(AttCount) with AttRng.Cells(AttCount, 1) ?

You are correct that the routine is just a concatenation routine. I tried what you suggested and got the same error. I had actually tried that before but figured I had messed with it so much that it was worth another try. As I stated in the original message I can fix it if I type a value in the cell that is passed as Class and then save the spreadsheet so I know it has to do with the blank cells. I really was just curious because it just seems like it should work.

 

[mikerickson]

What formula do you use to call the function?
As written the UDF is useless without the AttRng argument so why not make it a required argument.

This is a version of a similar UDF with some bullet proofing added.

Function DescBuild(AttRng As Range, Optional Prefix As String) As String
    Dim FinalString As String
    Dim attCnt As Long
 
    DescBuild = ""
    FinalString = ""

    For attCnt = 1 To 15
        If Not CStr(AttRng.Cells(attCount, 1).Value) = "" Then
            FinalString = FinalString & ", " & CStr(AttRng.Cells(attCnt, 1).Value)
        End If
    Next

    If Prefix = vbNullString Then
        DescBuild = Mid(FinalString, 3)
    Else
        DescBuild = Prefix & ", " & Mid(FinalString, 3)
    End If

End Function

 

[ExperTeece]

What formula do you use to call the function?
As written the UDF is useless without the AttRng argument so why not make it a required argument.

This is a version of a similar UDF with some bullet proofing added.

Function DescBuild(AttRng As Range, Optional Prefix As String) As String
    Dim FinalString As String
    Dim attCnt As Long
 
    DescBuild = ""
    FinalString = ""

    For attCnt = 1 To 15
        If Not CStr(AttRng.Cells(attCount, 1).Value) = "" Then
            FinalString = FinalString & ", " & CStr(AttRng.Cells(attCnt, 1).Value)
        End If
    Next

    If Prefix = vbNullString Then
        DescBuild = Mid(FinalString, 3)
    Else
        DescBuild = Prefix & ", " & Mid(FinalString, 3)
    End If

End Function

My calling formula is =DescBuild(B6,B7:B21). I tried your code and got a similar result. It works fine until the file is saved with no values in any of the cells. As soon as I save it and reopen it I get the value error. The smart tag text that comes up when I hover over the cell says "A value used in the formula is of the wrong data type.

 

[Rick Rothstein]

While this does not answer the question you asked, you may find the UDF that I posted here of some interest (it is a concatenation program with additional functionality)...

Flexible Concatenation Function