I have almost no experience in Visual Basic and this little macro script is one of the first times GPT 4-o has been useful to me as a coding assistant. I know Claude gets a lot of attention, but the GPT models are the ones provided to me at work, and I was able to get good-enough results from it.

If you’re primarily interested in the script, here it is.

So this was a work problem, a macro script, the simple, one-file equivalent of a can opener. You can see the script; I just tied it to a button in the spreadsheet. I thought the process was more interesting than the product, in this case.

Working on something like this, I usually make a task list and keep documentation and StackOverflow open on one screen and gradually hack and check to get the items in the list working. If I have a poor understanding of the task or the language, their may be some task-resequencing, but it’s largely a chug-along process until you get something that works.

So, I described what I needed, a pretty simple programmatic stepping through a handful of sheets to update one task list based on another, pulling in outside information when needed.

The LLM output a commented script that bore some resemblence to what you see below. Did it work? Absolutely not. It did present correct Visual Basic syntax in a somewhat logical order, and that was surprisingly helpful.

Some of the output was straightforwardly wrong, and some was just inefficient. It gave me a toe hold in something I didn’t have experience with in a fraction of the time it would take to orient myself.

A Step-Through

 6    Dim PLSheet As Worksheet
 7    Dim PISheet As Worksheet
 8    
 9    Dim timelineSheet As String
10    Dim testName As String

This section was pure syntax. It was helpful to get some of these directly back from the LLM, and it essentially saved me a single Google. I don’t think it’s too much to admit that even something as simple as wrapping the code in “Sub” helped me out too. It’s the kind of thing I could see myself forgetting to do and wondering why something wasn’t working as expected for 30 minutes.

53    lrM = MPLSheet.Cells(MPLSheet.Rows.Count, "A").End(xlUp).Row

This I didn’t get for free. It was the output attempting to find the final row with data on one of the lists (one of the things I had asked it for), but the output wasn’t functional. Placement was helpful, and I had the excuse to start digging into the dot-based object-oriented-ish syntax used here.

84 Set copyRange = TSheet.Range(TSheet.Cells(3, fcT), TSheet.Cells(lrT, lcT)) ' sets the outer boundaries of the copy space
85    With copyRange
86        .AutoFilter Field:=1, Criteria1:="*TDS*", Operator:=xlOr, Criteria2:="=" 'Take rows where "Responsible" column contains TDS or is blank
87        .AutoFilter Field:=3, Criteria1:="<>" ' Take rows where Duration is not blank
88        .SpecialCells(xlCellTypeVisible).Copy Destination:=PLSheet.Cells(frPL_blank, fcPL) ' copy these cells to the project log
89    End With

This was one of those inefficiencies. The raw output had no filtering, and as we’ll see, there was a repeated, line-by-line copy and paste rather than a single block copy-over.

100PLSheet.Range(PLSheet.Cells(frPL_prev, mpl_tlead), PLSheet.Cells(frPL_blank - 1, mpl_tlead)).Value = projectLead
101            
102' Check the checkbox in the Complete? column in the Master Project List sheet
103MPLSheet.Cells(i, mpl_logged).Value = True

Second-to-last, pasting repeated information needed some work. Like the above, I refactored to do a single paste rather than line-by-line. Finally, the boolean value update was handled correctly. That may be a low bar, though.

Next Steps

There’s another part of this that I currently have running with a spreadsheet formula. It’s a funny XLOOKUP(), with a wildcard search of the list’s responsible parties field, finding a match in another sheet, and applying an offset based on the particular sheet you came from.

Sounds complicated, but it’s working fine. I don’t know Visual Basic well enough to quickly translate into it here, and I did the obvious thing of asking the LLM to do the translation for me, without much success.

Evaluation

I probably could have prompted better, or deeper, at least. I had some limited success specifying and rejecting things I didn’t want. This is not the dream of Copilot, but my sense is that, while I might be somewhat below the curve, my experience isn’t out-of-sample. The LLM was helpful, but not magic.

I also don’t want to under-rate this. I was able to turn this around in a few hours without previous experience with the language. That might be something close to a best-case scenario. I’d expect this approach to fall down with a project of actual complexity, where architecture needed to be considered or runtime performance was any kind of a concern. But, as a bunch of quick lookups and sequencings to get something generally the right shape and look in the right order, I was pleased.

Whole Script

  1 Sub ModifyExcelSpreadsheet()
  2
  3    ' Declarations
  4    Dim MPLSheet As Worksheet
  5    Dim TSheet As Worksheet
  6    Dim PLSheet As Worksheet
  7    Dim PISheet As Worksheet
  8    
  9    Dim timelineSheet As String
 10    Dim testName As String
 11    Dim projectLead As String
 12    Dim projectCode As String
 13    Dim testCode As String
 14    Dim searchText As String
 15    Dim result As String
 16    
 17    Dim lrM As Integer ' last row Master Project List
 18    Dim lrT As Integer ' last row Timeline, reassigned for each new timeline
 19    Dim fcPL As Integer ' first column of Project Log to paste timeline data
 20    Dim fcT As Integer ' first column of timeline to copy
 21    Dim frPL_blank As Integer ' first blank row of project log, recheck for each master entry
 22    Dim frPL_prev As Integer ' holder for previous value of frPL
 23    Dim lcT As Integer ' last column Timeline, reassigned for each new timeline
 24    Dim lrPI As Integer 'last row Project Info
 25    Dim i As Integer ' row counter for Master Project List
 26    Dim j As Integer ' row counter for Project Log
 27    Dim k As Integer ' row counter for Project Information
 28    
 29    Dim mpl_logged  As Integer
 30    Dim mpl_timeline As Integer
 31    Dim mpl_pcode As Integer
 32    Dim mpl_tcode As Integer
 33    Dim mpl_tname As Integer
 34    Dim mpl_tlead As Integer
 35    Dim match_col As Integer
 36    
 37    Dim copyRange As Range
 38    
 39    ' Placeholder for better error handling
 40    On Error GoTo ErrorHandler
 41    
 42    ' Set references to the first and last timeline columns and project log column to copy
 43    fcT = 3
 44    lcT = 7
 45    fcPL = 4
 46    
 47    ' Set references to the worksheets
 48    Set MPLSheet = ThisWorkbook.Worksheets("Master Project List")
 49    Set PLSheet = ThisWorkbook.Worksheets("Project Log")
 50    Set PISheet = ThisWorkbook.Worksheets("Program Information")
 51    
 52    ' Find current final row of Master Project List
 53    lrM = MPLSheet.Cells(MPLSheet.Rows.Count, "A").End(xlUp).Row
 54    
 55    'Find the 1 indexed column for each column name
 56    mpl_logged = Application.WorksheetFunction.Match("*Logged*", MPLSheet.Rows(1), 0)
 57    mpl_timeline = Application.WorksheetFunction.Match("*Timeline*", MPLSheet.Rows(1), 0)
 58    mpl_pcode = Application.WorksheetFunction.Match("*Program Code*", MPLSheet.Rows(1), 0)
 59    mpl_tcode = Application.WorksheetFunction.Match("*Project Code*", MPLSheet.Rows(1), 0)
 60    mpl_tname = Application.WorksheetFunction.Match("*Project Name*", MPLSheet.Rows(1), 0)
 61    mpl_tlead = Application.WorksheetFunction.Match("*Project Lead*", MPLSheet.Rows(1), 0)
 62
 63    ' Loop through each row in the Master Project List sheet
 64    For i = 2 To lrM
 65        
 66        ' Check if the "Complete?" checkbox is not checked
 67        If MPLSheet.Cells(i, mpl_logged).Value = False Then
 68
 69            timelineSheet = MPLSheet.Cells(i, mpl_timeline).Value ' Set Timeline Name
 70            testName = MPLSheet.Cells(i, mpl_tname).Value ' Set Test Name
 71            projectLead = MPLSheet.Cells(i, mpl_tlead).Value ' Set TD Lead
 72            projectCode = MPLSheet.Cells(i, mpl_pcode).Value ' Set Project Code
 73            testCode = CStr(MPLSheet.Cells(i, mpl_tcode).Value) ' Set Test Code as String
 74            Set TSheet = ThisWorkbook.Worksheets(timelineSheet) ' this will throw an error if there isn't a matching timeline sheet name
 75
 76            
 77            ' Find the last row in the Timeline sheet
 78            lrT = TSheet.Cells(TSheet.Rows.Count, "D").End(xlUp).Row
 79            
 80            ' Find the last row in the Project Log sheet
 81            frPL_blank = PLSheet.Cells(PLSheet.Rows.Count, "E").End(xlUp).Row + 1 ' the +1 should find the first empty row
 82              
 83            'Copy rows to Project Log
 84            Set copyRange = TSheet.Range(TSheet.Cells(3, fcT), TSheet.Cells(lrT, lcT)) ' sets the outer boundaries of the copy space
 85            With copyRange
 86              .AutoFilter Field:=1, Criteria1:="*TDS*", Operator:=xlOr, Criteria2:="=" 'Take rows where "Responsible" column contains TDS or is blank
 87              .AutoFilter Field:=3, Criteria1:="<>" ' Take rows where Duration is not blank
 88              .SpecialCells(xlCellTypeVisible).Copy Destination:=PLSheet.Cells(frPL_blank, fcPL) ' copy these cells to the project log
 89            End With
 90            
 91            'Find the first and last rows entered
 92            frPL_prev = frPL_blank
 93            frPL_blank = PLSheet.Cells(PLSheet.Rows.Count, "E").End(xlUp).Row + 1
 94            
 95            'paste relevant product information to the left of the timeline data
 96            'this depends on the columns being in the same position in the master project list and the project log
 97            PLSheet.Range(PLSheet.Cells(frPL_prev, mpl_pcode), PLSheet.Cells(frPL_blank - 1, mpl_pcode)).Value = projectCode
 98            PLSheet.Range(PLSheet.Cells(frPL_prev, mpl_tcode), PLSheet.Cells(frPL_blank - 1, mpl_tcode)).Value = testCode
 99            PLSheet.Range(PLSheet.Cells(frPL_prev, mpl_tname), PLSheet.Cells(frPL_blank - 1, mpl_tname)).Value = testName
100            PLSheet.Range(PLSheet.Cells(frPL_prev, mpl_tlead), PLSheet.Cells(frPL_blank - 1, mpl_tlead)).Value = projectLead
101                       
102            ' Check the checkbox in the Complete? column in the Master Project List sheet
103            MPLSheet.Cells(i, mpl_logged).Value = True
104            
105        End If
106    Next i
107      
108ErrorHandler:
109    MsgBox "An error occurred: " & Err.Description, vbCritical
110    
111End Sub