[VBA 3D] Fastest way to redraw big number of cells.

[MisiuR]

Hello forum,

My question is: do you know of any ways to redraw screen in excel at a constant rate of 30 times per second with lots of colored cells on screen?

I'm currently working on a 3d engine in excel that (aside from real-time 3d calculations) uses cells as pixels. With 255x255 resolution the framerate isn't sattisfactory enough though (I get 15-20 fps). Here you can see how my Graphics engine works:

Class_Initialize:

Public GameRange As Range
Public BackgroundColor As Integer
Public GroundColor As Integer

Private Sub Class_Initialize()On Error GoTo Errors


'Hard code GameRange to 255x255
'Because that's the only range we want to refresh
'And because that's our resolution
1 Set GameRange = Range(Cells(1, 1), Cells(255, 255))
2 GameRange.Rows.RowHeight = 1.5
3 GameRange.Columns.ColumnWidth = 0.25

'Excel settings
4 ActiveWindow.DisplayHeadings = False
5 ActiveWindow.DisplayGridlines = False
7 ActiveWindow.DisplayHorizontalScrollBar = False
8 ActiveWindow.DisplayOutline = False
10 ActiveWindow.DisplayVerticalScrollBar = False
12 ActiveWindow.DisplayWorkbookTabs = False
13 ActiveWindow.DisplayZeros = False
14 ActiveWindow.Zoom = 100
15 Application.DisplayFullScreen = True
16 Application.Calculation = xlCalculationManual
17 ActiveWindow.ScrollColumn = 1
18 ActiveWindow.ScrollRow = 1
19 Cells.Clear
20 BackgroundColor = 1
21 GroundColor = 51

Exit Sub
Errors:
MsgBox ("Graphics.Class_Initialize error: " & Err.Description & ". Error number: " & Err.Number & ". On line: " & Erl)

End Sub

And then the Graphical refresh procedure:

Public Sub RefreshScreen(ByRef GameObjects As Collection, ByRef x As Graphics, ByRef Col2d As Collection)On Error GoTo Errors


Dim GameObject As Object
Dim T As Double
Dim temp As Range


Application.ScreenUpdating = False


'Coloring the background
Range(Cells(1, 1), _
Cells(GameRange.Rows.Count / 2 - 10, GameRange.Columns.Count)).Interior.ColorIndex = BackgroundColor

Range(Cells(GameRange.Rows.Count / 2 - 10, 1), _
Cells(GameRange.Rows.Count, GameRange.Columns.Count)).Interior.ColorIndex = GroundColor

'Sorting so we have something like z-buffering effect here
Call CollectionSort(GameObjects)

'Refreshing 3d objects (painting unions of ranges)
For Each GameObject In GameObjects




GameObject.RedrawMe(x).Interior.ColorIndex = GameObject.mycolor


Next

'Painting 2d objects (copying sprites from another sheet)
For Each GameObject In Col2d


Call GameObject.RedrawMe



Next

'Applying changes to screen
Application.ScreenUpdating = True


Application.Calculate


Exit Sub


Errors:
MsgBox ("Graphics.RefreshScreen error: " & Err.Description & ". Error number: " & Err.Number & ".")


End Sub

The thing is that I want this procedure to take at most 0,03s (so I can run it 30 times per second). Calculations and stuff in it take almost no time, but "Application.ScreenUpdating = True" takes huuuge amount of time (0,05-0,10s). So my question is: do you know any alternatives to this method? I've tried doing stuff with WinAPI calls but I've found them either not working or being painfully slow.

Any help/suggestions are appreciated!

With regards,
Michal