AI for VBA Excel script: Gemini vs ChatGPT vs Copilot Comparison
We tested VBA scripting capabilities of three AI systems (Copilot, Gemini and ChatGPT) and compared the ease of using prompts.
According to the test using the same prompt here is our list of their usefulness:
- Copilot
- ChatGPT
- Gemini AI
Prompt used in this comparison is:
Lets create a VBA code that can copy an excel sheet named Formula to a new sheet and take the new sheet name from cell A2 of Formula sheet, if the new sheet names is already taken create a new version adding a number to the end of the A2 cell entry.
The objective was to create a VBA script that can copy an excel sheet contents into a new one named after the entry in cell A2 of the initial excel sheet.
Copilot was amazing, did what was asked for at the first trial!
ChatGPT had to go through 3 iterations to get it right, after indicating what the error was it corrected the code and it just ran fine.
Gemini, unfortunately failed us! Even after 30 minutes of retries, corrections, new prompt trials it still did not give us a functioning code. Image of the error is at the bottom of this article.
1 Copilot
# VBA code to copy a sheet and handle duplicate sheet names
Sub CopySheetWithUniqueName()
Dim wsFormula As Worksheet
Dim wsNew As Worksheet
Dim newName As String
Dim i As Integer
' Set reference to the Formula sheet
Set wsFormula = ThisWorkbook.Sheets("Formula")
' Get the new sheet name from cell A2 of Formula sheet
newName = wsFormula.Range("A2").Value
' Check if the sheet name already exists
If SheetExists(newName) Then
' If exists, add a version number
i = 1
Do
i = i + 1
newName = wsFormula.Range("A2").Value & " (" & i & ")"
Loop While SheetExists(newName)
End If
' Copy the Formula sheet to a new sheet with the unique name
wsFormula.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Set wsNew = ActiveSheet
wsNew.Name = newName
' Print a success message
MsgBox "Sheet copied to " & newName & " successfully!"
End Sub
Function SheetExists(sheetName As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Sheets(sheetName)
On Error GoTo 0
SheetExists = Not ws Is Nothing
End Function
' Call the main subroutine
Sub Main()
CopySheetWithUniqueName
End Sub
Here’s how the code works:
- It sets a reference to the “Formula” sheet.
- Retrieves the new sheet name from cell A2 of the “Formula” sheet.
- Checks if the sheet name already exists. If it does, it appends a version number.
- Copies the “Formula” sheet to a new sheet with the unique name.
- Displays a message box with the success notification.
Feel free to customize this code to fit your specific requirements! 🚀
2 ChatGPT
Sub CopySheetWithVersion()
Dim ws As Worksheet
Dim newSheetName As String
Dim newName As String
Dim versionNumber As Integer
Dim sheetExistsFlag As Boolean
' Get the active sheet
Set ws = ActiveSheet
' Get the new sheet name from cell A2
newSheetName = ws.Range("A2").Value
' Check if sheet with new name already exists
sheetExistsFlag = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name = newSheetName Then
sheetExistsFlag = True
Exit For
End If
Next ws
' If sheet with new name already exists, create a new version
If sheetExistsFlag Then
versionNumber = 1
newName = newSheetName & " - " & versionNumber
Do While CustomSheetExists(newName)
versionNumber = versionNumber + 1
newName = newSheetName & " - " & versionNumber
Loop
newSheetName = newName
End If
' Copy the "Formula" sheet to a new sheet with the new name
ThisWorkbook.Sheets("Formula").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = newSheetName
End Sub
You can run this code by opening the VBA editor (Alt + F11), inserting a new module (Insert > Module), and pasting the code into the module. Then, you can run the CopySheetWithVersion subroutine to copy the “Formula” sheet to a new sheet with the specified name.
From <https://chat.openai.com/c/cd9c0d21-f489-44ff-ab21-e07578caccb7>
3 Gemini
Sub CopyFormulasToNewSheetWithVersioning()
Dim sourceSheet As Worksheet
Dim newSheet As Worksheet
Dim sheetName As String
Dim versionNumber As Integer
Dim sheetFound As Boolean
' Set source sheet (replace "Formula" with actual name, if needed)
On Error Resume Next
Set sourceSheet = ThisWorkbook.Worksheets("Formula")
On Error GoTo 0
' Check if source sheet exists
If sourceSheet Is Nothing Then
MsgBox "Source sheet not found. Please check the sheet name.", vbExclamation
Exit Sub
End If
' Get sheet name from cell A2 of the source sheet
sheetName = sourceSheet.Range("A2").Value
' Check if sheet name needs versioning (empty or problematic)
If sheetName = "" Or sheetName = "A2" Or Left(sheetName, 4) = "R[1C" Then
' Start version number at 1
versionNumber = 1
' Loop until a unique sheet name is found
Do
sheetFound = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name = sheetName & " " & versionNumber Then
sheetFound = True
versionNumber = versionNumber + 1
Exit For
End If
Next ws
Loop Until Not sheetFound
' Append the version number to the sheet name (if needed)
sheetName = sheetName & " " & versionNumber
End If
' Create the new sheet
Set newSheet = ThisWorkbook.Sheets.Add
' Copy used range with formulas from source sheet
sourceSheet.UsedRange.Copy newSheet.Range("A1")
' Rename the new sheet with the final sheet name
newSheet.Name = sheetName
End Sub
To use this code:
- Paste it into a VBA module within your Excel workbook:
- Open the VBA editor (Alt + F11).
- Right-click on the workbook name in the Project Explorer and select “Insert” > “Module”.
- Paste the code into the module’s code window.
Ensure the source sheet is named correctly:
- If your source sheet is not named “Formula”, replace “Formula” with the actual name in the line Set sourceSheet = ThisWorkbook.Worksheets(“Formula”).
Provide a name in cell A2:
- Enter the desired name for the new sheet in cell A2 of the source sheet.
Run the macro:
- Go back to the Excel workbook.
- Open the “Macros” dialog box (Alt + F8).
- Select the “CopyFormulasToNewSheetWithVersioning” macro and click “Run”.
The code will create a new sheet with the name from cell A2, adding a version number if the name is already taken.
From <https://gemini.google.com/app/568522f2e456cb94>
