Skip to content

Instantly share code, notes, and snippets.

@sancarn
Last active March 2, 2023 20:39
Show Gist options
  • Save sancarn/65113dedf664a44f451ee4267c84671f to your computer and use it in GitHub Desktop.
Save sancarn/65113dedf664a44f451ee4267c84671f to your computer and use it in GitHub Desktop.

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.

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
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment