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
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.
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
Range of Formulae to VBA
-
- Posts: 575
- Joined: Wed Apr 19, 2017 5:12 pm
- Location: Wolverhampton
You do not have the required permissions to view the files attached to this post.
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/
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/
-
- 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
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.
So A1 can be referenced like so within VBA
Code: Select all
Worksheets("Sheet1").Cells(1, 1).Value
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
- 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.
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
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
damn
I just saw you'd been helping...i'll leave you to it...
I just saw you'd been helping...i'll leave you to it...
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
I'd get in the habit of putting a ". Value" on those.Derek27 wrote: ↑Sun Oct 07, 2018 5:43 pmIn 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/
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.
-
- 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!
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!
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
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.ShaunWhite wrote: ↑Sun Oct 07, 2018 6:12 pmI'd get in the habit of putting a ". Value" on those.Derek27 wrote: ↑Sun Oct 07, 2018 5:43 pmIn 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/
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.
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.