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 
  • UDF не работает с ссылками на ячейки, если локаль изменена
  • Уникальная консолидация данных в нескольких рабочих листах
  • Excel WBA UDF для управления строкой не работает
  • Delphi Excel UDF с возвратом типа Variant
  • Повторное использование кода Spreadhseet
  • UDF для возврата отформатированного номера
  • UDF: для значений ячейки меньше, чем x в столбце, возвращайте все значения из первого столбца
  • UDF не рассчитывается в коде VBA
  • UDF для удаления специальных символов, знаков препинания и пробелов внутри ячейки для создания уникального ключа для Vlookups
  • Генерация комбинаций атрибутов продукта в Excel
  • UDF не обновляется при вставке строк
  • Interesting Posts

    Создайте макрос в Excel, который генерирует документ Word

    Как поместить размер времени в порядок, который я импортировал из таблицы Excel на SQL Server?

    Нечетная ошибка excel-vba Ошибка и не удаляет существующий лист

    Контролировать правильные значения в столбце

    Как установить формат данных для строки в Excel?

    Выборочно объединить строки Excel и удалить исходные строки

    Excel VBA – ошибка выделения выреза

    В Excel VBA на Windows, как смягчить проблему точечного синтаксического обхода анализируемого JSON, нарушенного поведением капитализации IDE?

    Как изменить разрешения на выход, сброшенные в браузер?

    Apache POI – Колонка с неанглийскими символами, например, тайская и ячейка пустая, а затем пропускает ячейку

    Прокрутка мыши в списке

    Заказ списка атрибутов размера

    В Excel я хочу исправить этот код, поскольку он, кажется, не работает propaply

    Excel VBA – Игнорировать подсказки пароля

    создать файл .xls и отправить его по почте с помощью php

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