562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Coding in SQL, Python, VBA, JQuery and OfficeScript using AI tools
Part four of an eight-part series of blogs
One of the immediate and obvious ways to use AI tools is to boost your productivity as a programmer. This blog shows how you can use ChatGPT 4 and Google's Bard to write programs in SQL, Python, VBA, JQuery and Office Script (probably the 5 languages Wise Owl use most).
The phrase I'm testing our AI twosome with for VBA is this:
"Write a VBA program which will loop over the members of staff in a worksheet, finding all the staff members whose salaries are more than a given number and writing their personnel number, name and salary to a separate workbook"
This is probably the clearest question, so I'm expecting both AI tools will find it relatively easy to answer. Let's see!
ChatGPT finished by giving me instructions on how to use its macro:
Some AI notes on how to run the VBA macro generated.
As to the macro itself, here's what was offered (I've added colour-coding and a couple of separator lines, but otherwise this is as it came out of the box):
Dim srcSheet As Worksheet
Dim destWorkbook As Workbook
Dim destSheet As Worksheet
Dim salaryThreshold As Currency
Dim i As Long
Dim j As Long
' Set source sheet
'Change "Source" to your source sheet name
Set srcSheet = ThisWorkbook.Sheets("Source")
' Create a new workbook for the filtered staff
Set destWorkbook = Application.Workbooks.Add
Set destSheet = destWorkbook.Sheets(1)
' Set your salary threshold
'Change this to your desired salary threshold
salaryThreshold = 50000
' Initialize the counter for destination sheet
j = 1
' Copy column headers
destSheet.Cells(j, 1).Value = "Personnel Number"
destSheet.Cells(j, 2).Value = "Name"
destSheet.Cells(j, 3).Value = "Salary"
' Loop over the rows in the source sheet
For i = 2 To srcSheet.Cells(Rows.Count, 1).End(xlUp).Row
' Check if salary is greater than threshold
If srcSheet.Cells(i, 3).Value > salaryThreshold Then
' If so, copy the details to the destination sheet
j = j + 1
destSheet.Cells(j, 1).Value = srcSheet.Cells(i, 1).Value
destSheet.Cells(j, 2).Value = srcSheet.Cells(i, 2).Value
destSheet.Cells(j, 3).Value = srcSheet.Cells(i, 3).Value
' Save and close the destination workbook
This is a nice, simple easy to understand solution, and the assumptions are clearly documented. I suppose I was hoping that ChatGPT would create an object variable to refer to the column of cells holding the staff numbers, then use a FOR EACH ... NEXT loop to loop over these, but this is still pretty impressive.
Bard gave the following footnotes explaining its code:
A succinct summary of what the code should do.
And here's the VBA generated (again, I've added some colours):
'This Sub will loop over the members of staff in a worksheet, finding all the staff members whose salaries are more than a given number and writing their personnel number, name and salary to a separate workbook.
Dim wsStaff As Worksheet
Dim wsResults As Worksheet
Dim cell As Range
Dim personnelNumber As String
Dim name As String
Dim salary As Double
Set wsStaff = ThisWorkbook.Sheets("Staff")
Set wsResults = ThisWorkbook.Sheets("Results")
'Set the salary threshold
salary = 100000
'Loop over the staff members
For Each cell In wsStaff.Range("A2:A")
'If the salary is more than the threshold, then write the personnel number, name and salary to the Results workbook
If cell.Value > salary Then
'Get the personnel number
personnelNumber = cell.Offset(0, 1).Value
'Get the name
name = cell.Offset(0, 2).Value
'Get the salary
salary = cell.Value
'Write the data to the Results workbook
wsResults.Range("A1").Value = personnelNumber
wsResults.Range("B1").Value = name
wsResults.Range("C1").Value = salary
This is a better program, I think: its clearer, makes better use of variables and uses a better algorithm. As is now becoming familiar, however, it suffers from one huge drawback: it wouldn't work. The problem is this line:
For Each cell In wsStaff.Range("A2:A")
I wondered what the range A2:A meant, so I tried it out in VBA - it just produces an error. The problem with this is that while this bug would be pretty easy to solve, you'd have to know VBA pretty well to diagnose the problem and correct it!
Yet again, ChatGPT probably wins this. Bard reminds me of a persuasive and likeable friend, who always convinces you that they know the answer to a question but whose answers fall apart under close scrutiny; ChatGPT is the slightly boring nerd (takes one to know one) who may not win the most-popular-kid-in-the-playground award but whose answers you learn to trust.
|Parts of this blog|
25 Aytoun Street