Range of Formulae to VBA

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

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 :)
You do not have the required permissions to view the files attached to this post.
User avatar
Derek27
Posts: 23477
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

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
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

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
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

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.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

damn :)
I just saw you'd been helping...i'll leave you to it...
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Derek27 wrote:
Sun Oct 07, 2018 5:43 pm
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
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

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
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

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
User avatar
Derek27
Posts: 23477
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

ShaunWhite wrote:
Sun Oct 07, 2018 6:12 pm
Derek27 wrote:
Sun Oct 07, 2018 5:43 pm
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
Posts: 800
Joined: Thu May 18, 2017 8:01 am

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
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

sa7med wrote:
Mon Oct 08, 2018 8:42 am
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.
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”