How to call a VBA function

[jbunn]

Hi There -

I have the following repetitive code which I use to calculate differences between various columns. Any ideas how I can simply the code or setup a function and call it within my code?

'Add Column and Difference
    Range("E:E").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveSheet.Range("E3").Select
    Selection.FormulaR1C1 = "Difference"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A25000")
    Range("F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
'Add Column and Difference
    Range("I:I").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveSheet.Range("I3").Select
    Selection.FormulaR1C1 = "Difference"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A25000")
    Range("J:J").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

 

[Jerry Sullivan]

Hi jbunn,

Working with your existing code, you could do something like this....

Sub Main()
 Call InsertCopy(lColumn:=5)
 Call InsertCopy(lColumn:=8)

'---the keyword "Call" is optional and used for clarity
End Sub

Private Sub InsertCopy(lColumn As Long)
 Columns(lColumn).Select
 Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
 ActiveSheet.Cells(3, lColumn).Select
 Selection.FormulaR1C1 = "Difference"
 ActiveCell.Offset(1, 0).Range("A1").Select
 ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
 ActiveCell.Select
 Selection.AutoFill Destination:=ActiveCell.Range("A1:A25000")
 Columns(lColumn + 1).Select
 Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

However, it's much more efficient to just reference the objects directly without Selecting them.
Here's code that will arrive at the same result without Selecting any Cells...

Private Sub InsertCopy(lColumn As Long)
 Columns(lColumn).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
 Cells(3, lColumn).Formula = "Difference"
 Cells(4, lColumn).Resize(25000).FormulaR1C1 = "=RC[-2]-RC[-1]"
 Columns(lColumn + 1).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

 

[jbunn]

thanks Jerry -

I ended up doing a For Loop to simplify my code

For i = 5 To 105 Step 4

Columns(i).Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbov
ActiveSheet.Cells(3, i).Select
Selection.FormulaR1C1 = "Difference"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A25000")
Columns(i + 1).Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbov


Next i