0

I am fairly new to VBA and been trying to automate some financial reports. Below is the subprocedure that I have:

Sub normdata()

Dim numofstocks As Integer
Dim numofdatapoints As Integer
Dim numberofiterations As Integer
Dim averageposition As Integer

numofstocks = Application.CountA(Sheets("Static").Range("B:B")) - 1
Sheets("NormData").Range("A2").Value = "Date"

    For i = 1 To numofstocks
       Sheets("NormData").Cells(1, 2 * (i - 1) + 2).Value = Sheets("Static").Cells(i + 1, 1)
       Sheets("NormData").Cells(2, 2 * (i - 1) + 2).Value = "Close"
       Sheets("NormData").Cells(2, 2 * (i - 1) + 3).Value = "Returns"
    Next i

numofdatapoints = Application.CountA(Sheets("RawData").Range("A:A")) - 2

    For i = 1 To numofdatapoints
        Sheets("NormData").Cells(i + 2, 1).Value = Sheets("RawData").Cells(i + 2, 1).Value
    Next i

    For j = 1 To numofstocks
        For i = 1 To numofdatapoints
            Sheets("NormData").Cells(i + 2, 2 * (j - 1) + 2).Value = Sheets("RawData").Cells(i + 2, 6 * (j - 1) + 5).Value
        Next i
    Next j

numberofiterations = Application.CountA(Sheets("RawData").Range("A:A")) - 3
     For j = 1 To numofstocks
        For i = 1 To numberofiterations
            Sheets("NormData").Cells(i + 2, 2 * (j - 1) + 3).Value = (Sheets("NormData").Cells(i + 2, 2 * (j - 1) + 2).Value - Sheets("NormData").Cells(i + 3, 2 * (j - 1) + 2).Value) / Sheets("NormData").Cells(i + 3, 2 * (j - 1) + 2).Value
        Next i
    Next j


averageposition = Application.CountA(Sheets("NormData").Range("A:A")) + 2
    For i = 1 To numofstocks
        Worksheets("NormData").Cells(averageposition, 2 * (i - 1) + 2).Value = Worksheets("Static").Cells(i + 1, 1) & " average daily returns"
        Worksheets("NormData").Cells(averageposition, 2 * (i - 1) + 3).Value = Application.WorksheetFunction.Average(Worksheets("NormData").Range(Cells(3, 2 * (i - 1) + 3), Cells(numberofiterations + 2, 2 * (i - 1) + 3)))
        Worksheets("NormData").Cells(averageposition + 1, 2 * (i - 1) + 2).Value = Worksheets("Static").Cells(i + 1, 1) & " daily variance"
        Worksheets("NormData").Cells(averageposition + 1, 2 * (i - 1) + 3).Value = Application.WorksheetFunction.VarP(Worksheets("NormData").Range(Cells(3, 2 * (i - 1) + 3), Cells(numberofiterations + 2, 2 * (i - 1) + 3)))
        Worksheets("NormData").Cells(averageposition + 2, 2 * (i - 1) + 2).Value = Worksheets("Static").Cells(i + 1, 1) & " daily std dev"
        Worksheets("NormData").Cells(averageposition + 2, 2 * (i - 1) + 3).Value = (Application.WorksheetFunction.VarP(Worksheets("NormData").Range(Cells(3, 2 * (i - 1) + 3), Cells(numberofiterations + 2, 2 * (i - 1) + 3)))) ^ (1 / 2)
        Worksheets("NormData").Cells(averageposition + 3, 2 * (i - 1) + 2).Value = Worksheets("Static").Cells(i + 1, 1) & " 95% VaR"
        Worksheets("NormData").Cells(averageposition + 3, 2 * (i - 1) + 3).Value = Application.WorksheetFunction.Percentile(Range(Cells(3, 2 * i + 1), Cells(numberofiterations + 2, 2 * i + 1)), 0.05)
        Worksheets("NormData").Cells(averageposition + 4, 2 * (i - 1) + 2).Value = Worksheets("Static").Cells(i + 1, 1) & " 99% VaR"
        Worksheets("NormData").Cells(averageposition + 4, 2 * (i - 1) + 3).Value = Application.WorksheetFunction.Percentile(Range(Cells(3, 2 * i + 1), Cells(numberofiterations + 2, 2 * i + 1)), 0.01)
   Next i

    For i = 1 To numofstocks
        Worksheets("Static").Cells(1 + i, 4).Value = Worksheets("NormData").Cells(numberofiterations + 4, 2 * i + 1).Value
    Next i


End Sub

For example, I am only able to run the code when I am in sheet "NormData", otherwise i get a run-time error '1004', application defined or object defined error. The code always stops in the second last for loop and highlights the second line of the loop. Thank you for your help in advance! Much appreciated :)

Bert
  • 1
  • 3

2 Answers2

1

Your problem is that your code is using a bunch of unqualified/implicit references which innately makes it difficult to catch when one of these issues is code breaking. This line right here is the problem:

Worksheets("NormData").Range(Cells(3, 2 * (i - 1) + 3), Cells(numberofiterations + 2, 2 * (i - 1) + 3)))

See how you start with ActiveWorkbook.Worksheets("NormData").Range and then enter into an unqualified cells reference Cells(3, 2 * (i - 1) + 3)? This cells reference actually reads as ActiveSheet.Cells("") and so if your ActiveSheet is anything other than ActiveWorkbook.Worksheets("NormData") your code will break.

Check out this post for more info: Why does Range work, but not Cells? .

Brandon Barney
  • 2,382
  • 1
  • 9
  • 18
  • Hi Brandon, thank you for your help so far! Do you have any suggestions on how I can edit or change the code for it to work? Also, what are unqualified cell reference and how do I overcome them? – Bert Jun 30 '17 at 17:26
  • The answer above is literally the problem that is breaking your code, so you'll want to start there to fix it. An unqualified reference is basically a reference to an object where the parent isnt specified. For example `Worksheets()` would be unqualified whereas `ThisWorkbook.Worksheets` would be fully qualified. Check out the other post that answers the same question as well, there is additional info there. My recommendation would be to clean up your code using `With` blocks and variables, and then go from there. – Brandon Barney Jun 30 '17 at 17:28
  • Thanks a lot for your help, i'll try with what you suggested and see how it goes :) – Bert Jun 30 '17 at 17:36
  • @Bert Agreed with Mat's Mug. Rubberduck can be a life saver, especially for someone new to VBA :). – Brandon Barney Jun 30 '17 at 18:15
  • Thanks for the tips guys! It was a great help. Managed to get my problem solved! – Bert Jul 02 '17 at 14:41
-1

Try to set all worksheets in variables

For example:

dim wsNormData as Worksheet
set wsNormData = ThisWorkbook.Worksheets("NormData")

then use like:

wsNormData.Cells(x,y).value = "value"
Rafael Januário
  • 1
  • Welcome to SO Rafael! Just a tip, be sure to explain not just what, but also why. This will help your answers be clearer for the OP's. Best of luck! – Brandon Barney Jun 30 '17 at 17:09
  • Thx for the advice :) – Rafael Januário Jun 30 '17 at 17:12
  • Caching reference to worksheets may marginally improve performance, but it is not the cause of the problem. – GSerg Jun 30 '17 at 17:12
  • @GSerg He is correct in the sense that it will help solve one of the symptoms of the problem. More explanation would help though :). – Brandon Barney Jun 30 '17 at 17:13
  • @BrandonBarney But what symptom does it solve? The OP has a lot of `Sheets("...")` and `Woksheets("...")`, which is correct in principle. They also have a dozen of places where they forgot to mention any sheets. This answer advises replacing the `Sheets("...")` and `Woksheets("...")` with cached references. That will make the code look better but will not solve the missing qualifications or change how existing code works. – GSerg Jun 30 '17 at 17:16
  • @GSerg I am not saying it was a good answer. I was more defending his attempt at an answer. It is one step in the right direction for eliminating references to the ActiveWorkbook and the ActiveSheet. That is also why I suggested he improve his answer with additional info. It wont solve the problem, but it will solve part of the behavior that is causing the problem. Certainly there is more to it (as I have pointed out in my answer, and you have pointed out by referencing the dupe). I just dont want to discourage a new community member from trying to give back to the community. – Brandon Barney Jun 30 '17 at 17:18
  • Hi everyone, thank you for all your comments so far! This is my first VBA / programming project that I am doing on my own and am not very familiar with the VBA syntax. I have tried Rafael's suggestion but am still unable to solve the problem. Is there anything else that i can edit in the code? Especially the second line of the long for loop which seems to be the problem. Thanks again! – Bert Jun 30 '17 at 17:34