Macro that inserts shapes gets progressively slower
I hope this message finds all of you well. I have been working on a macro to make entomological specimen labels that come with an embedded QR code such that museum curators can scan the QR codes of a series of specimens in the same group and easily manipulate their data. The QR code images are inserted as "shapes" (I believe--they respond to shape commands in the macro), and are generated via the brilliant pure-VBA script by Jiri Gabriel, with editing by Jonas Heidelberg (I am not allowed to post urls because I'm new; but if you search "barcode-vba-macro-only" on github you will find the code). The basic premise of the macro is that it takes some data, populates a bunch of cells with strings and values (i.e., what gets printed on the human-readable part of the individual labels), and then when all of the printed text is inserted, the macro iteratively generates one QR code image at a time and places each generated image next to the corresponding human-readable label. My apologies for not being able to post an image, as I haven't been on the board long enough to be allowed to attach files to my posts...
The issue I'm running into is that the macro is quick to generate and insert the first few QR code images, and then gets progressively slower, and slower, and slower with each successive image--I presume because Excel is not built to handle a large number of high-resolution images on the same spreadsheet. My current sheet design accommodates 220 individual QR code images, but it takes nearly 10 minutes just to populate the spreadsheet with 50 QR code images (on the other hand it takes less than 30 seconds to populate with 10 QR code images, so the slowdown is appreciable).
Is there something I can do on the macro end to make the process not run so slowly? Things I have tried are as follows:
1. Disable screen updating - does not seem to improve the processing speed
2. Set calculation to manual - does not seem to improve the processing speed
3. After generating each QR code image, hide the image by using the following code, and then at the very end, turn all the images visible - seems to help a little bit but not nearly sufficient to make the macro usable at scale.
Selection.ShapeRange.Visible = False
I've looked for solutions to see if all of the QR code image shapes can be merged into one shape, because after all, wouldn't it be easier to manage a single shape than 200+ individual small shapes? But alas there seems to be no functionality to combine all of the shapes into a single shape. Another solution I had thought about is if the macro can simultaneously generate all of the QR codes at once, instead of iteratively, then perhaps it won't have the issue of the later-coming shapes being so slow to render due to having to hold all of the previously rendered codes in its memory. But I haven't found a way to write the code such that all QR code image shapes are generated in parallel, rather than in sequence. Yet another solution I toyed with is to paste the shapes as PNG or some other image that could potentially be easier to deal with, but I get a lot of loss of quality, which seems strange because the QR code should be just a matrix of black and white cells, right? Why do they lose so much quality?
At any rate, any advice would be greatly appreciated! Thank you so much in advance!
Also, I recognize that this question may pertain to how Excel functions in general, but since I'm facing this issue in the context of a macro, I'm posting it here. If it should be posted in the Excel General board, please let me know and I will post the question there instead.
Thank you very much!
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By pongmeister in forum Excel Formulas & Functions
Last Post: 05-02-2020, 06:23 PM
By Nicholaes in forum Excel Programming / VBA / Macros
Last Post: 10-29-2019, 06:33 PM
By maruthu22 in forum Excel Programming / VBA / Macros
Last Post: 08-17-2017, 12:36 AM
By AliJay in forum Excel Programming / VBA / Macros
Last Post: 06-20-2017, 01:31 AM
By jj4jj in forum Excel Programming / VBA / Macros
Last Post: 07-07-2016, 10:26 AM
By swoop99 in forum Excel Programming / VBA / Macros
Last Post: 06-24-2011, 06:41 AM
By whitespaces in forum Excel Programming / VBA / Macros
Last Post: 07-15-2009, 03:29 AM
Tags for this Thread
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1