Towelmonkey
New Member
- Joined
- Jan 12, 2022
- Messages
- 7
- Office Version
- 2016
- 2010
- Platform
- Windows
tl;dr I would like to know:
1. Just how fast/slow is Excel at updating colours and values of hundreds of cells at once? What is causing the disproportionate results in my experiment?
2. Is there any way to speed this process up, so that it is feasible to turn Excel into a pixel display?
3. Are there any libraries/references that I can use to create standalone windows and do stuff with them, possibly through Shell commands?
I am a beginner programmer currently using VBA to write a tetris game in Excel. (Please don't ask me to switch to another language - there is a work-related reason why I have to use VBA) Currently, every cell in my sheet is also a cell of each tetris piece, meaning I only have a 10×24 range of cells to worry about. I was inspired by another excel game, "Dave vs. Ziggy", which is a fully functional first-person shooter that is able to run at 15FPS on an approximately 100×60 "screen". (You can google it online.)
I have been experimenting with "upgrading" my game "screen" to a higher "resolution" (320×135 cells). I tested the speed at which Excel can colour in a diagonal-running pattern of the 16 QBColours, using a For loop and Select Case to add cells one by one to one of 16 ranges based on their colours.
I got the following results:
- a 20×20 range takes 0.03125s
- a 40×40 range takes 0.5625s
- a 60×60 range takes around 3s
- an 80×80 range takes more than 15s
As can be seen, the increase is highly disproportionate.
- four 20×20 ranges take 0.12s, as expected
- nine 20×20 ranges take 0.3s, slightly higher than expected
- sixteen 20×20 ranges take 0.7s, even higher than expected
The best result I could get for the whole board is to colour twenty-five 64×35 ranges, which takes around 1.1s (i.e. the FPS of my game would be around 0.9). In comparison, my current 10×24 screen takes 0.03125s to run each game loop (for an FPS of 30). However, the game is extremely barebones...
I tried decrypting the code in Dave vs. Ziggy. The programmer used a larger colour palette (around 50 colours IIRC), and used a similar method in his code, except he seemed to fill in columns of cells with the same colours first. (The code is extremely dense D: ) He was somehow able to achieve a stunning 15FPS.
I would appreciate if some VBA pros here could take the time to explain to me how all this works, and why Excel seems to be so slow graphically. I have also noticed that, even after doing a Range.Clear, Excel lags whenever I switch back to the "game screen" from another worksheet, presumably because there are so many cells being displayed at once - although why would it lag if all the cells are empty?
Finally, I would really appreciate if some VBA guru could answer the 3rd question above about libraries. My motivation for "upgrading" the game screen is to enable cool visual effects (like explosions every time the player scores a line clear) and animations (like a moving background as the game is being played, much like in Tetris Effect). I know that C# handles such things relatively efficiently, and I also read somewhere that the Excel application itself slows VBA down by a bit, so I would love it if there are any built-in libraries that could allow me to bypass Excel entirely.
Thank you all!
P.S. I would also love to know if VBA code has a "unit" of time. I have only ever seen multiples of 0.015625s when running my code.
1. Just how fast/slow is Excel at updating colours and values of hundreds of cells at once? What is causing the disproportionate results in my experiment?
2. Is there any way to speed this process up, so that it is feasible to turn Excel into a pixel display?
3. Are there any libraries/references that I can use to create standalone windows and do stuff with them, possibly through Shell commands?
I am a beginner programmer currently using VBA to write a tetris game in Excel. (Please don't ask me to switch to another language - there is a work-related reason why I have to use VBA) Currently, every cell in my sheet is also a cell of each tetris piece, meaning I only have a 10×24 range of cells to worry about. I was inspired by another excel game, "Dave vs. Ziggy", which is a fully functional first-person shooter that is able to run at 15FPS on an approximately 100×60 "screen". (You can google it online.)
I have been experimenting with "upgrading" my game "screen" to a higher "resolution" (320×135 cells). I tested the speed at which Excel can colour in a diagonal-running pattern of the 16 QBColours, using a For loop and Select Case to add cells one by one to one of 16 ranges based on their colours.
I got the following results:
- a 20×20 range takes 0.03125s
- a 40×40 range takes 0.5625s
- a 60×60 range takes around 3s
- an 80×80 range takes more than 15s
As can be seen, the increase is highly disproportionate.
- four 20×20 ranges take 0.12s, as expected
- nine 20×20 ranges take 0.3s, slightly higher than expected
- sixteen 20×20 ranges take 0.7s, even higher than expected
The best result I could get for the whole board is to colour twenty-five 64×35 ranges, which takes around 1.1s (i.e. the FPS of my game would be around 0.9). In comparison, my current 10×24 screen takes 0.03125s to run each game loop (for an FPS of 30). However, the game is extremely barebones...
I tried decrypting the code in Dave vs. Ziggy. The programmer used a larger colour palette (around 50 colours IIRC), and used a similar method in his code, except he seemed to fill in columns of cells with the same colours first. (The code is extremely dense D: ) He was somehow able to achieve a stunning 15FPS.
I would appreciate if some VBA pros here could take the time to explain to me how all this works, and why Excel seems to be so slow graphically. I have also noticed that, even after doing a Range.Clear, Excel lags whenever I switch back to the "game screen" from another worksheet, presumably because there are so many cells being displayed at once - although why would it lag if all the cells are empty?
Finally, I would really appreciate if some VBA guru could answer the 3rd question above about libraries. My motivation for "upgrading" the game screen is to enable cool visual effects (like explosions every time the player scores a line clear) and animations (like a moving background as the game is being played, much like in Tetris Effect). I know that C# handles such things relatively efficiently, and I also read somewhere that the Excel application itself slows VBA down by a bit, so I would love it if there are any built-in libraries that could allow me to bypass Excel entirely.
Thank you all!
P.S. I would also love to know if VBA code has a "unit" of time. I have only ever seen multiples of 0.015625s when running my code.
