AI for VBA Excel script: Gemini vs ChatGPT vs Copilot Comparison

, ,

Image created by DALLE-3 - Edited by Qubilinx
Image created by DALLE-3 – Edited by Qubilinx

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: 

  1. Copilot
  2. ChatGPT
  3. 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:

  1. It sets a reference to the “Formula” sheet.
  2. Retrieves the new sheet name from cell A2 of the “Formula” sheet.
  3. Checks if the sheet name already exists. If it does, it appends a version number.
  4. Copies the “Formula” sheet to a new sheet with the unique name.
  5. Displays a message box with the success notification.

Feel free to customize this code to fit your specific requirements! 🚀

From <https://copilot.microsoft.com/?form=WSBSH2&cvid=0ac33d73c21145f8b3db09a07b11253e&nclid=D1DDF2364A2DA95855CA24F0C725A0D5&ts=1710138107317&nclidts=1710138107&tsms=317>

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>

Related Posts

Van Gogh-inspired artwork

,

AI: Generative Adversarial Networks (GANs)

Generative Adversarial Networks (GANs) are based on the idea where a generator and a discriminator network work together to improve each other.   The Generator network creates new synthetic data that resembles authentic data (like images), while the Discriminator network works towards finding out if synthetic data is good enough to fake the reality (authenticity). […]

Multi-Head Self-Attention Mechanism.png

,

Transformer Architecture: Most Effective Deep Learning Model for Text

Transformer architecture: is a neural network architecture that’s quite different from traditional RNN, CNN, LSTM owing to its Attention Mechanism and parallel processing capabilities (see ref). “In this work we propose the Transformer, a model architecture eschewing recurrence and instead relying entirely on an attention mechanism to draw global dependencies between input and output. The […]