VBA - Visual Basic for Applications

If you ever wondered how to determine, if an opened workbook was saved before below you'll find a code snippet that might do the job for you.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
'@Author - Alexander Bolte 
'@ChangeDate - 2014-05-30 
'@Description - Returns true, if a workbooks path property 
'is unequal an empty string. 
'This is true only if the workbook has been saved. 
'@Param wrk - an initialized Excel Workbook object. Function fails, if Nothing is handed. 
'@Returns true, if a given workbook has been saved. Else False.
Public Function wasWorkbookEverSaved(ByRef wrk As Workbook) As Boolean
  Dim ret As Boolean
  
  ret = wrk.path <> ""
  
  wasWorkbookEverSaved = ret
End Function

 

I am Mr. Forgetful. What was this password again?

Anyway, if you do not know any better, use brute force!

Source Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
' @Author - Alexander Bolte
' @ChangeDate - 2013-12-15
' @Description - Removes the password from a protected worksheet using brute force.
' Select a protected worksheet and start the sub.
Sub PasswordBreaker()
    Dim i As Integer, j As Integer, k As Integer
    Dim l As Integer, m As Integer, n As Integer
    Dim i1 As Integer, i2 As Integer, i3 As Integer
    Dim i4 As Integer, i5 As Integer, i6 As Integer
    Dim trg As Worksheet
  
    On Error Resume Next
  
    Set trg = ActiveSheet
  
    For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
    For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
    For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
    For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
        trg.Unprotect Chr(i) & Chr(j) & Chr(k) & _
            Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
            Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
        If trg.ProtectContents = False Then
            MsgBox "One usable password is " & Chr(i) & Chr(j) & _
              Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
              Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n), vbInformation, ";0)"
            Exit Sub
        End If
    Next: Next: Next: Next: Next: Next
    Next: Next: Next: Next: Next: Next
End Sub

Here we have two more functions, which have done me great service over the last years.

Instead of duplicating the code for switching on and off certain Excel application specific events, alerts and so on you can call these two functions before and after each function call reachable through an user interface.

Version & Remarks

Version Date Description
0.2 2015-02-01 Now preventing user interaction / input with Excel through keyboard or mouse.

 

2015-02-01

I've written a script to postprocess some data extracted from a database for one of my clients. Processing took quite some time and a collegue came up to me showing me on my keyboard how his wife typed in something wrong. Great! the script was only half way through and stopped, now showing my collegues input in a cell in one of the referenced Excel workbooks. Luckily I had a log file and could continue from there.

Lessons learned - prevent a user from interacting with Excel while you are processing something in VBA. Below a reference from the according Microsoft documentation for the Interactive property of the Excel Application object. Before I made this experience I always thought that this would happen automatically.

"If you set the this property to False, Microsoft Excel will block all input from the keyboard and mouse (except input to dialog boxes that are displayed by your code). Read/write Boolean."

However you will still be able to interrupt code exceution pressing the escaoe button.

Source Code

Make sure to place the function call of switchOnApp always after an error handling in the top calling function. Do not place calls to below functions in any function, which is called by other functions / subs. Else you would end up disabling and enabling application properties, when you actually don't want to enable or disable them.

'@Author - Alexander Bolte
'@ChangeDate - 2014-05-30
'@Description - Switches on all events, automated claculation and alerts in Excel. Also switches security to standard in Excel Security Center.
Public Sub switchOnApp()
' reset security settings
Application.AutomationSecurity = msoAutomationSecurityByUI
Application.EnableEvents = True
Application.DisplayAlerts = True
If Not (ActiveSheet Is Nothing) Then
Application.Calculation = xlAutomatic
End If
Application.ScreenUpdating = True
Application.EnableAnimations = True
Application.Interactive = True
End Sub

'@Author - Alexander Bolte
'@ChangeDate - 2014-05-30
'@Description - Switches off all events, automated claculation and alerts in Excel. Also switches security to low in Excel Security Center in case macro enabled workbooks have to be processed.
Public Sub switchOffApp()
' Disable all alerts.
Application.DisplayAlerts = False
' Disable all event handling.
Application.EnableEvents = False
' Enable macros on workbooks opened through automation.
Application.AutomationSecurity = msoAutomationSecurityLow
' Disable automated calculation.
If Not (ActiveSheet Is Nothing) Then
Application.Calculation = xlManual
End If
Application.EnableAnimations = False
Application.ScreenUpdating = False
Application.Interactive = False
End Sub

Referenced API

Important referenced APIs are listed below.

Subcategories

This category will hold articles regarding developement in Excel VBA. It will serve as a wiki and an Excel VBA Framework for myself.

Some development tasks reoccur for every customer. Since I am a lazy bum it will be nice to have a central source where I can reuse source code from.

This category holds articles regarding general things in MS Office VBA independent from the MS Office application.  

This category holds articles regarding Access VBA, but also general things I come accross Access and its usage in companies.