CODEWITHSUNDEEP
vbaexceldynamic-programming
wendy liu
wendy liu

Reputation: 15

sectioning a dynamic list vba

Edit: The solution below helped in a way that combined all my select statements into one string, but I ended up using unions because I had too many tags and SQL would not take the amount of OR clauses. The exact solution below might help someone who has less elements.

I have a dynamic string list that i want to section off into batches of 4000 so i can combine those 4000 into one string. Currently i have a program that assumes there is at most 8000 items, which is giving me errors since the list extended beyond that. I was thinking of using a dynamic variable in VBA (which i learned in C++) but it does not apply in VBA sadly... The tagcapture() is used to grab the item name from a worksheet column. MaxTagPull is 3999 to account for the 0th item being counted. 

    For CurrentIndex = 2 To LastTagIndex
        If RegTagCount = 0 Then                                     ' If first regular tag in string
            String2 = "'" & tagcapture(1, CurrentIndex) & "'"
            String3 = String3 & String2
        End If
        If RegTagCount > 0 And RegTagCount < MaxTagPull Then        ' If second tag and up until max number of tags allowed in one query string
            String2 = ", '" & tagcapture(1, CurrentIndex) & "'"
            String3 = String3 & String2
        End If
        If RegTagCounter = MaxTagPull Then                          ' Put the rest of the regular tags in a separate string
            String4 = "'" & tagcapture(1, CurrentIndex) & "'"
            String5 = String5 & String4
        End If
        If RegTagCounter > MaxTagPull Then                          ' Put the rest of the regular tags in a separate string
            String4 = ", '" & tagcapture(1, CurrentIndex) & "'"
            String5 = String5 & String4
        End If
        RegTagCount = RegTagCount + 1                               ' Counts the amount of regular tags encountered


    Next

How do i modify this so it can accommodate the dynamic list? Maybe using a function or another sub? Note that the total number of items is known, so maybe something could be done with that?

Thank you

Upvotes: 1

Views: 90

Answers (1)

Tim Williams
Tim Williams

Reputation: 166595

This is not the exact question you asked, but I think it meets your final goal:

Sub Tester()
    Debug.Print SQLInClause(Range("B3:B29"), "myFieldName", False)
End Sub

'Given a single-column range "rng", convert the content to a SQL "in" clause,
'  with a maximum number of items per "in" block, with blocks joined by "or"
'  Use the SQL field name supplied in "fName"
'  "isChar" determines whether to quote the values
Function SQLInClause(rng As Range, fName As String, isChar As Boolean) As String

    Const MAX_PER_BLOCK As Long = 10 '<<10 for testing: set to 4000 for production use...

    Dim sql As String, arr(), i As Long, n As Long
    Dim d, sep, qt, tot As Long


    d = rng.Value
    tot = UBound(d, 1)
    i = 0      'counter for each block
    qt = IIf(isChar, "'", "") '<< set quote character (or none)
    sep = ""
    ReDim arr(1 To MAX_PER_BLOCK)

    For n = 1 To tot
        'any value to add?
        If Len(d(n, 1)) > 0 Then
            i = i + 1 
            arr(i) = d(n, 1)

            'are we at the end of a block, or at the end of the list?
            If i = MAX_PER_BLOCK Or n = tot Then

                'if at the end of the list, resize array to trim off empty elements
                If n = tot Then ReDim Preserve arr(1 To i)

                sql = sql & sep & fName & " in(" & qt & Join(arr, qt & "," & qt) & qt & ")"
                sep = vbCrLf & " or " '<< "or" is set after first block
                i = 0
            End If
        End If
    Next n
    SQLInClause = sql
End Function

Sample output (formatted a little):

myFieldName in('Val0001','Val0002','Val0003','Val0004','Val0005','Val0006',
               'Val0007','Val0008','Val0009', 'Val0010') 
or myFieldName in('Val0011','Val0012','Val0013','Val0014','Val0015','Val0016',
      'Val0017','Val0018','Val0019', 'Val0020') 
or myFieldName in('Val0021','Val0022','Val0023','Val0024','Val0025','Val0027')

Upvotes: 1

Related Questions