Check VBA Macro (4)

Magpie Bavarde
Magpie Bavarde used Ask the Experts™
on
(And final for now as I dont want you to hate me for bothering you too much today ^^)

Hello the experts,

I'm not good with VBA but I use it a lot, I usually record some stuff then ask my friend  google about what I can't get recorded. I know all my macros are probably very weak but as long as I'm the only one at risk it's not an issue. But this time some colleagues are interested and I don't want to "sell" them a rotten file. So... Would you please have a look on my macros and let me know if you see something that should be improved so I can share my file with good chances it will work for other people ? Your corrections are obviously welcome, but also your advices to help me understand why I should do this and not do that...

Thank you very much for your help, Kind regards,

Mélanie

MACRO 1 : https://www.experts-exchange.com/questions/29166936/Check-VBA-macro-1.html
MACRO 2 : https://www.experts-exchange.com/questions/29166937/Check-VBA-Macro-2.html
MACRO 3 : https://www.experts-exchange.com/questions/29166939/Check-VBA-Macro-3.html

MACRO 4 : this is the specific macro for this specific file, basically it deletes this column, format this other one with help from the previous macros, and gets a lot of boring stuff does in a few secondes. It tooked 2mn to run when I did it first, then I improved it with some help here and there... And now it does the job in 3-5s. I dont copy it all, just the structure and a few examples so you can tell me what I should pay attention to and improve... Or what I should NEVER do like this... Thank you very much !!


Sub SoGe()
    '
    ' SoGe Macro
    '
    Range("A:A,B:B,H:H,S:S,U:U,V:V,AC:AC,AD:AD,AE:AE").Select
    Selection.Delete Shift:=xlToLeft
   
   Rows("3:3").Select
    Selection.Columns.AutoFit
	
    Columns("J:J").Select
    Selection.ColumnWidth = 30
    
    Range("B1:B500").Select
    Application.Run "PERSONAL.XLSB!FormatDate"
   
    Range("I1:I500").Select
    Application.Run "PERSONAL.XLSB!ToNumbers"
	
    Range("Q1:Q500").Select
    Application.Run "PERSONAL.XLSB!TextToNumbers"
    
    Columns("I:I").Select
    Selection.Cut

    Columns("H:H").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
	
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=RC[1] & "" ("" & RC[2] & "" "" & RC[3] & "")"""
   
   Range("H2").Select
    Selection.AutoFill Destination:=Range("H2:H500"), Type:=xlFillValues
   
    Range("I1:I500").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                           :=False, Transpose:=False
    Application.CutCopyMode = False
	
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "dpt"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "FR ou ET"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Pays"
    Range("M1").Select
	"... and so on
	
    Range("A1:U1").Select
    Selection.AutoFilter
    Application.ScreenUpdating = True

End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
I got rid of the Select statements, except those right before calling your other macros.

I assumed the purpose of the Paste followed by Paste Special was to replace the formulas in column I with the values they returned.
Sub SoGe()
    '
    ' SoGe Macro
    '
    Range("A:A,B:B,H:H,S:S,U:U,V:V,AC:AC,AD:AD,AE:AE").Delete Shift:=xlToLeft
   
    Rows("3:3").Columns.AutoFit
    
    Columns("J:J").ColumnWidth = 30
    
    Range("B1:B500").Select
    Application.Run "PERSONAL.XLSB!FormatDate"
   
    Range("I1:I500").Select
    Application.Run "PERSONAL.XLSB!ToNumbers"
    
    Range("Q1:Q500").Select
    Application.Run "PERSONAL.XLSB!TextToNumbers"
    
    Columns("I:I").Cut

    Columns("H:H").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
    
    Range("H2").FormulaR1C1 = "=RC[1] & "" ("" & RC[2] & "" "" & RC[3] & "")"""
   
    Range("H2").AutoFill Destination:=Range("H2:H500"), Type:=xlFillValues
   
    Range("I1:I500").Formula = Range("I1:I500").Value
    
    Range("J1").FormulaR1C1 = "dpt"
    Range("K1").FormulaR1C1 = "FR ou ET"
    Range("L1").FormulaR1C1 = "Pays"
    'Range("M1").Select
    '... and so on
    
    Range("A1:U1").AutoFilter

End Sub

Open in new window

Magpie BavardeExecutive Assistant

Author

Commented:
Thank you again byundt ! Could you please tell me briefly why it's better to avoid the "select" ?
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Any time you Select or Activate, VBA slows down until the selection can be rendered on your screen. If you do a lot of selecting, you will notice your screen flickering as a result.

By including a statement turning screen updating off (Application.ScreenUpdating = False) and avoiding the Select and Activate statements, you make the code go a lot faster.

I don't suggest turning screen updating off if the helper macro only does one thing. There's no advantage to it. I do suggest turning screen updating off if a macro does more than one thing, however. There's no need for the screen try to refresh before the macro is finished, and avoiding the screen refresh means the code runs much faster.

Unlike a number of other Excel experts, I do not turn screen updating back on at the end of my suggested code. Excel does this for you automatically when the last running macro returns control to the worksheet. The statement turning screen updating back on means one extra screen flicker, and an unnecessary one at that.

Like you, many people piece together a solution to their problem from questions posted on the internet. If you assemble a master macro that calls a number of helper macros, turning screen updating back on in the helper macros means your screen will be flickering many times before the master macro is finished. The master macro runs much slower as a result.

The better practice is to turn screen updating off only in your master macro. If the helper macros might be used by themselves, they should test whether screen updating is on before turning it off. You can do so with the following statement.
If Application.ScreenUpdating = True Then Application.ScreenUpdating = False

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial