Putting Standard Deviation on your Status Bar would involve creating a new
class.
In your 'ThisWorkbook', establish the class with something like...
'set up class for Stats on the Status Bar
Dim clsStatsOnStatusBar As New Class_StatsOnStatusBar
Then create a Class Module.
In the class module, declare the application with...
'/==================================================/
Public WithEvents App_Stats As Application 'App_Stats is a variable
'/==================================================/
Since you are showing info on the Status Bar, you want to make sure to set
the Status Bar back to normal when exiting, so the Class module needs
something like...
'/==================================================/
Private Sub App_Stats_WorkbookBeforeSave(ByVal WB As Workbook, _
ByVal SaveAsUI As Boolean, Cancel As Boolean)
'return statusbar to 'Ready' prior to saving so that you
' can see the 'Saving' progress bar
Application.StatusBar = False
'
End Sub
'/==================================================/
You want to make sure to show up-to-date information when you first go to a
worksheet, so you need something like...
'/==================================================/
Private Sub App_Stats_SheetActivate(ByVal Sh As Object)
Dim rng As Range
On Error Resume Next
Set rng = Selection
If Selection.Cells.Count < 2 Then
Application.StatusBar = False
GoTo exit_Sub
Else
' Call StatsOnStatusBar(Sh, rng)
Call StatsOnStatusBar(rng)
End If
exit_Sub:
If Err.Number <> 0 Then
Application.StatusBar = False
End If
Set rng = Nothing
End Sub
'/==================================================/
You want to make sure to show up-to-date information when you make changes
to a worksheet, so you need something like...
'/==================================================/
Private Sub App_Stats_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
On Error Resume Next
If Selection.Cells.Count < 2 Then
Application.StatusBar = False
GoTo exit_Sub
Else
' Call StatsOnStatusBar(Sh, Target)
Call StatsOnStatusBar(Target)
End If
exit_Sub:
If Err.Number <> 0 Then
Application.StatusBar = False
End If
End Sub
'/==================================================/
And of course you need the actual information to be put on the Status Bar...
'/==================================================/
Private Sub StatsOnStatusBar(ByVal Target As Range)
Dim strStatusBar As String, strStatus As String
strStatusBar = "My Deviation is: " & Application.WorksheetFunction.StDev
(Selection)
'[don't know if this formula works - untested]
Application.StatusBar = strStatusBar
End Sub
'/==================================================/
Save, get out of Excel and then back in.
--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown
"Mike K" wrote:
> Oh Wise Ones,
> I like excel's built-in ability to show the rolling
> average, count, etc.. in the status bar as you click and drag. Has anyone
> ever extended this ability to add standard deviation to the current options
> or via a similar method?
>
> Thanks,
> Mike
>
> xp pro v2002 sp2
> excel 2003
|
Similar Threads
