用于串联的Excel VBA UDF提供了一条错误消息 [英] Excel VBA UDF for concatenating is giving an Error message
问题描述
我正在尝试在Excel中编写一个用户定义函数(UDF),该函数将采用一系列单元格中的值,并以某种方式将它们连接起来.具体来说,我想以一种方式将它们串联起来,以便可以将结果字符串粘贴到SQL输入"函数中-即,如果我在Excel中具有包含以下内容的范围:
I'm trying to write a User Defined Function (UDF) in Excel that will take the values in a range of cells, and concatenate them in a certain way. Specifically, I want to concatenate them in a way that the resulting string could be pasted into a SQL "in" function - i.e. if I have a range in Excel that contains:
apples
oranges
pears
我希望UDF生成'apples','oranges','pears'
(即最后一个值后没有逗号).
(i.e. no comma after the last value).
这是我的代码-可以在VBA窗口中编译OK,但是当我在工作表中使用它时,我只会得到ERROR.任何想法都很感激-我在编写VBA方面是个新手.对于这个模糊的问题表示歉意;我不知所措,看看是哪一点造成了麻烦.
This is my code - it compiles OK in the VBA window, but when I use it in a worksheet I just get ERROR. Any thoughts much appreciated - I'm a bit of a newbie at writing VBA. And apologies for the vague question; I'm just at a loss to see which bit is causing the trouble.
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数组方法.
I prefer the JOIN array approach.
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
这篇关于用于串联的Excel VBA UDF提供了一条错误消息的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!