Interpreting VBA code for moving averages

Question

While I roughly understood my coding at the time of writing it awhile back, I have since forgotten how to interpret the first few parts of it (in bold).

Why 'as long'? My understanding is that this is used when the variable is only to take larger integer values. Since the share values contain several decimals, I am not sure why I chose this over 'double'.
Why/when do we dim a variable as a 'range', and why do we use 'set' at all? My limited understanding of the purpose of the set function is to assign values to 'object' variables. Why is the 'range' an 'object'?
I have completely forgot what the line Set stockValue = Range("B5:B" & lastStockprice) is doing, especially the ampersand.
I've no idea what is going on here:

ReDim stockPrice(stockValue.Count - 1) For Each cell In stockValue stockPrice(cell.Row - 5) = cell.Value Next

Sub MovingAverage()

Dim CumulSum() As Double
Dim MovingAv() As Double

RowCountA = Range("StockPrice").Rows.Count
RowCountB = Range("MovingAv").Rows.Count


ReDim CumulSum(RowCountB)


Dim stockPrice As Variant
Dim lastStockprice **As Long**
    lastStockprice = Cells(Rows.Count, "B").End(xlUp).Row
Dim stockValue **As Range**
    **Set stockValue = Range("B5:B" & lastStockprice)**

**ReDim stockPrice(stockValue.Count - 1)
For Each cell In stockValue
stockPrice(cell.Row - 5) = cell.Value
Next**


    For i = 0 To RowCountB - 1
        For k = 0 To 9
            CumulSum(i) = CumulSum(i) + stockPrice(i + k)
        Next k
    Next i

    For i = 1 To RowCountB
        Range("MovingAv").Cells(i) = CumulSum(i - 1) / 10
    Next i

End Sub

If someone could please explain the bolded code for me (I've a very basic knowledge of VBA that extends about as far as matrix multiplication, basic functions and double arrays), it would be greatly appreciated. :)

this is why you should always add descriptive comments to your code :) — Katana24, Apr 19 '13 at 10:21

score 0 · Accepted Answer · edited May 08 '18 at 21:49

Dim lastStockprice **As Long**
lastStockprice = Cells(Rows.Count, "B").End(xlUp).Row

This has to be long because we are trying to find the last row in Col B. This is to make the code compatible with xl2007+ (Where there are 1048576 rows). You can see this link on how to get the last row.

Why is the 'range' an 'object'?

See this link. Also see this.

I have completely forgot what the line Set stockValue = Range("B5:B" & lastStockprice) is doing, especially the ampersand.

As mentioned earlier lastStockprice is the last row and & is used to concatenate so that we can set our range. Let's say the last row is 20 then the above code can be written as

Set stockValue = Range("B5:B" & 20)
'OR
Set stockValue = Range("B5:B20")

I've no idea what is going on here: ReDim stockPrice(stockValue.Count - 1)

What the code is trying to do is dynamically increase the size of the array so that it can store more values to it. REDIM (Re-Dimension) I would recommend seeing this link

FOLLOWUP (From comments)

I understand all of it now except this part: For Each cell In stockValue stockPrice(cell.Row - 5) = cell.Value Next**

What that piece of code is doing is looping through every cell in the range stockvalue and then storing the cell value in the array stockPrice

Ex: Let's say we have a range, A1:B2

When we say For each cell in Range("A1:B2"), we are telling the code to loop through every cell in that range (A1, A2, B1, B2)

Thanks for your help. I understand all of it now except this part: --- For Each cell In stockValue stockPrice(cell.Row - 5) = cell.Value Next — Luke Denham, Apr 19 '13 at 08:45
What does it mean by 'stockPrice(cell.Row - 5) = cell.Value' - is this saying it is placing the 'value' present in each stockValue 'cell' and putting in to each row of stockPrice? What does the '-5' mean? Wouldn't cell.Value and cell.row return the same thing since there is only one column? Thanks. — Luke Denham, Apr 19 '13 at 10:46
`stockPrice(cell.Row - 5) = cell.Value` means the value from the `cell` will be saved in the array `stockPrice`. This `cell.Row - 5` means if cell.row is 15 then store the value in `stockPrice(15-5)` which is `stockPrice(10)` Think of the array as a cupboard with different sections to keep your cloths. I would seriously recommend you to go through the [link](http://www.cpearson.com/excel/vbaarrays.htm) — Siddharth Rout, Apr 19 '13 at 11:25

Interpreting VBA code for moving averages

1 Answers1