CODEWITHSUNDEEP
vbaobjectsetaverage
Luke Denham
Luke Denham

Reputation: 15

Interpreting VBA code for moving averages

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).

  1. 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'.

  2. 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'?

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

  4. 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. :)

Upvotes: 1

Views: 788

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149277

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)

Upvotes: 0

Related Questions