Mistery: Inputbox makes my code 1000 times slower

Hello

I have a macro that intends to copy the cells colors of selected range and place it somewhere else in the sheet

The code takes less than 1/10 of a second, but if I make an Inputbox appear it will take now several seconds....

This is my code:

Please Login or Register  to view this content.

STEPS:

1) select a big coloured range to copy, for example NK5:UR10
2) Run sub aaa from editor. It will copy the selected range to fixed cell MW22. It takes about 0.06 seconds to execute
3) Uncomment red line above:

Please Login or Register  to view this content.

This makes an Inputbox appear and you need to click on any cell, nothing else is done.
4) when you accept the inputbox, the code will take now several seconds



Help will be much appreciated

Many thanks!!

And in this way ?

Please Login or Register  to view this content.

Originally Posted by Special-K

Someone on another website had the same problem and said this

"i solved it by adding Application.ScreenUpdating = true before inputbox then again i added Application.ScreenUpdating = False after it now its not slowing down"

It improves from 10 seconds to about 6 seconds.... but still is 6 seconds agains 0.06 seconds when Inputbox is not used

Originally Posted by mikerickson

This (potential) loop could be shortened and the user interface made more like Microsoft's.

Please Login or Register  to view this content.

by

Please Login or Register  to view this content.

I agree and thanks for the suggestion.... but this particular line has no effect in my problem, is there just to prove that making a SET without Inputbox does not affect the execution time

Originally Posted by porucha vevrku

And in this way ?

Please Login or Register  to view this content.

Thanks for the suggestion... it did not affect at all.... Wjat is the purpose of the "(1)" at the end??

As above xlnitwit
Your order of events in the code:

1. ini = Strings.Format(Now, "HH:nn:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2)
2. Set desti = Application.InputBox(prompt:="Marca la primera cel·la de la nova ubicació", Type:=8)
3. fini = Strings.Format(Now, "HH:nn:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2)
4. MsgBox (ini & " ----> " & fini)

Originally Posted by xlnitwit

When I run that code in 2010 after making the changes mentioned by Special-K, it is only slower due to the time taken to show the inputbox, select a cell and press OK. The actual processing doesn't appear to be slower.

I think you might be selecting too little cells....

This is the excel file attached.

Could you please try by selecting range NK5:UR10 before running the macro. After the inputbox is closed, it takes about 10 seconds in my Excel 2013

Originally Posted by Zer0Cool

For me the same speed with or without the input box.

Be sure you change this:

Please Login or Register  to view this content.

when you uncomment the input box, otherwise its checking the old variable

Yes, thanks.... right now all I'm doing is pasting always in the same spot because I just wanted to know tha having a "SET" is not affecting

I have declared the desti and desti 2 as Range.... same result: 0.06 seconds when line is commented and 10 seconds when inputbox appears.... I am very puzzled that you don't have this problem.... have you used my excel file and selected big range such NK5:UR10??

HEllo ZeroCool... thanks so much for your suggestions

I think I did not explain my self properly

The main objective is to select a BIG range and copy it somewhere else, but ONLY cell fills and values, not borders. So the range to be copied is the selection (BIG) and the destination is indicated by a single cell (which will get the top-left cell of the big selection and copy the big range from there)

I tried to do it with loops and Cell(row,column) statements and was veeeery slow.... then I tried arrays and the speed was amazing, just instant.... but then when I try to indicate the destination single cell with Inputbox I get a slow execution but JUST making it appear, even if I don't use the result of the Inputbox.....

And this is what I want to figure it out

Thanks a lot

Originally Posted by Zer0Cool

Also stepping back and looking at your code, I am not sure I understand the need for the complexity.

Couldnt this be done without any arrays using 2 range objects and those properties since you are using selection or a continuous range of cells?

IE (psuedo code):

Please Login or Register  to view this content.

I just dont see the need for filling 4 arrays, getting dimensions, multiple nested loops, etc. If you want to make it easy for the user, you can have them select the top leftmost cell of the range they want to dump into, and then resize it to the dimensions of the source range.

range to range is much quicker than looping cells/ranges. Especially as the ranges get larger.

The code does not work as expected... it makes a cell white for some reason....

Originally Posted by Zer0Cool

The code was psuedo code, IE not functional. It was only meant to convey the method.

If possible Ill write something later to do this. The input box should have no impact on run speed what-so-ever except the time it takes to open, enter input in and hit ok. It doesnt seem any of us could duplicate the problem.

If you could give a bullet point list of what the macro needs to do, that may help in understanding it (In other words not explaining your code as it is, but explaining what you want the macro to do)

I tried this:

Please Login or Register  to view this content.

An this is what happens: the grey source turns to black

Using Ranges only.jpg

The whole objective is as follows:

Explanation.jpg

Good try Zer0Cool.... but as you can see in my screenshot above your post, my destination already has a different border layout... and the pastespecial will replace it with source layout and the xlNone will then erase, losing the original borders in the destination area....

I've come to learn than when multiple cells need to be processed, arrays are the way to speed up the execution dramatically..... my problem is that the InputBox introduces an amazing delay for no apparent reason

But if you can find a more direct way, be my guest

Have you tried my code in my excel file with the Inputbox? How fast is it for a big range (like the one in my shot) using Inputbox?

Originally Posted by xlnitwit

I have tested your original code in 2010 with the Inputbox enabled and it is no slower than without it.

Oh Dear God... I just tested it in different computer with Excel 2003 and it is not slower either !!!

Can anybody test with Excel 2013? This is the version I'm having problems with....

I don't even know where to start looking now....

It's 64 bits...