This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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). Comparing the coding skills of two different AI tools SQL programming using AI tools Getting help with Python code using AI tools VBA programming using AI tools (this blog) Getting AI help with C# programming Writing programs in Office Scripts using AI Writing JQuery or JavaScript code using AI tools Conclusion: which AI tools (if any) should you use for which languages?

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).

Posted by Andy Brown on 07 June 2023

In this blog

VBA using ChatGPT 4 (time taken: 60 seconds)
Getting Bard to write VBA (time taken: 8 seconds)

Using AI tools to help write your VBA code

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!

VBA using ChatGPT 4 (time taken: 60 seconds)

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):

Sub FilterStaff()

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

End If

Next i

' Save and close the destination workbook

destWorkbook.SaveAs "FilteredStaff.xlsx"

destWorkbook.Close

End Sub

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.

Getting Bard to write VBA (time taken: 8 seconds)

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):

Sub FindHighSalaries()

'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.

'Declare variables

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 variables

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

End If

Next cell

End Sub

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
Comparing the coding skills of two different AI tools SQL programming using AI tools Getting help with Python code using AI tools VBA programming using AI tools (this blog) Getting AI help with C# programming Writing programs in Office Scripts using AI Writing JQuery or JavaScript code using AI tools Conclusion: which AI tools (if any) should you use for which languages?

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Using AI tools to help write your VBA code

VBA using ChatGPT 4 (time taken: 60 seconds)

Getting Bard to write VBA (time taken: 8 seconds)

Head office

London

Manchester