Programming with AI

You can shortcut some work by asking a large language model (LLM) how to do something.  If someone else out there has done it and the AI LLM has trained on it, it might even do a decent job.

Today I needed to write a function that would take a string of SQL statements separated by semicolons “;” BUT, I was having a problem with the code I was already doing this because there were semicolons inside of some of the SQL strings that were being updated.

Knowing what you want and thinking about potential problems with subroutines can help you ask the right questions.  In this case I first asked:

“Write a vba function to split SQL strings into an array of SQL commands”.  It automatically just did a straight split of all segments on the “;” character.

I was then able to prompt: “Now make sure that semicolons in strings are ignored”.  This caused it to write some more complex code.

I reviewed it carefully to see if it would do what I wanted.  A good thing to do in this case would be to write some tests (using Test Driven Development and RubberDuckVBA) to do sanity checking on the function.  In fact, it wrote some example code in the answer on how to use the function and what it would output.  You could easily use this as a start to program some tests to make sure you were getting the expected output.

In my case I used CoPilot. My first attempt actually did something different, although this function also works although it’s rather verbose for what it’s doing:

'Write a vba function to split SQL strings into an array of SQL commands
Function SplitSQLCommands(sqlString As String) As Variant
    Dim sqlCommands() As String
    Dim command As String
    Dim i As Integer
    Dim startPos As Integer
    Dim endPos As Integer
    
    ' Initialize the start position
    startPos = 1
    i = 0
    
    ' Loop through the SQL string to find each command
    Do While startPos <= Len(sqlString)
        ' Find the position of the next semicolon
        endPos = InStr(startPos, sqlString, ";")
        
        ' If no more semicolons are found, exit the loop
        If endPos = 0 Then Exit Do
        
        ' Extract the command
        command = Mid(sqlString, startPos, endPos - startPos + 1)
        
        ' Add the command to the array
        ReDim Preserve sqlCommands(i)
        sqlCommands(i) = command
        
        ' Move the start position to the next character after the semicolon
        startPos = endPos + 1
        i = i + 1
    Loop
    
    ' Return the array of SQL commands
    SplitSQLCommands = sqlCommands
End Function

Now after following up with the second prompt we get this function:

'Now make sure that semicolons in strings are ignored
Function SplitSQLCommands(sqlString As String) As Variant
    Dim sqlCommands() As String
    Dim command As String
    Dim i As Integer
    Dim startPos As Integer
    Dim endPos As Integer
    Dim inString As Boolean
    Dim currentChar As String
    
    ' Initialize the start position and inString flag
    startPos = 1
    i = 0
    inString = False
    
    ' Loop through the SQL string to find each command
    Do While startPos <= Len(sqlString)
        ' Initialize endPos to startPos
        endPos = startPos
        
        ' Loop to find the next semicolon not within a string
        Do While endPos <= Len(sqlString)
            currentChar = Mid(sqlString, endPos, 1)
            
            ' Toggle inString flag if a single quote is found
            If currentChar = "'" Then
                inString = Not inString
            End If
            
            ' If a semicolon is found and we are not within a string, exit the loop
            If currentChar = ";" And Not inString Then
                Exit Do
            End If
            
            ' Move to the next character
            endPos = endPos + 1
        Loop
        
        ' Extract the command
        command = Mid(sqlString, startPos, endPos - startPos + 1)
        
        ' Add the command to the array
        ReDim Preserve sqlCommands(i)
        sqlCommands(i) = command
        
        ' Move the start position to the next character after the semicolon
        startPos = endPos + 1
        i = i + 1
    Loop
    
    ' Return the array of SQL commands
    SplitSQLCommands = sqlCommands
End Function

Note that if you do the same prompts, you might get something different, in fact you probably will. Checking and testing the function to make sure it works can be as simple as using the immediate window as well. Anyway, it dwindled my work of writing the function myself down by quite a bit. You can also use these responses from CoPilot and ask it if there are any other ways to do the task or add other restrictions or changes to how the function works. I know that doing these types of things for relatively simple input / output functions has helped me on more than one occasion!