Archive for the 'Computers' Category

Excel - getting an application event to fire when starting Excel

If I want this code to execute upon opening Excel:

Option Explicit
Public WithEvents mApp As Application

Private Sub mApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Dim vAvg As Variant 'to account for errors
Dim lCells As Long
Dim lCnt As Long
Dim vMax As Variant
Dim vMin As Variant
Dim vSum As Variant
Dim dCnta As Double

'Make sure selection is a range
If TypeName(Target) = "Range" Then

   'Only when more than one cell is selected
   If Target.Cells.Count > 1 Then

      'Caclulate stats
      vAvg = Application.Average(Target)
      lCells = Target.Cells.Count
      lCnt = Application.Count(Target)
      vMax = Application.Max(Target)
      vMin = Application.Min(Target)
      vSum = Application.Sum(Target)
      dCnta = Application.CountA(Target)

      'Concatenate statusbar message
      Application.StatusBar = "Average: " & CStr(vAvg) & " | " & _
      "Cell Count: " & lCells & " | " & _
      "Count Nums: " & lCnt & " | " & _
      "CountA: " & dCnta & " | " & _
      "Max: " & CStr(vMax) & " | " & _
      "Min: " & CStr(vMin) & " | " & _
      "Sum: " & CStr(vSum) & " | "

   Else
      'Return control of statusbar
      Application.StatusBar = False

   End If

Else
   Application.StatusBar = False

End If

End Sub

put it as a class module in personal.xls named SheetStatsClass

Then, have a normal code module in personal.xls include:

Public x As New SheetStatsClassSub InitializeApp()  

Set x.mApp = Application  

End Sub

Finally, have this code in ThisWorkbook in personal.xls:

Private Sub Workbook_Open()  

   InitializeApp  

End Sub

Excellent Local Library

I’ve never really talked about my local library. It has been the source for 90% of the books I’ve read since I started keeping track in October.

Serving a county of 71,295 (38,967 in the city, the rest rural), the library does an excellent job as far as having a wonderful collection of books, DVDs, and services. Starting in March, free wireless was offered, which I think is a hallmark of forward thinking. You can even email a reference librarian and ask a question.

Now I’ll pick a nit. They have an excellent bi-monthly newsletter that is distributed via the local paper (affectionately called by some locals, “the second worst paper in the country”, because there must surely be one that’s worse). Unfortunately they don’t offer it online, so if you don’t get the paper or what to refer to something 6 weeks later, you’re out of luck. But, again, that’s just a nit.

An Excel Blog

Daily Dose of Excel [via J-Walk Blog, who is a prolific Excel book writer]

Clear Type

One of the most disappointing things about my new computer was the quality of words on the flat screen. The monitor self-adjusts and I couldn’t find a way to improve the view.

Today I stumbled across the ClearType Tuner. No effect. Then I realized I better use Internet Explorer to view the page, not Firebird. Once I did that and downloaded the tuner, I was able to choose a text sample that looked best to me. Click it and now I can read my screen 100% better than before. Excellent!

(Now I can close Internet Explorer until the next Windows or Office Update is needed.)

How Small Can You Go?

Windows XP Box [via Simon’s Blogmarks]