Thread: What's in Your Personal.xls?

I agree add-ins are probably better and I use those when I've written something to share with others but if it's just for me I use my personal.xls and keep a copy in my Google Docs so I can download it wherever I want.

Dom

- a toggle between manual and automatic calculation

I have this, it's called 'dumbass mode button', when I click it my statusbar shows 'warning, dumbas mode enabled'

Dom, I'd really appreciate a look at your compare spreadsheets code, if you don't mind?

Charlie, I liked your Compare function. It will definitely come in handy when validating huge spreadsheets with complex formulas. Thanks!

You're welcome to it although it's probably not quite what you're thinking of from my post. It's just some code that I use to compare the output of SAP reports that we've run pre and post applying patches to see if they've had any effect on the output of the reports.

It highlights the differences and sticks hyperlinks to the first 255 in the first couple of rows of the second worksheet (I work on the fact that if there's more than 255 differences we've got real problems )

I wrote this quite a long time ago so it could be greatly improved.

Code:

Sub CompareSheets()
    
    Dim strPreSheet As String, strPostSheet As String
    Dim wsPresheet As Worksheet, wsPostsheet As Worksheet
    Dim rngLoopRange As Range, lngNErrors As Long
    Dim strDifferentCells(256) As String, strOutput As String, C As Long
    
    strPreSheet = InputBox("Enter the name of the first worksheet")
    strPostSheet = InputBox("Enter the name of the second (output) worksheet")
    
    If strPreSheet = "" Or strPostSheet = "" Then
        MsgBox "Procedure cancelled", vbCritical
        Exit Sub
    End If
    
    Set wsPresheet = Sheets(strPreSheet)
    Set wsPostsheet = Sheets(strPostSheet)
 
    If wsPresheet.UsedRange.Rows.Count <> wsPostsheet.UsedRange.Rows.Count Then
        MsgBox "Different number of rows in worksheets", vbCritical
        Exit Sub
    End If
    
    If wsPresheet.UsedRange.Columns.Count <> wsPostsheet.UsedRange.Columns.Count Then
        MsgBox "Different number of columns in worksheets", vbCritical
        Exit Sub
    End If

    lngNErrors = 0
    
    For Each rngLoopRange In wsPostsheet.UsedRange
        
        If rngLoopRange.Value <> wsPresheet.Range(rngLoopRange.Address).Value Then
            
            rngLoopRange.Interior.ColorIndex = 3
            
            If (lngNErrors < 256) Then
            
                strDifferentCells(lngNErrors) = rngLoopRange.Offset(2, 0).Address
            
            End If
            
            lngNErrors = lngNErrors + 1
        
        End If
    
    Next rngLoopRange
    
    If (lngNErrors > 0) Then
    
        With wsPostsheet
    
            .Rows("1:2").Insert Shift:=xlDown
            .Range("A1") = "Found " & lngNErrors & " differences."
    
            For C = 0 To lngNErrors - 1
        
                If C > 255 Then Exit For
        
                .Hyperlinks.Add Anchor:=.Range("A2").Offset(0, C), Address:="", SubAddress:= _
                    strDifferentCells(C), TextToDisplay:=strDifferentCells(C)

            Next C
            
        End With
    
    Else
    
        MsgBox "No errors found!!!"
    
    End If
    
End Sub

Dom

Normally I can't be bothered with tidying up functional code - even it could benefit from it, but this:

Code:

strPreSheet = InputBox("Enter the name of the first worksheet")
    strPostSheet = InputBox("Enter the name of the second (output) worksheet")

Made me write this, general tool for picking either any worksheet in the active workbook, or any open workbook - something I've written individual versions of too many times... not any more!

Code:

Function Picker(iPickType As Integer, Optional sMessage As String, Optional sCaption As String) As String
'ipicktypes:
'1 = worksheets in active book
'2 = open workbooks
Dim wb As Workbook
Dim ws As Worksheet
With fPicker
    .Caption = sCaption
    .Label1.Caption = sMessage
    .ComboBox1.Clear
    Select Case iPickType
    Case 1
        For Each ws In ActiveWorkbook.Worksheets
            .ComboBox1.AddItem (ws.name)
        Next ws
    Case 2
        For Each wb In Workbooks
            If wb.name <> "PERSONAL.XLS" Then
                .ComboBox1.AddItem (wb.name)
            End If
        Next wb
    End Select
    .Show
    Picker = .ComboBox1.Value
End With
End Function

Supported (obviously?) by a very simple userform with one label, one combobox and two buttons, one (cancel) clears the combobox entry before hiding, (ok) just hides the userform.

If Picker returns "" then consider it a cancel in either case...

Not sure it was worth the endeavour, but I needed a break, work's killing me today!

FWIW I put together an example of this working for this post...

http://www.excelforum.com/excel-prog...o-compare.html

Dom

Here's another alternative

I couldn't resist...

:D Know the feeling

I like the use of choose (all of them), but I'm not a fan of iif. (spit).

Why add the elements to a string then split the string into the box rather than add the items straight into the box? (I don't mean to make any judgement either way, just curious).