CODEWITHSUNDEEP
vbaexcel
s.turn
s.turn

Reputation: 71

Excel UDF - getting VALUE! error and not sure why

After some good advice here on Friday which helped me fix my VBA code, I thought I'd try my hand at a similar user-defined function. The idea here is to take a list of values and (optionally) a table reference (e.g. "t") to end up with a string like: t.value1 + t.value2 + t.value3

It compiles fine and I have checked it for typos and wrong names (though it's possible I have still missed something). When I try to use it in the worksheet, I get a "VALUE!" error. The below code is saved in a module within the VBA Editor in Excel.

Thanks in advance for any suggestions.

(p.s. excuse my "VBA for dummies" style comments - it's because I am a VBA dummy!)

    'Here we'll create the formula's structure - these are the bits the worksheet user will choose:
    Function ConcatenateToAdd(ConcatenateRange as Range, Optional TableReference as String = "") As Variant  'the default value for TableReference will be ""

    'And here are our other building blocks that we'll use behind the scenes:

        Dim i As Long

        Dim strResult1 As String  'this will be everything up to the last value
        Dim strResult2 As String  'this will add the last value on to the string produced as strResult1

        Dim Separator1 As String
        Dim Separator2 As String

        Separator1 = "."            'this will slip between the table reference and the field name
        Separator2 = " + "          'this will go after each field name, except the last.   



    'Just in case - let's make a back-up plan
        On Error GoTo ErrHandler

    'OK - let's go!

    'First, let's string together every value but the last one:
       For i = 1 To ConcatenateRange.Count - 1                                        
                  strResult1 = strResult1 & TableReference & Separator1 & ConcatenateRange.Cells(i).Value & Separator2

        Next i


    'Lovely!  Now let's just add on the last one - this one won't have  a + on the end.

        For i = ConcatenateRange.Count - 0 To ConcatenateRange.Count + 0                        'I'm sure this is not the most elegant way to phrase this...

          strResult2 = strResult1 & TableReference & Separator1 & ConcatenateRange.Cells(i).Value

        Next I

'The next bit tells Excel what the final result of the formula should be, in the worksheet: 
ConcatenateToAdd = strResult2


    'And this is what the error handler does - it will just make Excel shout "ERROR!" at you.  Let's hope it doesn't need to.
            ErrHandler:
        ConcatenateToAdd = CVErr(xlErrValue)

    'And that's all!    
        End Function

Upvotes: 2

Views: 1135

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

You are just missing a little bit of error handling. In your code whatever happens the result will be set to an error value because you either:

a) don't exit the function after you set ConcatenateToAdd = strResult2, or

b) check that an error actually occurred in ErrHandler

Try it like below - I've refactored your code a little as you don't need two loops (and therefore only need strResult1):

Option Explicit

Function ConcatenateToAdd(ConcatenateRange As Range, Optional TableReference As String = "") As Variant

    On Error GoTo ErrHandler

    Dim i As Long
    Dim strResult1 As String
    Dim Separator1 As String
    Dim Separator2 As String

    ' update format if TableReference = ""
    If TableReference = "" Then
        Separator1 = ""
    Else
        Separator1 = "."
    End If
    Separator2 = " + "

    strResult1 = ""
    For i = 1 To ConcatenateRange.Count
        strResult1 = strResult1 & TableReference & Separator1 & ConcatenateRange.Cells(i).Value
        If i < ConcatenateRange.Count Then
             strResult1 = strResult1 & Separator2
        End If
    Next i

    ConcatenateToAdd = strResult1

    'you could do an Exit Function here
    'Exit Function

' or continue into the ErrHandler block
ErrHandler:
    ' check an error actually occurred
    If Err.Number <> 0 Then
        ConcatenateToAdd = CVErr(xlErrValue)
    End If

    ' ConcatenateToAdd still equals strResult1 if no error occurred

End Function

The bit to note is that the return of the function is set after building the string:

ConcatenateToAdd = strResult1

You could do an Exit Function as the next line, but if you let the execution slip into the ErrHandler block then you should only update the value of ConcatenateToAdd if there has been an error. You can handle that with:

If Err.Number <> 0 Then
    ConcatenateToAdd = CVErr(xlErrValue)
End If

Upvotes: 2

Related Questions