Excel VBA UDF для конкатенации дает сообщение об ошибке
Я пытаюсь написать пользовательскую функцию (UDF) в Excel, которая будет принимать значения в диапазоне ячеек и определенным образом конкатенировать их. В частности, я хочу объединить их так, чтобы результирующая строка могла быть вставлена в функцию SQL «in», то есть, если у меня есть диапазон в Excel, который содержит:
apples oranges pears Я хочу, чтобы UDF приводил к 'apples', 'oranges', 'pears'
(т. е. без запятой после последнего значения).
- Совпадение любого слова внутри ячейки с любым словом в диапазоне ячеек
- сравните 3 диапазона вместо 2
- Функция Excel, определяемая пользователем, оценивается до нуля, если открыта другая рабочая книга
- публичная функция в Excel, которая не работает должным образом, как UDF
- Преобразование UDF в массив UDF
Это мой код – он компилирует ОК в окне 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
- Пользовательская функция в ms excel 2010 для получения имени пользователя переменной окружения не работает
- Функция поиска соответствия индексов в VBA
- Попытка получить форматирование пользовательских ячеек Excel UDF для возврата ничто «пустой ячейки» или «форматированного ввода пользователя»
- Пользовательская функция Возврат #Value
- excel vba UDF в цикле массива
- Excel - Пользовательская функция: ,
- Сравните два диапазона: классический диапазон и диапазон значений с разделителями-запятыми в одной ячейке
- Запустить 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