Range.CopyPicture question

[Gokhan Aycan]

Is there a limit on how many rows that can be copied or an image size limit? Or some other setting etc I need to check?

When the range is 6000 rows by 20 columns, the resulting image is around 1600 rows (just short of 25000 points).

Essentially using:

rng.CopyPicture xlScreen, xlPicture

rng2.PasteSpecial

xlPrinter is even less, 200ish rows (I guess Print Area comes into play here).
xlBitmap has worse resolution and file size impact is almost double.

 

[Dan_W]

Most stuff I am doing atm is just some calculations/ratios and timing/optimizing the steps involved. I may end up using other forums as well for the png -> bmp part.

That's fair, but at the same time - you don't know what you don't know, and somebody just might chime in a helpful suggestion. Just a thought.

I will post how you can put a video on a userform with mci later when I have the time.

I think that would be really useful, thank you - perhaps as an article here on MrExcel.com?

 

[Gokhan Aycan]

you don't know what you don't know

Indeed.

 

[Gokhan Aycan]

I am marking this question as answered, since it is already verified that it depends. Thanks Dan and Jaafar for the input.

Update on testing GDI:

It seems reading stuff from class objects and collections can be slow. With class objects Property Get is slower than just reading the value directly.

I will most likely arrange everything into arrays (sacrificing readability) and see how that will work.

Just reading the variables from class object and collection takes 3+ ms each iteration, while the same amount of reads from an array is 0.08 ms. That would be around 300 reads. I had already reduced single row time down to 7-8 ms, if this test result holds true, a single row should be created in about 5 ms, which would be quite nice.

 

[Gokhan Aycan]

Using Jagged Arrays (array of arrays) indeed dropped single row operation times. Now it takes on average 4.3 ms rather than the previous 7-8ms.

Readability didn't get a lot worse either, as I am using enumerations for index values.

Using class object:

InstLaneWidth = Instruments(Instrument).Lanes(Lane).Width

Using jagged arrays:

InstLaneWidth = arrInstruments(iInst)(iLanes)(Lane, iWidth)

 

[Gokhan Aycan]

Further decreased per row calculations down to around 2 ms. Instead of copying Track_DC -> TrackCopy_DC -> Update the Copy - > Track_DC -> BitBlt on to form, I now alternate between 2 DCs. Current row's DC becomes the next one's CopyDC, and vice versa, thus eliminating 1 bitblt of the entire bitmap.

Another change is how I bitblt the result on to the form. It is now much smoother. Also, gave up on using sleep() as it was not accurate enough (Timer resolution was already 0.5 milliseconds due to opening song track as a video file). Instead using a for next loop with a simple SomeVar = SomeVar assignment within, and checking elapsed time. Pretty much 10-100 micro seconds accurate this way.

Last addition worthy to mention is video playback (will require a codec pack like K-Lite, disable video playback in the Settings sheet if it doesn't work!). I had hard time putting it on the userform. In my real project, I am using a separate window created with AutoIT. If I first put the video on it, then I can put it on the userform, otherwise it will just not work (I have no idea why). Even put the video on notepad, and then on to form once. But it is unreliable. So, I went with putting the video on the Excel window (for now) instead and placed it under a userform (make sure Excel window is high enough to show the video if you try this file).

Download link (27MB due to added video file)
GDrive - Downtown Train.7z

I didn't record a video for this one.

 

[Gokhan Aycan]

Deleted the file that was linked on previous posts. Latest version GDrive - Downtown Train.7z

 

[Dan_W]

Further decreased per row calculations down to around 2 ms. Instead of copying Track_DC -> TrackCopy_DC -> Update the Copy - > Track_DC -> BitBlt on to form, I now alternate between 2 DCs. Current row's DC becomes the next one's CopyDC, and vice versa, thus eliminating 1 bitblt of the entire bitmap.

Another change is how I bitblt the result on to the form. It is now much smoother. Also, gave up on using sleep() as it was not accurate enough (Timer resolution was already 0.5 milliseconds due to opening song track as a video file). Instead using a for next loop with a simple SomeVar = SomeVar assignment within, and checking elapsed time. Pretty much 10-100 micro seconds accurate this way.

Last addition worthy to mention is video playback (will require a codec pack like K-Lite, disable video playback in the Settings sheet if it doesn't work!). I had hard time putting it on the userform. In my real project, I am using a separate window created with AutoIT. If I first put the video on it, then I can put it on the userform, otherwise it will just not work (I have no idea why). Even put the video on notepad, and then on to form once. But it is unreliable. So, I went with putting the video on the Excel window (for now) instead and placed it under a userform (make sure Excel window is high enough to show the video if you try this file).

Download link (27MB due to added video file)
GDrive - Downtown Train.7z

I didn't record a video for this one.

I'm sorry, I didn't see that you had posted these updates. Can't wait to check it out.

 

[Gokhan Aycan]

Hey Dan, actually I have a newer test file (last video in the playlist on YT, link should be on an earlier post in the first page). That one is not yet on the drive. Currently there is only 1 userform, and I haven't bothered with fixing the video with that one. I took a break afterwards (3 weeks by now). What you can do with the latest is you can enter notes and sustains, and adjust the bpm values. Play a metronome click sound, and or tap a key (space bar) to help measure the bpm of a song (simple taking an average calculation).

 

[Dan_W]

Hey Dan, actually I have a newer test file (last video in the playlist on YT, link should be on an earlier post in the first page). That one is not yet on the drive. Currently there is only 1 userform, and I haven't bothered with fixing the video with that one. I took a break afterwards (3 weeks by now). What you can do with the latest is you can enter notes and sustains, and adjust the bpm values. Play a metronome click sound, and or tap a key (space bar) to help measure the bpm of a song (simple taking an average calculation).

So many bells and whistles! I'm easily distracted by bright shiny things!

Your note about the difficulties with the userform and the video sounds odd. What is it looking for? A hWnd property?

 

[Gokhan Aycan]

Yes, you send the hWnd of any window you want it on, even notepad works (until it is repainted). So the same hwnd (userform) works if I first put it on the AutoIT generated window, and then to the userform, but not if I try to put it on the userform directly. It beats the purpose of trying to eliminate the use of the said AutoIT executable though.

Edit: And somehow webm videos crash the app regardless of which window. I would need pro help on how to solve that one, as the problem is mci and k-lite related.