VBA is performing unexpectedly slow!!

Topic

New Reply

Hi All,

I need to do things repeatedly in my working file. I have recorded and added some VBA in my excel file. But unfortunately, it is performing too much slow which is totally unexpected. I think the operation would be accelerated by doing something in the file.

Could you please check and help me to sort out the problem?

Thanks in advance.

Regards

Shazzad

Reply | Quote

Replies

Shazzad,

It’s a bit hard to offer assistance w/o some additional information.

How many rows & columns does your worksheet use?
Do you have more than one worksheet in the workbook?
Could you please post the code that is running slow?

HTH

 

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Don’t whats going wrong . Cannot attach file.

Reply | Quote

At bottom left under your post you should see SELECT FILE.

Click on that,

Then select the file, by double clicking on the file it will attach, but ..

you have to then select if you want it as an attachment or to INSERT in text of post.

 

Reply | Quote

After clicking on insert into content, I have clicked on submit.

Therefore, it is submitted without any attachment.

Reply | Quote

File details:

 

Reply | Quote

I tries and got this message.

1 user thanked author for this post.

Bluetrix

Reply | Quote

Not all file types are allowed, that one apparently isn’t. Size is another thing that will stop an attachment.

If you have a template  of the file opened (and empty), you can capture that and post it.

RG asked for the “code” not the file anyway. Do you know how to view/copy the code?

If not RG may be able to assist you doing that.

1 user thanked author for this post.

shazzad.arla

Reply | Quote

Kirsty wrote:

As you are unable to upload your macro-enabled spreadsheet, for security reasons, why don’t you post a link to a cloud source of the file instead?

Thanks Kristy. i was actually not sure whether any cloud link would be allowed or not. but getting confirmation from you, i will do that.

Bluetrix wrote:

I’m not positive but I think because that file has an executable in it, that isn’t allowed.

RetiredGeek (RG) wants the code, not the file.

Yea I am gonna share the code.

 

RetiredGeek wrote:

Shazzad,

It’s a bit hard to offer assistance w/o some additional information.

How many rows & columns does your worksheet use?
Do you have more than one worksheet in the workbook?
Could you please post the code that is running slow?

HTH

 

Here is my code:

Sub Run_DIO()
'
' Run_DIO Macro
'
' Keyboard Shortcut: Ctrl+q
'

ActiveCell.GoalSeek Goal:=ActiveCell.Offset(-5, -1), ChangingCell:=ActiveCell.Offset(-4, -1).Range( _
"A1")
ActiveCell.Offset(-3, -1).Range("A1").Select
Selection.Copy
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(4, 2).Range("A1").Select
End Sub

There is another macro to run the above code for 90 times that is my focus to run faster. but it seemed to run too much slowly than usual.

Here is the file link

 

Regards

Shazzad

Reply | Quote

Could you please check this out ??

Sub Run_DIO()
‘
‘ Run_DIO Macro
‘
‘ Keyboard Shortcut: Ctrl+q
‘

ActiveCell.GoalSeek Goal:=ActiveCell.Offset(-5, -1), ChangingCell:=ActiveCell.Offset(-4, -1).Range( _
“A1”)
ActiveCell.Offset(-3, -1).Range(“A1”).Select
Selection.Copy
ActiveCell.Offset(-1, 0).Range(“A1”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(4, 2).Range(“A1”).Select
End Sub

 

 

Thanks.

Reply | Quote

Hi Bluetrix,

is there any restriction imposed on .xlsm formatted file??

I didn’t know actually.

 

Reply | Quote

shazzad.arla wrote:

is there any restriction imposed on .xlsm formatted file??

Appears that this is one of the many places where those aren’t allowed as is, right now. I wouldn’t know if this is on purpose or an automated filter inherited from somewhere.

I do know that .xlsm is one of the file types most likely to be blocked outright on the ‘net… guess there’s been a few too many of the macro malware scares.

Reply | Quote

I’m not positive but I think because that file has an executable in it, that isn’t allowed.

RetiredGeek (RG) wants the code, not the file.

1 user thanked author for this post.

Kirsty

Reply | Quote

As you are unable to upload your macro-enabled spreadsheet, for security reasons, why don’t you post a link to a cloud source of the file instead?

1 user thanked author for this post.

shazzad.arla

Reply | Quote

Here is the code

Sub Run_DIO()
‘
‘ Run_DIO Macro
‘
‘ Keyboard Shortcut: Ctrl+q
‘

ActiveCell.GoalSeek Goal:=ActiveCell.Offset(-5, -1), ChangingCell:=ActiveCell.Offset(-4, -1).Range( _
“A1”)
ActiveCell.Offset(-3, -1).Range(“A1”).Select
Selection.Copy
ActiveCell.Offset(-1, 0).Range(“A1”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(4, 2).Range(“A1”).Select
End Sub

 

File link is here.

Reply | Quote

The Goal Seek operation can take arbitrarily long.  It can even take an infinite amount of time if the goal cannot be reached.  That is the most likely culprit.

Apart from that, you are constantly using ActiveCell, Select, Copy and Paste, which violates best practices for writing macro code.  You can get that kind of code using the macro recorder, but it is fragile and prone to error.

Reply | Quote

Shazzard,

I downloaded your file but the copy you uploaded is broken as I get an error when I click the Run DIO button, on the first line of the macro.

I tried running the Set DIO macro, via button, but it only opened the macro in the VBE.

Some general problems I found:

• The Run_DIO macro depends on the location of the cursor (ActiveCell). This is a bad idea, especially since you are copying & pasting on this value.
• It’s obvious that most of the code was generated using the Record Macro feature as it has a lot of superfluous code which of course can slow down your operations.

In Module1 you can replace a lot of code as follows:

Sub Run_DIO_All()
'
' Run_DIO_All Macro
'
Dim iCntr As Integer
'
   For iCntr = 1 To 90
      Run_DIO
   Next iCntr
   
    ActiveWindow.SmallScroll Down:=-18
    ActiveCell.Offset(11, -90).Range("A1").Select
    
End Sub

In Module 3 you can eliminate some of what Brian was talking about with this code:

Sub Run_DIO()

ActiveCell.GoalSeek Goal:=ActiveCell.Offset(-5, -1), _
      ChangingCell:=ActiveCell.Offset(-4, -1)
Range(ActiveCell.Offset(-3, -1), ActiveCell).Copy
ActiveCell.Offset(-1, 0).PasteSpecial Paste:=xlPasteValues, _
                                                  Operation:=xlNone, _
                                                  SkipBlanks:=False, _
                                                  Transpose:=False
ActiveCell.Offset(4, 2).Select
End Sub

Unfortunately, I can’t test this code since I don’t know how to run your program, e.g. where to place the cursor. However I ran a test on the copy/paste part on a test workbook with numbers in Col A and the cursor in B4 with this result:

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

1 user thanked author for this post.

shazzad.arla

Reply | Quote

Hi RG,

Sorry for late response since I was on vacation. Thanks for your effort. It has fastened the process a bit. Actually I am not a pro coder. but try to write with some online help and recording. Thanks again

Shazzad

Reply | Quote