CODEWITHSUNDEEP
excelvba
Dekks
Dekks

Reputation: 137

Alternative or more efficient method than referencing cells?

Firstly, I wasn't sure if VBA questions belong here or on SuperUser, apologies if in wrong place.

I am trying to teach myself VBA, and going through various exercises/challenges I've found online. I have made a macro that writes out a multiplication table, its almost certainly very inefficient, but a good starting point on writing loops etc.

My code is as follows:

Sub TimesTable()
Dim TimesTable As Integer
Dim Plot As Integer
Dim Numbers As Long
Dim RowNumbers As Long
Dim StartTime As Double
Dim SecondsElapsed As Double
'Turn off various things to speed up macro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'Start Timer
StartTime = Timer
ActiveSheet.Cells.ClearContents
'Ask for and read input into variable
TimesTable = InputBox("Enter a Integer to display Times Table")
'Write Axis lines
For Plot = 1 To TimesTable
ActiveSheet.Range("A1").Offset(0, Plot).Value = Plot
ActiveSheet.Range("A1").Offset(Plot, 0).Value = Plot
ActiveSheet.Range("A2").Select
Next
'Start loop 1 to kick off line writing
For RowNumbers = 1 To TimesTable
'Start loop 2 to write actual lines
    For Numbers = 1 To TimesTable
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = RowNumbers * Numbers
    Next
ActiveCell.Offset(1, -TimesTable).Select
Next
'Turn on screen updating etc again
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
SecondsElapsed = Round(Timer - StartTime, 2)
'Display time taken to run macro
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
End Sub

I've managed to get it all working and learned a few things on the way (mostly how to nest loops and that Integer has a limit), but I was wondering if there is a better way to write each calculation rather than using cell offsets? At the moment it writes the calculation, offsets by one cell to the right, and then when it gets to the end of the line, offsets back to the start of the row, and then goes down one row. I've read that for speed you want to avoid referencing specific cells when possible but struggling with how I would achieve this.

Upvotes: 2

Views: 1426

Answers (2)

Plagon
Plagon

Reputation: 3139

As already pointed out, .Select is not necessary and slow. What you want is to refer the Cells directly. Below i have an example without .Offset and .Select. 

Sub TimesTable()
Dim ws As Worksheet
Dim TimesTable As Long, Plot As Long
Dim Numbers As Long, RowNumbers As Long
Dim StartTime As Double, SecondsElapsed As Double
Dim Cell As Range

Set ws = ActiveSheet

'Turn off various things to speed up macro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'Start Timer
StartTime = Timer
ws.Cells.ClearContents

'Ask for and read input into variable
TimesTable = 100

'Write Axis lines
With ws
    For Plot = 1 To TimesTable
        .Cells(Plot + 1, 1).Value = Plot
        .Cells(1, Plot + 1).Value = Plot
    Next

    For Each Cell In .Range(.Cells(2, 2), .Cells(TimesTable + 1, TimesTable + 1))
        Cell.Value = .Cells(Cell.Row, 1).Value * .Cells(1, Cell.Column).Value
    Next Cell
End With

'Turn on screen updating etc again
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
SecondsElapsed = Round(Timer - StartTime, 2)
'Display time taken to run macro
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
End Sub

I just tried it with TimesTable = 1000 and it saves around 20Secs. The main difference is, that i used a For Each-Loop instead of 2 For-Loops. The For Each goes trough every Cell in the Range where the multiplicated values should be. Also to makes things shorter you can use vars for Workbooks, Worksheets, etc., also a lot easier to modify afterwards.

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37367

Select is inefficient. Instead of selecting offset cells, try referring to cell like this: Cells(row, column).Value, with this you can get or set the value of cell on the specified row and column (you can use Formula instead of Value and many others :) accordingly to your needs ). That should speed up your macro. Generally, in VBA we have Range type, which can represent a range in a sheet. Thus, you don't need select specific ranges (which slows down execution) in order to refer to them :)

Also, it's better to use Long instead of Integer, because all integers are converted to long in VBA anyway, refer to this post.

Upvotes: 1

Related Questions