CODEWITHSUNDEEP
vbaexcel
seegoon
seegoon

Reputation: 573

Of two identical VBA functions one suddenly returns a #NAME error

I've just experienced a sudden failure of one of my macros. It calls upon the following function, which concatenates a series of cells using a selected optional delimiter:

Public Function MAKELIST(ByVal cellRange As Range, Optional ByVal delimiter As String)
 'Function to join a range of cells together with an optional
 Dim c As Range
 Dim newText As String
 Dim Count As Integer
 Count = 0
 newText = ""
 For Each c In cellRange
  Count = Count + 1
  newText = newText & c.Value
  If Count < cellRange.Count Then
   newText = newText & delimiter
  End If
 Next
 MAKELIST = newText
End Function

It is simply joining together manually-entered cell data--any values at all appear to be breaking it. It appears that the problem lies with how the function is being referred to/called (sorry, not good with the nomenclature) rather than the function itself.

This was working perfectly. I moved the file between folders and it suddenly stopped working, returning a #NAME error every time. Nothing had changed within the code, so I changed it from MAKELIST to MAKELIST2, with identical VBA. This works perfectly. However, I obviously don't want to have to change every reference to the function within my workbook, and I want it to be robust and futureproof so this won't happen to other users. Can anyone shed any light on why this might have happened?

Thanks!

Upvotes: 3

Views: 1825

Answers (1)

brettdj
brettdj

Reputation: 55692

It would be useful to see the problematic range. Although you could just use this much shorter function

[Updated to handle multiple column ranges]

Public Function MAKELIST2(ByVal cellRange As Range, Optional delimiter As String)
Dim rng1 As Range
For Each rng1 In cellRange.Columns
    MAKELIST2 = MAKELIST2 & Join(Application.Transpose(rng1), delimiter)
Next
End Function

Upvotes: 6

Related Questions