Meaning of some VBA
Bob:
Thanks very much for your explanantion, it really opens my mind and now I
know better about this VBA. I am so amazed with all good poeple like you in
this forum. We, in the developping countiry are really helped by this forum.
Greetings from Jakarta, Indonesia.
--
H. Frank Situmorang
"Bob Phillips" wrote:
Frank,
When you record a macro, the recorder copie all actions, some of which may
be what you did but are not necessary for the job, and also takes a
necessarily singular, simplistic approach to it.
As such, statements like
ActiveWindow.SmallScroll Down:=72
are totally redundant in macros, and can be removed.
It tends to repeat things too, so you may see code like this repeated over
and over, which if needed at all is only neede once at the end
Application.CutCopyMode = False
When you set a value in the recorder, it records it in this manner
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
which can be reduced to
Range("N9").FormulaR1C1 = "=+R[1]C[-12]"
or even
Range("N9").Value= "=+R[1]C[-12]"
in other words, no need to select it.
And again, you rarely need to select, so instead of
Range("P9").Select
Selection.Copy
you can use
Range("P9")..Copy
and instead of
Range("Q9:X9").Select
ActiveSheet.Paste
you can use
Range("Q9:X9").Paste
or even better, combine the 4 lines into one, that is
Range("P9").Copy Range("Q9:X9")
which is simpler to read, and more efficient without the selecting.
Taking your code as an example this set, which is repeatable on different
ranges throughout your code
'Execute S20
Sheets("S20 (2)").Select
ActiveWindow.SmallScroll ToRight:=6
Range("N9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-12]"
Range("O9").Select
ActiveCell.FormulaR1C1 = "=+R[2]C[-13]"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=+R[1]C[-13]"
Range("P9").Select
Selection.Copy
Range("Q9:X9").Select
ActiveSheet.Paste
Range("N9:X11").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12:N1004").Select
ActiveSheet.Paste
Range("N9:X9").Select
ActiveWindow.SmallScroll Down:=72
Range("N9:X1002").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N9").Select
Application.CutCopyMode = False
can be reduced to
'Execute S20
Sheets("S20 (2)").Select
Range("N9").Value = "=+R[1]C[-12]"
Range("O9").Value = "=+R[2]C[-13]"
Range("P9").Value = "=+R[1]C[-13]"
Range("P9").Copy Range("Q9:X9")
Range("N9:X11").Copy Range("N12:N1004")
Range("N9:X1002").Value = Range("N9:X1002").Value
or even remove the sheet select by using with
'Execute S20
With Sheets("S20 (2)")
.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002").Value
End With
So overall we now have
Sub AutoShape2_Click()
'
' AutoShape2_Click Macro
' Macro recorded 5/2/2007 by Frank
' This is to modify the data presentation from columnwize to rowise, so
thatwe can sort
'
With Sheets("A00 (2)")
.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With
'Execute E00
With Sheets("E00 (2)")
.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With
'Execute E10
With Sheets("E10 (2)")
.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With
'Execute E20
With Sheets("E20 (2)")
.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With
'Execute E30
With Sheets("E30 (2)")
.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With
'Execute M00
With Sheets("M00 (2)")
.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With
'Execute M10
With Sheets("M10 (2)")
.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With
'Execute S10
With Sheets("S10 (2)")
.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002")
End With
'Execute S20
With Sheets("S20 (2)")
.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002").Value
End With
Sheets("SUMMARYWBLA").Select
Range("H2").Select
End Sub
But here we see the code is doing the same thing to different sheets, so it
would be better to put this into a separate variable function, like so
Private Function UpdateSheet(ByRef sh As Worksheet)
With sh
.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002").Value
End With
End Function
Which is called like so
Call UdateSheet(Sheets("S20 (2)"))
The whole code then reduces to
Sub AutoShape2_Click()
'
' AutoShape2_Click Macro
' Macro recorded 5/2/2007 by Frank
' This is to modify the data presentation from columnwize to rowise, so
thatwe can sort
'
Call UpdateSheet(Sheets("A00 (2)"))
Call UpdateSheet(Sheets("E00 (2)"))
Call UpdateSheet(Sheets("E10 (2)"))
Call UpdateSheet(Sheets("E20 (2)"))
Call UpdateSheet(Sheets("E30 (2)"))
Call UpdateSheet(Sheets("M00 (2)"))
Call UpdateSheet(Sheets("M10 (2)"))
Call UpdateSheet(Sheets("S10 (2)"))
Call UpdateSheet(Sheets("S20 (2)"))
Sheets("SUMMARYWBLA").Select
Range("H2").Select
End Sub
Private Function UpdateSheet(ByRef sh As Worksheet)
With sh
.Range("N9").Value = "=+R[1]C[-12]"
.Range("O9").Value = "=+R[2]C[-13]"
.Range("P9").Value = "=+R[1]C[-13]"
.Range("P9").Copy .Range("Q9:X9")
.Range("N9:X11").Copy .Range("N12:N1004")
.Range("N9:X1002").Value = .Range("N9:X1002").Value
End With
End Function
which is simpler, more readable, more maintaiabe, and faster.
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"Frank Situmorang" wrote in message
...
Thanks Bob for your great help. In fact my specialty is an accountant, so
I
am so thankful for your help.
|