Excel VBA UDF для конкатенации дает сообщение об ошибке

Я пытаюсь написать пользовательскую функцию (UDF) в Excel, которая будет принимать значения в диапазоне ячеек и определенным образом конкатенировать их. В частности, я хочу объединить их так, чтобы результирующая строка могла быть вставлена ​​в функцию SQL «in», то есть, если у меня есть диапазон в Excel, который содержит:

apples oranges pears 

Я хочу, чтобы UDF приводил к 'apples', 'oranges', 'pears'

(т. е. без запятой после последнего значения).

Это мой код – он компилирует ОК в окне VBA, но когда я использую его на листе, я просто получаю ERROR. Любые мысли, которые мне очень понравились – я немного новичок в написании VBA. И извинения за неопределенный вопрос; Я просто не понимаю, какой бит причиняет неприятности.

 Function ConcatenateforSQL(ConcatenateRange As Range) As Variant Dim i As Long Dim strResult1 As String Dim strResult2 As String Dim Separator1 As String Dim Separator2 As String Separator1 = "'" 'hopefully the quotes act as escape characters Separator2 = "'," On Error GoTo ErrHandler For i = 1 To CriteriaRange.Count - 1 'all but the last one strResult1 = strResult1 & Separator1 & ConcatenateRange.Cells(i).Value & Separator2 Next i 'next, sort out the last example in the string For i = CriteriaRange.Count - 0 To CriteriaRange.Count + 0 strResult2 = strResult1 & Separator1 & ConcatenateRange.Cells(i).Value & Separator1 Next i ConcatenateforSQL = strResult2 Exit Function ErrHandler: ConcatenateforSQL = CVErr(xlErrValue) End Function 

Я предпочитаю подход массива JOIN.

 Option Explicit Function ConcatenateforSQL(ConcatenateRange As Range) As Variant On Error GoTo ErrHandler Dim r As Long, c As Long Dim vVAL As Variant, vVALS As Variant ReDim vVAL(1 To 1) vVALS = ConcatenateRange.Value2 For r = LBound(vVALS, 1) To UBound(vVALS, 1) For c = LBound(vVALS, 2) To UBound(vVALS, 2) 'Debug.Print vVALS(r, c) ReDim Preserve vVAL(1 To (r * c)) vVAL(r * c) = vVALS(r, c) Next c Next r ConcatenateforSQL = Chr(39) & Join(vVAL, "','") & Chr(39) Exit Function ErrHandler: ConcatenateforSQL = CVErr(xlErrValue) End Function 

Это работает для меня (не стесняйтесь добавлять в свои ловушки ошибок и т. Д.):

 Function ConcatenateforSQL(ConcatenateRange As Range) As Variant Dim csql As String csql = "" For Each cl In ConcatenateRange If Len(cl) > 0 Then If csql <> "" Then csql = csql & "," csql = csql & "'" & cl.Value & "'" End If Next ConcatenateforSQL = csql End Function 

Несколько иной подход, который позволяет вам указать разделитель запятой (это будет запятая, если вы не укажете). Можно было бы добавить еще один аргумент для другого.

 Function ConcatenateforSQL(ConcatenateRange As Range, Optional sSep As String = ",") As Variant Dim i As Long Dim strResult As String On Error GoTo ErrHandler For i = 1 To ConcatenateRange.Count strResult = strResult & sSep & "'" & ConcatenateRange.Cells(i).Value & "'" Next i ConcatenateforSQL = Mid(strResult, Len(sSep) + 1) Exit Function ErrHandler: ConcatenateforSQL = CVErr(xlErrValue) End Function 
  • Использование округления раунда Excel в раундах UDF 1 к 0
  • Ссылка на внешнюю книгу в функции
  • EXCEL UDF dll не отображается в excel adins
  • публичная функция в Excel, которая не работает должным образом, как UDF
  • Excel VBA Непрерывная матрица Определенная пользователем функция Fix
  • Как превратить формулу Excel в UDF?
  • Можно ли использовать FindPrevious при поиске кода VBA в UDF?
  • Пользовательская функция формулы excel UDF для подсчета Условное форматирование
  • Пользовательская функция в VBA не "array-firendly"
  • Пользовательская функция в ms excel 2010 для получения имени пользователя переменной окружения не работает
  • Ошибка #VALUE при копировании листов
  • Interesting Posts

    Как изменить название страны в код ISO в Talend?

    Два для каждого оператора цикла в событии изменения книги

    сохраняя текущую сумму формулы с условиями

    Поиск списка для разных серий, а затем их соответствующие значения

    Как изменить цвет строк, если все строки пустые. VBA или нет?

    Перемещение данных в самую левую пустую ячейку с помощью VBA

    VBA – Невозможно закрыть excel

    столбцы не читаются в последовательном порядке при чтении листа excel с использованием java

    VBA, как вставить ссылку динамической / относительной ячейки в метод .formulaArray?

    Как читать первые n строк файла HUGE excel

    Перекрестные ссылки на таблицы Excel

    Excel: как скопировать данные конкретной ячейки (почтовые индексы) в новый столбец

    Размещение VBA в другом листе

    Salesforce Excel Добавить в – автообновление не запускает загрузку данных

    Способ вызова функциональности файла плагина Excel (.xll или .dll) без установки Excel?

    Давайте будем гением компьютера.