Hi there, Superbead here –
I need Very Fast Redraw in Excel for game animation purposes, which I understand's a big ask, although I'm reasonably sure I'm not too far away from achieving it. I'm using VBA for Excel 2007 on an XP SP3 machine. Basically I need to alternate the colours of a couple of thousand cells in the time of an animation frame (less than 1/10s) without any flicker or stutter.
I've tried switching Application.Screenupdating in the game loop, and while it's False the cell colours update fantastically quickly, but the very switching of this application variable every frame introduces a significant delay. My idea was to turn .Screenupdating off at the very beginning, and then force repaints of the Workbook window with WinAPI calls each frame to avoid the overhead of accessing the Application object.
Here's an demo which attempts to use WM_SETREDRAW, InvalidateRect() and UpdateWindow() to alternate the colour of cell 10,10 between red and green. I say 'attempts', as it doesn't work, and I can't work out why.
(Test() is called from, let's say, Worksheet_Activate.)
Supposedly, WM_SETREDRAW with wParam=True allows redrawing, InvalidateRect() tells the window its whole client area needs repainting on the next WM_PAINT message, and UpdateWindow() sends that WM_PAINT message.
However – the cell never appears to change colour. MS Spy++ confirms I've got the correct hWnd for the Workbook window, but the only WM_PAINT message received is when the code exits (and presumably Application.Screenupdating is reset to True). The WM_SETREDRAW on and off messages are shown in Spy++ as received, though.
Strangely, sending WM_PAINT after setting WM_SETREDRAW true does result in the Workbook window receiving paint messages, but regardless the window is never repainted, and the cell still cannot be seen to change colour. I'm aware you're not supposed to send WM_PAINT to other processes, at least not according to the XP SDK, but I thought I'd give it a crack.
Has anyone any ideas as to why UpdateWindow isn't working? Or even, a better suggestion? (PS. I've already tried palette rotation with both Excel and GDI, and that's a no-go.)
Regards – SB
The background:
I'm porting Jet Set Willy (an old 8-bit 1980s platform game) from the ZX Spectrum to Excel. My graphics 'engine' manipulates the interior colours of shrunken cells to simulate the Spectrum's 256x192px display. Although I've exceeded my expectations of how far I'd get in terms of smooth drawing and animation, I'm having trouble with a particularly slow-to-draw element. In case anyone remembers it, it's the Speccy's FLASH attribute which exchanged the background and foreground colours of 8x8px cells about three or four times a second. No matter how super-efficient my drawing routines are, the 'wash' effect can easily be seen when redrawing thousands of cells at once.
I need Very Fast Redraw in Excel for game animation purposes, which I understand's a big ask, although I'm reasonably sure I'm not too far away from achieving it. I'm using VBA for Excel 2007 on an XP SP3 machine. Basically I need to alternate the colours of a couple of thousand cells in the time of an animation frame (less than 1/10s) without any flicker or stutter.
I've tried switching Application.Screenupdating in the game loop, and while it's False the cell colours update fantastically quickly, but the very switching of this application variable every frame introduces a significant delay. My idea was to turn .Screenupdating off at the very beginning, and then force repaints of the Workbook window with WinAPI calls each frame to avoid the overhead of accessing the Application object.
Here's an demo which attempts to use WM_SETREDRAW, InvalidateRect() and UpdateWindow() to alternate the colour of cell 10,10 between red and green. I say 'attempts', as it doesn't work, and I can't work out why.
(Test() is called from, let's say, Worksheet_Activate.)
Code:
Option Explicit
Option Base 0
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" ( _
ByVal hWndParent As Long, _
ByVal hWndChildAfter As Long, _
ByVal lpszClass As String, _
ByVal lpszWindow As String _
) As Long
Private Declare Function InvalidateRect Lib "user32" ( _
ByVal hWnd As Long, _
ByRef lpRect As Long, _
ByVal bErase As Long _
) As Long
Private Declare Function UpdateWindow Lib "user32" (ByVal hWnd As Long) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" ( _
ByVal hWnd As Long, _
ByVal Msg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long _
) As Long
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Const WM_SETREDRAW As Long = &HB&
' #######################################
Private Function GethWndWorkbook() As Long
Dim hWndXLDESK As Long
hWndXLDESK = FindWindowEx( _
Application.hWnd, _
0, _
"XLDESK", _
vbNullString)
GethWndWorkbook = FindWindowEx( _
hWndXLDESK, _
0, _
vbNullString, _
ActiveWorkbook.Name)
End Function
' #######################################
Private Sub ScreenUpdate(hWnd As Long, bState As Boolean)
Dim lResult As Long
If bState Then
lResult = SendMessage(hWnd, WM_SETREDRAW, 1&, 0&)
lResult = InvalidateRect(hWnd, 0&, 1&)
lResult = UpdateWindow(hWnd)
Else
lResult = SendMessage(hWnd, WM_SETREDRAW, 0&, 0&)
End If
End Sub
' #######################################
Public Sub Test()
Dim hWnd As Long
Dim I As Long
hWnd = GethWndWorkbook
Application.ScreenUpdating = False
For I = 1 To 100
ScreenUpdate hWnd, False
Worksheets(1).Cells(10, 10).Interior.Color = RGB(255, 0, 0)
ScreenUpdate hWnd, True
Sleep 10
ScreenUpdate hWnd, False
Worksheets(1).Cells(10, 10).Interior.Color = RGB(0, 255, 0)
ScreenUpdate hWnd, True
Sleep 10
Next I
End SubHowever – the cell never appears to change colour. MS Spy++ confirms I've got the correct hWnd for the Workbook window, but the only WM_PAINT message received is when the code exits (and presumably Application.Screenupdating is reset to True). The WM_SETREDRAW on and off messages are shown in Spy++ as received, though.
Strangely, sending WM_PAINT after setting WM_SETREDRAW true does result in the Workbook window receiving paint messages, but regardless the window is never repainted, and the cell still cannot be seen to change colour. I'm aware you're not supposed to send WM_PAINT to other processes, at least not according to the XP SDK, but I thought I'd give it a crack.
Has anyone any ideas as to why UpdateWindow isn't working? Or even, a better suggestion? (PS. I've already tried palette rotation with both Excel and GDI, and that's a no-go.)
Regards – SB
The background:
I'm porting Jet Set Willy (an old 8-bit 1980s platform game) from the ZX Spectrum to Excel. My graphics 'engine' manipulates the interior colours of shrunken cells to simulate the Spectrum's 256x192px display. Although I've exceeded my expectations of how far I'd get in terms of smooth drawing and animation, I'm having trouble with a particularly slow-to-draw element. In case anyone remembers it, it's the Speccy's FLASH attribute which exchanged the background and foreground colours of 8x8px cells about three or four times a second. No matter how super-efficient my drawing routines are, the 'wash' effect can easily be seen when redrawing thousands of cells at once.
