.Readme.md

This gisst was made in response to a reddit thread here. OP Claims that ChatGPT is good at writing basic VBA code if you give it clear instructions.

Explaining code in such a way that it codes the correct thing is pretty awful though, slow and tedious... Let's try to do that for stdTimer.

The prompt I used was as follows:

I want to write a predeclared VBA class. Next I want to add public event Tick with no parameters. Next I need a remote workbook property with events and a callback object. I want a Create method which acts as a constructor, which creates and initialises an object via the protInit method. The Create method should have params milliseconds and an optional callback of type stdICallable. In the initialisation method I need to set access to the VB object model to true, then create a new instance of Application and within this create a new workbook object. Within this workbook I want to inject some macro code from a getTimerCode() method including the milliseconds. getTimerCode should contain a string which Declares Sleep function for Mac 64-bit, Mac 32-bit, Windows 64-bit and Windows 32-bit. There should be a public boolean determining running state. In the MainLoop method we should loop while running, and modify a range A1 of the remote workbook. It's this event we will link to in our main file to emit the Tick event. After modifying the range we should sleep for the milliseconds and doevents. After the loop has finished we should close the workbook and quit the application. The injected code should also have a way of starting this loop (non-blocking) and a way of stopping the timer as 2 macros StartTimer and StopTimer. After injecting the code we should run the StartTimer macro remotely. As discussed above we should connect to the remote workbook, listen for change events and emit the Tick event and if a callback was provided we should call that as well. Would you be able to code this for me?

Chat GPT generated 2 responses and this was the code it generated.

Was it worth it? Not sure, maybe it would have been worth it to get some boiler plate down... On the other hand the prompt took a lot of writing.

ChatGPTTimerTest.cls

Private WithEvents wb As Workbook
Private WithEvents timerRange As Range
Private running As Boolean
Private milliseconds As Long
Private callback As stdICallable

Public Event Tick()

Public Sub Create(milliseconds As Long, Optional callback As stdICallable)
    Set wb = Workbooks.Add
    Set timerRange = wb.Sheets(1).Range("A1")
    Me.milliseconds = milliseconds
    Me.callback = callback
    protInit
End Sub

Private Sub protInit()
    Application.VBE.MainWindow.Visible = True
    Application.VBE.MainWindow.Visible = False
    Dim code As String
    code = getTimerCode(Me.milliseconds)
    With wb.VBProject.VBComponents(wb.Name).CodeModule
        .InsertLines 1, code
        Application.Run "StartTimer"
    End With
End Sub

Public Sub StartTimer()
    running = True
    MainLoop
End Sub

Public Sub StopTimer()
    running = False
    wb.Close SaveChanges:=False
    Application.Quit
End Sub

Private Sub MainLoop()
    Do While running
        timerRange.Value = Now
        DoEvents
        RaiseEvent Tick
        If Not callback Is Nothing Then
            callback.Call
        End If
        Application.Wait Now + milliseconds / 86400000#
    Loop
End Sub

Private Function getTimerCode(milliseconds As Long) As String
    getTimerCode = "Option Explicit" & vbCrLf
    getTimerCode = getTimerCode & "#If Mac Then" & vbCrLf
    getTimerCode = getTimerCode & "#If Win64 Then" & vbCrLf
    getTimerCode = getTimerCode & "Public Declare PtrSafe Sub Sleep Lib ""kernel32"" (ByVal dwMilliseconds As LongPtr)" & vbCrLf
    getTimerCode = getTimerCode & "#Else" & vbCrLf
    getTimerCode = getTimerCode & "Public Declare PtrSafe Sub Sleep Lib ""kernel32"" (ByVal dwMilliseconds As Long)" & vbCrLf
    getTimerCode = getTimerCode & "#End If" & vbCrLf
    getTimerCode = getTimerCode & "#Else" & vbCrLf
    getTimerCode = getTimerCode & "Public Declare Sub usleep Lib ""libc.dylib"" (ByVal useconds As Long)" & vbCrLf
    getTimerCode = getTimerCode & "#End If" & vbCrLf
    getTimerCode = getTimerCode & "Public Sub StartTimer()" & vbCrLf
    getTimerCode = getTimerCode & "    DoEvents" & vbCrLf
    getTimerCode = getTimerCode & "    Do While True" & vbCrLf
    getTimerCode = getTimerCode & "        DoEvents" & vbCrLf
    getTimerCode = getTimerCode & "        Call MainLoop" & vbCrLf
    getTimerCode = getTimerCode & "        Sleep " & milliseconds & vbCrLf
    getTimerCode = getTimerCode & "    Loop" & vbCrLf
    getTimerCode = getTimerCode & "End Sub" & vbCrLf
    getTimerCode = getTimerCode & "Public Sub StopTimer()" & vbCrLf
    getTimerCode = getTimerCode & "    running = False" & vbCrLf
    getTimerCode = getTimerCode & "End Sub" & vbCrLf
    getTimerCode = getTimerCode & "Private Sub MainLoop()" & vbCrLf
    getTimerCode = getTimerCode & "    Do While running" & vbCrLf
    getTimerCode = getTimerCode & "        Sheets(1).Range(""A1"").Value = Now" & vbCrLf
    getTimerCode = getTimerCode & "        DoEvents" & vbCrLf
    getTimerCode = getTimerCode & "        RaiseEvent Tick" & vbCrLf
    getTimerCode = getTimerCode & "        If Not callback Is Nothing Then" & vbCrLf
    getTimerCode = getTimerCode & "            callback.Call" & vbCrLf
    getTimerCode = getTimerCode & "        End If" & vbCrLf
    getTimerCode = getTimerCode & "#If Mac Then" & vbCrLf
    getTimerCode = getTimerCode & "            usleep " & milliseconds * 1000 & vbCrLf
    getTimerCode = getTimerCode & "#Else" & vbCrLf
    getTimerCode = getTimerCode & "            Sleep " & milliseconds & vbCrLf
    getTimerCode = getTimerCode & "#End If" & vbCrLf
    getTimerCode = getTimerCode & "    Loop" & vbCrLf
    getTimerCode = getTimerCode & "End Sub"
End Function
    

example.cls

Dim myTimer As New TimerClass

Private Sub Workbook_Open()
  myTimer.Create 1000
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  myTimer.StopTimer
End Sub

Private Sub myTimer_Tick()
  Debug.Print "Tick!"
End Sub