ChatGPT - a real world application using Excel VBA

[GeoffW]

I wanted to try something using ChatGPT to write a simple application.

I have a little competition with friends, playing Wordle. So I investigated a VBA application to do this.

Firstly, I found a list of Wordle's vocabulary. There were 2309 words. I put those words into column A of a spreadsheet.

So, every time I guessed a word, I put the letters which matched (green or yellow) into cell B1, and the letters which didn't match into cell B2.

Here's what I asked:

I have an excel spreadsheet with a list of words in column 1. Cell b1 has some letters. I want some VBA to produce a list of words from column 1 that contain any of the letters in cell b1, but contain none of the letters in cell b2. Put the results in column C in incremental rows

Here was the result:

Code: Select all

Sub FindMatchingWords()

Dim lastRow As Long
Dim i As Long
Dim word As String
Dim letters As String
Dim excludeLetters As String
Dim match As Boolean

lastRow = Range("A" & Rows.Count).End(xlUp).Row
letters = Range("B1").Value
excludeLetters = Range("B2").Value

For i = 1 To lastRow
    word = Range("A" & i).Value
    match = False
    
    For j = 1 To Len(letters)
        If InStr(word, Mid(letters, j, 1)) > 0 And InStr(excludeLetters, Mid(letters, j, 1)) = 0 Then
            match = True
            Exit For
        End If
    Next j
    
    If match = True Then
        Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = word
    End If
Next i

End Sub

Geoff Whitfield
Me: This doesn't work
Chat: I'm sorry that the code is not working as expected. Could you provide more information on the error message or any issues that you are encountering? This will help me troubleshoot and provide a more accurate solution.
Me: it's only looking at the matching letters from two cells in matching positions
Chat: My apologies for the mistake in the previous code. Here's an updated version that should solve the issue:

Code: Select all

Sub FindMatchingWords()

Dim lastRow As Long
Dim i As Long
Dim word As String
Dim letters As String
Dim excludeLetters As String
Dim match As Boolean

lastRow = Range("A" & Rows.Count).End(xlUp).Row
letters = Range("B1").Value
excludeLetters = Range("B2").Value

For i = 1 To lastRow
    word = Range("A" & i).Value
    match = False
    
    For j = 1 To Len(word)
        If InStr(letters, Mid(word, j, 1)) > 0 And InStr(excludeLetters, Mid(word, j, 1)) = 0 Then
            match = True
        ElseIf InStr(excludeLetters, Mid(word, j, 1)) > 0 Then
            match = False
            Exit For
        End If
    Next j
    
    If match = True Then
        Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = word
    End If
Next i

End Sub

This wasn't quite right - a specification problem. It should match all of the letters in cell B1.

Me: Please change it so that it has to match all the letters in cell b1
chat: Here's an updated version of the code that requires a match for all the letters in cell B1

Code: Select all

Sub FindMatchingWords()

Dim lastRow As Long
Dim i As Long
Dim word As String
Dim letters As String
Dim excludeLetters As String
Dim match As Boolean

lastRow = Range("A" & Rows.Count).End(xlUp).Row
letters = Range("B1").Value
excludeLetters = Range("B2").Value

For i = 1 To lastRow
    word = Range("A" & i).Value
    match = True
    
    For j = 1 To Len(letters)
        If InStr(word, Mid(letters, j, 1)) = 0 Or InStr(excludeLetters, Mid(letters, j, 1)) > 0 Then
            match = False
            Exit For
        End If
    Next j
    
    If match = True Then
        Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = word
    End If
Next i

End Sub

OK, this wasn't perfect, and I'm not sure why - this was just a preliminary run:
. It should clear the contents of column C before it starts. I can ask it to do that.
. It starts on Row 2 for column C - ideally, it should be Row 1. I'm not fussed
. It lets in a few words that it shouldn't. I haven't debugged this, but it's OK for what I want. I've had a brief exploration of the capabilities and deficiencies of ChatGPT code, and the results are impressive, though not perfect.

I haven't asked it to do positional checks of letters. That's something for the future - in the meantime, I can determine those words manually.

While it may be cheating in one way- in another way, I've had a lot more fun getting the solution in this way than in the conventional way.

[ChrisGreaves]

I wanted to try something using ChatGPT to write a simple application.
...
While it may be cheating in one way- in another way, I've had a lot more fun getting the solution in this way than in the conventional way.

Good for you Geoff. Encouraging results and I suspect that the code provided, even counting the iterations, is way better than my stumbling efforts in simulating a Random Walk, way back when.
It's Early Days yet.

Question: How easy /costly was the GPT setup?
Cheers, Chris

[GeoffW]

ChatGPT was simply a matter of enrolling. It's a website, not an installation. And it's free, although they're talking about charging for a higher level.

chat.openai.com

So much has been written about its other capabilities and limitations that further explanation isn't required in here perhaps. But I wanted to explore the programming capabilities a little.

[p45cal]

In the attached, a function at cell C1 with hints as to what the arguments are:

2023-02-04_141928.png

It's a lambda function (see it in Name Manager), longhand developmental versions in cells J1:L1 (not needed, just for info). Not streamlined, just as I first got it to work.

Separately, minimal changes to your code:

Code: Select all

Dim i As Long
Dim word As String
Dim letters As String
Dim excludeLetters As String
Dim match As Boolean

lastRow = Range("A" & Rows.Count).End(xlUp).Row
letters = Range("B1").Value
excludeLetters = Range("B2").Value

For i = 1 To lastRow
  word = Range("A" & i).Value
  match = True
    
  For j = 1 To Len(letters)
    If InStr(word, Mid(letters, j, 1)) = 0 Then
      match = False
      Exit For
    End If
  Next j
    
  If match = True Then
    For j = 1 To Len(excludeLetters)
      If InStr(word, Mid(excludeLetters, j, 1)) > 0 Then
        match = False
        Exit For
      End If
    Next j
    
    If match Then Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = word
  End If
Next i

End Sub

[snb]

Written as UDF:

=F_snb(A1:A2309;B1;B2)

Code: Select all

Function F_snb(sn, c00, c01)
   sn = Application.Transpose(sn)
   
   For j = 1 To Len(c00)
     sn = Filter(sn, Mid(c00, j, 1))
     If j <= Len(c01) Then sn = Filter(sn, Mid(c01, j, 1), 0)
   Next
   
   F_snb = Join(sn, ", ")
End Function

[ChrisGreaves]

chat.openai.com

I admit that I am impressed.
Specifically, when I started (back in 2012?) The first code I wrote was the tape-management stuff, Left, Right, Print, and Read.
The suggestions 5,6 and 7 are "spot on".
Of course by the end of today I'll be no closer to completion.
I might go back and ask it for a decent way to implement subroutines in Turing Machines, see if it knows about single-character hyperlinks inWord2003 ...
Thanks for the lead
Chris