Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: StDev Macro

  1. #1
    Board Regular
    Join Date
    Nov 2018
    Location
    US, TX
    Posts
    127
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default StDev Macro

    Hi,

    I have below macro that calculate StDev and it was working perfectly fine but I got error "unable to get the StDev property of the worksheetfunction class" whenever the I have empty cells. How can I fix this and make it ignore the empty cells and move on with other cells that have data?

    Code:
    Sub NV_stdev()Dim aSht As Worksheet
        Set aSht = ActiveSheet
    Dim firstC, firstR, lastC, lastR As Long
        firstC = 1
        firstR = 1
        lastC = aSht.Cells(firstR, aSht.Columns.Count).End(xlToLeft).Column
        lastR = aSht.Cells(aSht.Rows.Count, firstC).End(xlUp).Row
    
    
    Dim sa, a, wa, wd, d, sd, mu, n, sigma As String
        sa = "6. Strongly Agree"
        a = "5. Agree"
        wa = "4. Somewhat Agree"
        wd = "3. Somewhat Disagree"
        d = "2. Disagree"
        sd = "1. Strongly Disagree"
        mu = "Average Score"
        n = "Count of Responses"
        sigma = "Std Dev"
    
    
    Dim saR, aR, waR, wdR, dR, sdR, muR, nR, sigmaR As Range
        Set saR = Cells(1, Application.WorksheetFunction.Match(sa, ActiveSheet.[1:1], 0))
        Set aR = Cells(1, Application.WorksheetFunction.Match(a, ActiveSheet.[1:1], 0))
        Set waR = Cells(1, Application.WorksheetFunction.Match(wa, ActiveSheet.[1:1], 0))
        Set wdR = Cells(1, Application.WorksheetFunction.Match(wd, ActiveSheet.[1:1], 0))
        Set dR = Cells(1, Application.WorksheetFunction.Match(d, ActiveSheet.[1:1], 0))
        Set sdR = Cells(1, Application.WorksheetFunction.Match(sd, ActiveSheet.[1:1], 0))
        Set muR = Cells(1, Application.WorksheetFunction.Match(mu, ActiveSheet.[1:1], 0))
        Set nR = Cells(1, Application.WorksheetFunction.Match(n, ActiveSheet.[1:1], 0))
        Set sigmaR = Cells(1, Application.WorksheetFunction.Match(sigma, ActiveSheet.[1:1], 0))
    
    
    
    
    Dim saN, aN, waN, wdN, dN, sdN As Integer
        saN = Val(Left(saR.Value, 1))
        aN = Val(Left(aR.Value, 1))
        waN = Val(Left(waR.Value, 1))
        wdN = Val(Left(wdR.Value, 1))
        dN = Val(Left(dR.Value, 1))
        sdN = Val(Left(sdR.Value, 1))
    
    
    
    
    Dim responses As Variant, i As Long
    For Each itm In Range(Cells(firstR + 1, sigmaR.Column), Cells(lastR, sigmaR.Column))
        i = 1  '<-- initiate array element index
    If Cells(itm.Row, nR.Column).Value <> "" And Cells(itm.Row, nR.Column).Value > 0 Then
    ReDim responses(1 To Cells(itm.Row, nR.Column).Value) As Variant
        For x = 1 To Cells(itm.Row, saR.Column).Value
            responses(i) = saN
            i = i + 1
        Next x
        For x = 1 To Cells(itm.Row, aR.Column).Value
            responses(i) = aN
            i = i + 1
        Next x
        For x = 1 To Cells(itm.Row, waR.Column).Value
            responses(i) = waN
            i = i + 1
        Next x
        For x = 1 To Cells(itm.Row, wdR.Column).Value
            responses(i) = wdN
            i = i + 1
        Next x
        For x = 1 To Cells(itm.Row, dR.Column).Value
            responses(i) = dN
            i = i + 1
        Next x
        For x = 1 To Cells(itm.Row, sdR.Column).Value
            responses(i) = sdN
            i = i + 1
        Next x
    
    
    With Cells(itm.Row, sigmaR.Column)
        .Value = Application.WorksheetFunction.StDev(responses)
        .Font.Color = RGB(0, 56, 70)
        .Font.Name = "Calibri"
        .Font.Size = 8
        .NumberFormat = "0.00_#_#;;"
    End With
    End If
    
    
    Next itm
    
    
    
    
    End Sub

  2. #2
    Board Regular baitmaster's Avatar
    Join Date
    Mar 2009
    Location
    bristol, england
    Posts
    2,028
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: StDev Macro

    If the code Application.WorksheetFunction.StDev(responses) doesn't work because responses is empty then you could test responses before running this calculation. As it's an array you can test it's UBOUND to see if it's got zero dimension. Try adding something like
    Code:
    if not ubound(responses) = 0 then 
    As you're using i to monitor where items get passed to the array you could also use this, but it's not quite straightforward because of how you're using it. Personally I would set it to 0 to start with, then add 1 only if required - I can then test if it's zero or not. You may have to test if it's >1. Works exactly the same but the logic can get confusing if you're not careful...

    If the problem is that responses is populated but with only a zero value then you could test for that instead
    Baitmaster G



    Always save your work before running code from me, I probably haven't tested it and I'm not responsible when you lose all your work :p
    I aim to improve understanding so you can develop your own solutions, not do all your work for you. Write a clear requirement and you're more likely to get help

  3. #3
    Board Regular
    Join Date
    Nov 2018
    Location
    US, TX
    Posts
    127
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: StDev Macro

    Hi,

    Thank you for your help! How can I add this code? and yes the problem is with the cells that have empty responses as when I removed the rows that have empty cells, the macro worked perfectly fine. Is there a way to fix that?

  4. #4
    Board Regular baitmaster's Avatar
    Join Date
    Mar 2009
    Location
    bristol, england
    Posts
    2,028
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: StDev Macro

    You could try something like
    Code:
    With Cells(itm.Row, sigmaR.Column)
        if ubound(responses) = 0 then 
            .Value = 0
        else
            .value = Application.WorksheetFunction.StDev(responses)
        end if
        .Font.Color = RGB(0, 56, 70)
        .Font.Name = "Calibri"
        .Font.Size = 8
        .NumberFormat = "0.00_#_#;;"
    End With
    End If
    Baitmaster G



    Always save your work before running code from me, I probably haven't tested it and I'm not responsible when you lose all your work :p
    I aim to improve understanding so you can develop your own solutions, not do all your work for you. Write a clear requirement and you're more likely to get help

  5. #5
    Board Regular
    Join Date
    Nov 2018
    Location
    US, TX
    Posts
    127
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: StDev Macro

    Same error message "unable to get the StDev property of the worksheetfunction class" and it highlight below code:

    Code:
          .Value = Application.WorksheetFunction.StDev(responses)
    I have blank cells not "0"
    Last edited by zinah; Oct 15th, 2019 at 12:02 PM.

  6. #6
    Board Regular baitmaster's Avatar
    Join Date
    Mar 2009
    Location
    bristol, england
    Posts
    2,028
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: StDev Macro

    OK, I take it you're quite unfamiliar with VBA, so let's look at a few things we can do to find the problem and fix it

    The problem is because the code is trying to run an impossible calculation on the array variable "responses". We can either seek to understand why this is impossible, then check for those conditions and bypass the calculation when found, or we can stick a generic error handler in there that will react when the problem occurs

    My first guess was that responses was an empty array and hence I was testing it to see if the size was zero, but this apparently is not the case. Perhaps instead there are lots of values but all are zero and that causes a failure..? It's possible to view the array when the code breaks, and then work out why it doesn't work. To do this, you need to show the Locals window (VB > View > Locals Window) and then run your code.

    When the code breaks you can enter DEBUG mode (the problematic code line is highlighted in yellow), then this window contains details of all currently-active variables. You're looking for the responses variable, and you should be able to investigate it and see all values within it. I'm interested in what you can learn from this window, and you'll benefit from understanding it better


    Generic error handler:
    Another approach is to set up an error handler. The following tells the code to ignore the problem (but this doesn't always work), so we can assign a default value to a variable then only change it if a better value is found. I don't like this approach because it can mask other problems, but it may work OK for you
    Code:
    dim dblValue as double
    on error resume next    ' ignore any errors encountered
        dblValue = 0    ' resets it every loop if you are looping
        dblValue = Application.WorksheetFunction.StDev(responses)
    on error goto 0    ' switch error handling off again
    
    With Cells(itm.Row, sigmaR.Column)
        .Value = dblvalue
        .Font.Color = RGB(0, 56, 70)
        .Font.Name = "Calibri"
        .Font.Size = 8
        .NumberFormat = "0.00_#_#;;"
    End With
    Baitmaster G



    Always save your work before running code from me, I probably haven't tested it and I'm not responsible when you lose all your work :p
    I aim to improve understanding so you can develop your own solutions, not do all your work for you. Write a clear requirement and you're more likely to get help

  7. #7
    Board Regular
    Join Date
    Nov 2018
    Location
    US, TX
    Posts
    127
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: StDev Macro

    Hi @baitmaster !

    Thank you for your time and explaining the macro, sorry for the late respond but I didn't get the notification of your reply. Yes, I'm too new to VBA world but trying my best to learn. Let me tell you what I have and maybe you could help. I have a survey that is based on 6 rating

    6. Strongly Agree 5. Agree 4. Somewhat Agree 3. Somewhat Disagree 2. Disagree 1. Strongly Disagree

    What I want is to calculate Std Dev for each row. Below is a sample data along with calculated Std Dev. I want to update my macro to produce the same results in column Std Dev

    Slice Count of Responses Average Score Std Dev Favorable Percent Neutral Percent Unfavorable Percent 6. Strongly Agree 5. Agree 4. Somewhat Agree 3. Somewhat Disagree 2. Disagree 1. Strongly Disagree
    B 12 4.43 1.09 54.79% 37.72% 7.49% 84 282 198 54 40 10
    C 7 4.37 1.26 56.54% 32.37% 11.09% 72 183 100 46 37 13
    D 16 4.14 1.30 44.35% 42.46% 13.19% 124 276 259 124 85 34
    E 11 4.42 1.42 52.54% 36.23% 11.23% 204 147 135 107 56 19
    F 12 4.97 1.12 76.68% 18.38% 4.94% 263 296 103 31 21 15
    G 7 3.82 1.38 35.19% 44.54% 20.27% 31 127 151 49 50 41
    H 18 3.60 1.48 33.90% 36.78% 29.32% 65 276 260 110 188 107
    I 49 4.38 1.33 56.12% 32.30% 11.58% 519 1017 639 245 186 131


    Code:
    Sub Stdev_test()    Set aSht = ActiveSheet
    Dim firstC, firstR, lastC, lastR As Long
        firstC = 1
        firstR = 1
        lastC = aSht.Cells(firstR, aSht.Columns.Count).End(xlToLeft).Column
        lastR = aSht.Cells(aSht.Rows.Count, firstC).End(xlUp).Row
    
    
    
    
    Dim sa, a, wa, wd, d, sd, mu, n, sigma As String
        sa = "6. Strongly Agree"
        a = "5. Agree"
        wa = "4. Somewhat Agree"
        wd = "3. Somewhat Disagree"
        d = "2. Disagree"
        sd = "1. Strongly Disagree"
        mu = "Average Score"
        n = "Count of Responses"
        sigma = "Std Dev"
    
    
    
    
    Dim saR, aR, waR, wdR, dR, sdR, muR, nR, sigmaR As Range
        Set saR = Cells(1, Application.WorksheetFunction.Match(sa, ActiveSheet.[1:1], 0))
        Set aR = Cells(1, Application.WorksheetFunction.Match(a, ActiveSheet.[1:1], 0))
        Set waR = Cells(1, Application.WorksheetFunction.Match(wa, ActiveSheet.[1:1], 0))
        Set wdR = Cells(1, Application.WorksheetFunction.Match(wd, ActiveSheet.[1:1], 0))
        Set dR = Cells(1, Application.WorksheetFunction.Match(d, ActiveSheet.[1:1], 0))
        Set sdR = Cells(1, Application.WorksheetFunction.Match(sd, ActiveSheet.[1:1], 0))
        Set muR = Cells(1, Application.WorksheetFunction.Match(mu, ActiveSheet.[1:1], 0))
        Set nR = Cells(1, Application.WorksheetFunction.Match(n, ActiveSheet.[1:1], 0))
        Set sigmaR = Cells(1, Application.WorksheetFunction.Match(sigma, ActiveSheet.[1:1], 0))
    
    
    
    
    
    
    
    
    Dim saN, aN, waN, wdN, dN, sdN As Integer
        saN = Val(Left(saR.Value, 1))
        aN = Val(Left(aR.Value, 1))
        waN = Val(Left(waR.Value, 1))
        wdN = Val(Left(wdR.Value, 1))
        dN = Val(Left(dR.Value, 1))
        sdN = Val(Left(sdR.Value, 1))
        
    
    
    Dim responses As Variant, i As Long
    For Each itm In Range(Cells(firstR + 1, sigmaR.Column), Cells(lastR, sigmaR.Column))
        i = 1  '<-- initiate array element index
    If Cells(itm.Row, nR.Column).Value <> "" And Cells(itm.Row, nR.Column).Value > 0 Then
    
    
    ReDim responses(1 To Cells(itm.Row, nR.Column).Value) As Variant
        For x = 1 To Cells(itm.Row, saR.Column).Value
            responses(i) = saN
            i = i + 1
        Next x
        For x = 1 To Cells(itm.Row, aR.Column).Value
            responses(i) = aN
            i = i + 1
        Next x
        For x = 1 To Cells(itm.Row, waR.Column).Value
            responses(i) = waN
            i = i + 1
        Next x
        For x = 1 To Cells(itm.Row, wdR.Column).Value
            responses(i) = wdN
            i = i + 1
        Next x
        For x = 1 To Cells(itm.Row, dR.Column).Value
            responses(i) = dN
            i = i + 1
        Next x
        For x = 1 To Cells(itm.Row, sdR.Column).Value
            responses(i) = sdN
            i = i + 1
        Next x
    
    
    
    
    With Cells(itm.Row, sigmaR.Column)
        .Value = Application.WorksheetFunction.StDev(responses)
        .Font.Color = RGB(0, 56, 70)
        .Font.Name = "Calibri"
        .Font.Size = 8
        .NumberFormat = "0.00_#_#;;"
    End With
    End If
    
    
    
    
    Next itm
    
    
    
    
    
    
    
    
    End Sub

  8. #8
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,079
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: StDev Macro

    It looks like you are doing weighted averages.

    I am not so sure that you are doing weighted sample standard deviations. For Slice B, my weighted sample standard deviation was 1.20, not 1.09.

    As for the others, I don't know what the rules are for those categories.

    See this thread for weight formulas. https://www.mrexcel.com/forum/excel-...deviation.html

  9. #9
    Board Regular
    Join Date
    Nov 2018
    Location
    US, TX
    Posts
    127
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: StDev Macro

    Yes, I’m using weighted average. Maybe the results I provided are not correct, could you help me with updating my macro with the right formula/ codes?

  10. #10
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,079
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: StDev Macro

    Why do you need a macro? Formulas can do it.

    I did make macro UDFs to do the weighted average and weighted sample standard deviation. The only advantage I guess is that they are easier to use as a UDF as shorter "formulas". Of course they can also just insert the values rather than formulas.

    It just depends on what solution path you want to use.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •