Range of Formulae to VBA

[CallumPerry]

Hi traders.

I'm looking to make my spreadsheets more efficient by beginning to move all my formulas to vba. Hoping to speed things up and get better at using VBA so I'm not expecting this to be easy but worth giving it ago!

My first question is, how would you write code for a selection of cells? For example, I'm using a basic multiplication grid as an example with my screenshots so say I have a formula in one cell and I'm trying to drag it down by 9 rows and across by 9 columns.

The code in the following cell is: =B$2*$A3

Screen Shot 2018-10-07 at 16.22.06.png

And then because I have locked the rows in the first reference and the columns in the second, you just drag the cells down and across and the numbers autofill no problem at all.

Screen Shot 2018-10-07 at 16.21.23.png

What would be the most efficient way of doing this with more complicated formulas that each have 10+ references in? If somebody could type out an example or direct me to some specific reading that would really help me out! There seems to be so many ways of referencing a single cell, when looking at multiple cells going off in two directions it just becomes a little difficult to work out the most effective way of organising it.

Cheers,

Callum

[Derek27]

In VBA you use variables to store values. For example:-

Dim sngDemo as Single

sngDemo = Range("A2") * Range("B3")

To have a table of values you can use an array:-

Dim sngDemo(10, 10) as Single

sngDemo(1, 1) = ...
sngDemo(1, 2) = ...

http://www.homeandlearn.org/

[spreadbetting]

You need to start looking at how to manipulate loops, it's relatively simple when you get your head around the concept of replacing the variable part of your cell reference with an integer. There are lots of ways we can reference cells in excel but your best way is to probably use indexes for the column and row numbers.

So A1 can be referenced like so within VBA

Code: Select all

Worksheets("Sheet1").Cells(1, 1).Value  

First number is the row , second number the column.

If we now replace that number with a variable number we can step thru it in whatever increments we want and also loop within loops to 'freeze' references as per your example.

Code: Select all

 Dim r As Integer, c As Integer
 For r = 1 To 5
 For c = 1 To 5
 Worksheets("Sheet1").Cells(r, c).Value = r * c
 Next c
 Next r

[ShaunWhite]

Callum, I'm a bit busy out and about to give a proper answer but as a clue you need to use 'loops' and a 2 dimensional array

Eg.

Sub Test()

Dim Results(10, 20)
For ColNum = 1 To 10
For RowNum = 1 To 20
Results(ColNum, RowNum) = ColNum * RowNum
Next
Next

For ColNum = 1 To 10
Sheets(1).Cells(ColNum + 2, 3).Value = ColNum
Next

For RowNum = 1 To 20
Sheets(1).Cells(2, RowNum + 3).Value = RowNum
Next

For ColNum = 1 To 10
For RowNum = 1 To 20
Sheets(1).Cells(ColNum + 2, RowNum + 3).Value = Results(ColNum, RowNum)
Next
Next

End Sub


Best I can do now, hope it's a lead....there's obv other ways as usual.

"RowNum" and "ColNum" are just variables, needn't be those names.

[ShaunWhite]

damn
I just saw you'd been helping...i'll leave you to it...

[ShaunWhite]

In VBA you use variables to store values. For example:-

Dim sngDemo as Single

sngDemo = Range("A2") * Range("B3")

To have a table of values you can use an array:-

Dim sngDemo(10, 10) as Single

sngDemo(1, 1) = ...
sngDemo(1, 2) = ...

http://www.homeandlearn.org/

I'd get in the habit of putting a ". Value" on those.
Eg
sngDemo = Range("A2").Value * Range("B3").Value

When you hit the dot, the vba editors' intellisense should show all the different things you can use apart from just .Value.

.Value is an example of an object parameter.
Others which do things like .Copy or . Select are called methods.

[CallumPerry]

Cheers you 3!

Amazingly quick replies as ever and exactly what I need, I should be able to work out how to do this with my own code no problem at all now, at the very least I know what to research if I get stuck. Thanks again!

[nigelk]

As you know the start/end of the rows/columns another way would be:

Code: Select all

Sub test()

Range("b3").Copy

Range("B3:K12").PasteSpecial Paste:=xlPasteFormulas

Application.CutCopyMode = False

End Sub

[Derek27]

In VBA you use variables to store values. For example:-

Dim sngDemo as Single

sngDemo = Range("A2") * Range("B3")

To have a table of values you can use an array:-

Dim sngDemo(10, 10) as Single

sngDemo(1, 1) = ...
sngDemo(1, 2) = ...

http://www.homeandlearn.org/

I'd get in the habit of putting a ". Value" on those.
Eg
sngDemo = Range("A2").Value * Range("B3").Value

When you hit the dot, the vba editors' intellisense should show all the different things you can use apart from just .Value.

.Value is an example of an object parameter.
Others which do things like .Copy or . Select are called methods.

It is clearer to add .Value but if you have lots of them, it's the default property and you know it's the property you need it does feel like a waste of time to me.

[sa7med]

Hey, correct me if I'm wrong but isn't it more efficient to write formulas directly into excel rather than vba? my understanding is that vba is single threaded while Excel can use more cores.

[Wolf1877]

Hey, correct me if I'm wrong but isn't it more efficient to write formulas directly into excel rather than vba? my understanding is that vba is single threaded while Excel can use more cores.

I cant comment on the multithreading capabilities of excel formula calculations but the problem with excel formulas is they are continuously recalculated unconditionally whenever worksheets are refreshed. There are 6 worksheet refreshes per bet angel worksheet per guardian cycle. 7 refreshes per cycle if you have opted for optional values like VWAP.

VBA code can be developed so that calculations are performed conditionally so that numbers are only crunched when actually needed. So my code would typically be something like if the market volume has changed then crunch numbers selectively via VBA else dont bother. Excel formula would be blindly recalculated needlessly on every refresh. In my experience multi sheet workbooks with lots of formula are slow while well optimised VBA calculations are much faster.