Excel VBA UDF для конкатенации дает сообщение об ошибке
Я пытаюсь написать пользовательскую функцию (UDF) в Excel, которая будет принимать значения в диапазоне ячеек и определенным образом конкатенировать их. В частности, я хочу объединить их так, чтобы результирующая строка могла быть вставлена в функцию SQL «in», то есть, если у меня есть диапазон в Excel, который содержит:
apples oranges pears Я хочу, чтобы UDF приводил к 'apples', 'oranges', 'pears'
(т. е. без запятой после последнего значения).
- Excel WBA UDF для управления строкой не работает
- VSTO Excel: как сделать udf (пользовательская функция) с возвратом void?
- сравните 3 диапазона вместо 2
- Скрытие или отсутствие отображения excel VBA UDF module / code
- Неустойчивая пользовательская функция не пересчитывается, как ожидалось (VBA / Excel)
Это мой код – он компилирует ОК в окне 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
- Необходимо остановить пересчет UDF, когда удаленные ячейки не удалены
- Ячейки, использующие UDF в Excel, не переучитывают
- Пользовательский Sub с дополнительными параметрами - не отображается в окне макроса
- Excel VBA #Value! ошибка
- UDF: для значений ячейки меньше, чем x в столбце, возвращайте все значения из первого столбца
- UDF или стандартная функция IF для диапазона дат?
- Определенная пользователем функция в VBA не работает и возвращает ноль, несоответствие типа данных
- UDF, чтобы сделать своего рода замену функции массива
Я предпочитаю подход массива 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