VBA to programatically draw 3D shapes (slow!!!)

[GBagley]

Disclaimer: I fully realize Excel was not exactly designed to do what I'm trying to do with it, but that's what makes this fun!

I've built a program in Excel/VBA that takes 3D (x,y,z) plot points and programatically draws them on the screen after converting to 2D based on a rotation/shift matrix along with angles and viewer placement, etc. Fun stuff.

The problem is that it's slow! The method that I'm using basically loads 4 plot points into an array (the 4 corners of a square) and then draws the shape with this:

ActiveSheet.Shapes.AddPolyline(polylineArray)

Works great, but using this to draw the 6 faces of a cube takes about 150 milliseconds (timing that one line, specifically -- by far the most time consuming portion of all my subroutines in this program). And I need it to draw hundreds or thousands of cubes, which can take minutes.

Any tricks to speed this up? Already have screen updating off, and I've tried drawing more basic lines instead of polylines, no help there. Seems like Excel is just slow when working with drawing objects.

 

[Jerry Sullivan]

Your result of 150 milliseconds to execute this statement seems slower than it should be for your description.

ActiveSheet.Shapes.AddPolyline(polylineArray)

Are you isolating just that statement and not counting the time it takes to calculate the verticies?

I did a few tests drawing 10,000 squares using this statement and the results were pretty consistently about 4 seconds or 0.4 milliseconds per shape.

The results were the about the same for filled, unfilled, closed, open and longer polylines(with 10 verticies)

Can you post the code you are using for your test that results in 150 milliseconds per shape?

 

[GBagley]

Your result of 150 milliseconds to execute this statement seems slower than it should be for your description.

ActiveSheet.Shapes.AddPolyline(polylineArray)

Are you isolating just that statement and not counting the time it takes to calculate the verticies?

I did a few tests drawing 10,000 squares using this statement and the results were pretty consistently about 4 seconds or 0.4 milliseconds per shape.

The results were the about the same for filled, unfilled, closed, open and longer polylines(with 10 verticies)

Can you post the code you are using for your test that results in 150 milliseconds per shape?

Interesting...

I did have some other things wrapped in the timer. If I wrap just that one line in the timer, then I get an average of about 20 milliseconds. BUT, if I create a new module that only loops through that same line 1000 times, then I get an average of 0.6 milliseconds. It's the exact same line that is getting timed, it's just so much slower in the context of my actual program.

Could my full program be bogging down processing speed that much?

I'll keep digging into it. Maybe I can pre-load all the verticies and then run a separate program just to draw it all out.

 

[GBagley]

I did a lot of testing with this and finally discovered...something. The sheet that I've been doing all the drawings with seems to be the problem. After I'm done with each drawing, I clear the shapes with this:

Sub Clear_All()
Dim Sh As Shape

        For Each Sh In ActiveSheet.Shapes
            Sh.Delete
        Next Sh

End Sub

But there must be some other cache of the shapes left in memory? My test code is below. Try running this on a sheet, and then calling the Clear_All sub above to clear the shapes. Then run the test code below again. If you keep repeating that, the times increase with each subsequent run. If I create a new sheet, and run the test code there, then it's blazing fast again.

Sub test_polyline()

Dim cell As Range
Dim polyLineArray(1 To 5, 1 To 2) As Single
Dim CTimer As C_Timer

Set CTimer = New C_Timer
Application.ScreenUpdating = False

    polyLineArray(1, 1) = 100
    polyLineArray(1, 2) = 100
    polyLineArray(2, 1) = 100
    polyLineArray(2, 2) = 100
    polyLineArray(3, 1) = 100
    polyLineArray(3, 2) = 100
    polyLineArray(4, 1) = 100
    polyLineArray(4, 2) = 100
    polyLineArray(5, 1) = 100
    polyLineArray(5, 2) = 100

For Each cell In Sheets("plot_points").Range("AS1", "AS1000")
        CTimer.StartCounter
        ActiveSheet.Shapes.AddPolyline (polyLineArray)
        Debug.Print CTimer.TimeElapsed
Next cell

End Sub

Saving the worksheet, closing, and re-opening it has no effect. Same slow speed on the Sheet that I normally draw to. So my question is, where is this hidden cache of previously drawn shapes, and how can I destroy it?

Alternatively, I can just programatically create a new sheet for each new drawing, and then delete the sheet when I'm done with it. Not ideal, but it's a workaround.

Thanks for the sanity check that led me to look into this further!!

 

[Jerry Sullivan]

That's pretty interesting. Do you notice a significant difference in the file size of files that are running sluggishly after creating/ deleting shapes?

 

[GBagley]

That's pretty interesting. Do you notice a significant difference in the file size of files that are running sluggishly after creating/ deleting shapes?

It's a 50mb file (big, I know; mostly data though). I tried deleting the sheet that I always draw to and re-saving. Only decreased the file size by about 1.5mb. There was a good chunk of non-shape data on that sheet also though, so it doesn't seem like there are a bunch of invisible shapes taking up space.