StDev Macro

Z

zinah

Active Member
Joined
Nov 28, 2018
Messages
368
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
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([COLOR=#333333]responses) = 0 then [/COLOR]
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
 
Upvote 0
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?
 
Upvote 0
You could try something like
Code: 
With Cells(itm.Row, sigmaR.Column)
    [COLOR=#333333]if ubound([/COLOR][COLOR=#333333][COLOR=#333333]responses) = 0 then [/COLOR][/COLOR]
        .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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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

[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl63, width: 64"]6. Strongly Agree[/TD]
[TD="class: xl63, width: 64"]5. Agree[/TD]
[TD="class: xl63, width: 64"]4. Somewhat Agree[/TD]
[TD="class: xl63, width: 64"]3. Somewhat Disagree[/TD]
[TD="class: xl63, width: 64"]2. Disagree[/TD]
[TD="class: xl63, width: 64"]1. Strongly Disagree


[/TD]
[/TR]
</tbody>[/TABLE]
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

[TABLE="width: 832"]
<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Slice[/TD]
[TD="class: xl65, width: 64"]Count of Responses[/TD]
[TD="class: xl65, width: 64"]Average Score[/TD]
[TD="class: xl67, width: 64"]Std Dev[/TD]
[TD="class: xl66, width: 64"]Favorable Percent[/TD]
[TD="class: xl65, width: 64"]Neutral Percent[/TD]
[TD="class: xl65, width: 64"]Unfavorable Percent[/TD]
[TD="class: xl65, width: 64"]6. Strongly Agree[/TD]
[TD="class: xl65, width: 64"]5. Agree[/TD]
[TD="class: xl65, width: 64"]4. Somewhat Agree[/TD]
[TD="class: xl65, width: 64"]3. Somewhat Disagree[/TD]
[TD="class: xl65, width: 64"]2. Disagree[/TD]
[TD="class: xl65, width: 64"]1. Strongly Disagree[/TD]
[/TR]
[TR]
[TD="class: xl68"]B[/TD]
[TD="class: xl68, align: right"]12[/TD]
[TD="class: xl69, align: right"]4.43[/TD]
[TD="class: xl70, align: right"]1.09 [/TD]
[TD="class: xl71, align: right"]54.79%[/TD]
[TD="class: xl71, align: right"]37.72%[/TD]
[TD="class: xl71, align: right"]7.49%[/TD]
[TD="class: xl68, align: right"]84[/TD]
[TD="class: xl68, align: right"]282[/TD]
[TD="class: xl68, align: right"]198[/TD]
[TD="class: xl68, align: right"]54[/TD]
[TD="class: xl68, align: right"]40[/TD]
[TD="class: xl68, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl68"]C[/TD]
[TD="class: xl68, align: right"]7[/TD]
[TD="class: xl69, align: right"]4.37[/TD]
[TD="class: xl70, align: right"]1.26 [/TD]
[TD="class: xl71, align: right"]56.54%[/TD]
[TD="class: xl71, align: right"]32.37%[/TD]
[TD="class: xl71, align: right"]11.09%[/TD]
[TD="class: xl68, align: right"]72[/TD]
[TD="class: xl68, align: right"]183[/TD]
[TD="class: xl68, align: right"]100[/TD]
[TD="class: xl68, align: right"]46[/TD]
[TD="class: xl68, align: right"]37[/TD]
[TD="class: xl68, align: right"]13[/TD]
[/TR]
[TR]
[TD="class: xl68"]D[/TD]
[TD="class: xl68, align: right"]16[/TD]
[TD="class: xl69, align: right"]4.14[/TD]
[TD="class: xl70, align: right"]1.30 [/TD]
[TD="class: xl71, align: right"]44.35%[/TD]
[TD="class: xl71, align: right"]42.46%[/TD]
[TD="class: xl71, align: right"]13.19%[/TD]
[TD="class: xl68, align: right"]124[/TD]
[TD="class: xl68, align: right"]276[/TD]
[TD="class: xl68, align: right"]259[/TD]
[TD="class: xl68, align: right"]124[/TD]
[TD="class: xl68, align: right"]85[/TD]
[TD="class: xl68, align: right"]34[/TD]
[/TR]
[TR]
[TD="class: xl68"]E[/TD]
[TD="class: xl68, align: right"]11[/TD]
[TD="class: xl69, align: right"]4.42[/TD]
[TD="class: xl70, align: right"]1.42 [/TD]
[TD="class: xl71, align: right"]52.54%[/TD]
[TD="class: xl71, align: right"]36.23%[/TD]
[TD="class: xl71, align: right"]11.23%[/TD]
[TD="class: xl68, align: right"]204[/TD]
[TD="class: xl68, align: right"]147[/TD]
[TD="class: xl68, align: right"]135[/TD]
[TD="class: xl68, align: right"]107[/TD]
[TD="class: xl68, align: right"]56[/TD]
[TD="class: xl68, align: right"]19[/TD]
[/TR]
[TR]
[TD="class: xl68"]F[/TD]
[TD="class: xl68, align: right"]12[/TD]
[TD="class: xl69, align: right"]4.97[/TD]
[TD="class: xl70, align: right"]1.12 [/TD]
[TD="class: xl71, align: right"]76.68%[/TD]
[TD="class: xl71, align: right"]18.38%[/TD]
[TD="class: xl71, align: right"]4.94%[/TD]
[TD="class: xl68, align: right"]263[/TD]
[TD="class: xl68, align: right"]296[/TD]
[TD="class: xl68, align: right"]103[/TD]
[TD="class: xl68, align: right"]31[/TD]
[TD="class: xl68, align: right"]21[/TD]
[TD="class: xl68, align: right"]15[/TD]
[/TR]
[TR]
[TD="class: xl68"]G[/TD]
[TD="class: xl68, align: right"]7[/TD]
[TD="class: xl69, align: right"]3.82[/TD]
[TD="class: xl70, align: right"]1.38 [/TD]
[TD="class: xl71, align: right"]35.19%[/TD]
[TD="class: xl71, align: right"]44.54%[/TD]
[TD="class: xl71, align: right"]20.27%[/TD]
[TD="class: xl68, align: right"]31[/TD]
[TD="class: xl68, align: right"]127[/TD]
[TD="class: xl68, align: right"]151[/TD]
[TD="class: xl68, align: right"]49[/TD]
[TD="class: xl68, align: right"]50[/TD]
[TD="class: xl68, align: right"]41[/TD]
[/TR]
[TR]
[TD="class: xl68"]H[/TD]
[TD="class: xl68, align: right"]18[/TD]
[TD="class: xl69, align: right"]3.60[/TD]
[TD="class: xl70, align: right"]1.48 [/TD]
[TD="class: xl71, align: right"]33.90%[/TD]
[TD="class: xl71, align: right"]36.78%[/TD]
[TD="class: xl71, align: right"]29.32%[/TD]
[TD="class: xl68, align: right"]65[/TD]
[TD="class: xl68, align: right"]276[/TD]
[TD="class: xl68, align: right"]260[/TD]
[TD="class: xl68, align: right"]110[/TD]
[TD="class: xl68, align: right"]188[/TD]
[TD="class: xl68, align: right"]107[/TD]
[/TR]
[TR]
[TD="class: xl68"]I[/TD]
[TD="class: xl68, align: right"]49[/TD]
[TD="class: xl69, align: right"]4.38[/TD]
[TD="class: xl70, align: right"]1.33 [/TD]
[TD="class: xl71, align: right"]56.12%[/TD]
[TD="class: xl71, align: right"]32.30%[/TD]
[TD="class: xl71, align: right"]11.58%[/TD]
[TD="class: xl68, align: right"]519[/TD]
[TD="class: xl68, align: right"]1017[/TD]
[TD="class: xl68, align: right"]639[/TD]
[TD="class: xl68, align: right"]245[/TD]
[TD="class: xl68, align: right"]186[/TD]
[TD="class: xl68, align: right"]131

[/TD]
[/TR]
</tbody>[/TABLE]


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
 
Upvote 0
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
VBA Excel, Error code 91
4 5 6
Replies
56
Views
2K
Candyman8019
C
E
  • Solved
VBA to remove Apostrophes works but also gives Run-time error 13. Unable to work out why?
2 3
Replies
22
Views
1K
Event2020
E
B
  • Solved
Macro crashing and closing excel
Replies
1
Views
470
Beneindias
B
S
  • Question Question
"Subscript out of range ( Run time Error: 09)" Problem in My Excel VBA
Replies
5
Views
586
6StringJazzer
6StringJazzer
P
  • Solved
VBA to send multiple emails, run time error 440 after ~30 emails
Replies
5
Views
1K
Domenic
D

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top