CODEWITHSUNDEEP
excelstockvba
Cortez Ninja
Cortez Ninja

Reputation: 89

Slower performance of calculation in Excel VBA

I am currently calculating Returns on Stock investment. I have historical data for round about 10 years and the way I've constructed my function is taking to long to perform the job. For e.g I've got 11 columns and 2872 rows to calculate the returns for each day.

my Function
     Public Sub CalcROI(ByVal ColPick As Integer, ByVal ColPrint As Integer)

     Dim irow As Integer
     Dim iCol As Integer

     For irow = 4 To 2873
    'Calculating ROI
      Cells(irow + 1, ColPrint).Value = (Cells(irow + 1, ColPick).Value - Cells(irow, ColPick).Value) / Cells(irow, ColPick).Value
      Next irow

      End Sub

and the implementation of the procedure is

 CalcROI ColPick:=4, ColPrint:=17

ColPick - From where Value need to pick for calculation

ColPrint - on the Column its need to print output

Upvotes: 1

Views: 715

Answers (2)

enderland
enderland

Reputation: 14145

If you don't want to change your current code, utilizing Application.ScreenUpdating will help make this (as well as making nearly all your future Excel VBA code run faster).

Public Sub CalcROI(ByVal ColPick As Integer, ByVal ColPrint As Integer)
     'this stops Excel from updating the screen after every single iteration in your loop
     'in the future, this is an EASY way to speed up the majority of Excel macros
     Application.screenupdating = false  
     Dim irow As Integer
     Dim iCol As Integer

     For irow = 4 To 2873
    'Calculating ROI
      Cells(irow + 1, ColPrint).Value = (Cells(irow + 1, ColPick).Value - Cells(irow, ColPick).Value) / Cells(irow, ColPick).Value
      Next irow
      'this isn't strictly speaking necessary, but will help
      application.screenupdating = true
      End Sub

Upvotes: 0

NickSlash
NickSlash

Reputation: 5077

I've no idea if this will work, just wanted to test something I saw on another question yesterday. If you test it, please run it in a copy of your workbook in case it goes horribly wrong!

Update

I've tested it (just using a column of random numbers > 0) and it does work.

Public Sub CalcROI(ByVal ColPick As Integer, ByVal ColPrint As Integer)
Dim rgPick As Range
Dim vaPick As Variant
Dim rgPrint As Range
Dim vaPrint As Variant
Dim Row As Integer

    Set rgPick = Range(Cells(4, ColPick), Cells(2873 + 1, ColPick))
    vaPick = rgPick.Value

    Set rgPrint = Range(Cells(4, ColPrint), Cells(2873 + 1, ColPrint))
    vaPrint = rgPrint.Value

    For Row = LBound(vaPick) To UBound(vaPick) - 1
        vaPrint(Row + 1, 1) = (vaPick(Row + 1, 1) - vaPick(Row, 1)) / vaPick(Row, 1)
    Next Row

    rgPrint = vaPrint

End Sub

The Answer I referenced.

Upvotes: 1

Related Questions