VBA – Visual Basic for Applications
VBA is a programming language that microsoft has included in Excel and some other office applications.
Below is a collection of code that I have found useful.
Do you want to continue? – Message box
ExitResponse = MsgBox(“Do you want to proceed?”, vbYesNo) If ExitResponse <> 6 Then Exit Sub
Delete all Shapes on a page
If Worksheets("Unit Status").Shapes.Count <> 0 Then ActiveSheet.Shapes.SelectAll Selection.Delete End If
User = Application.UserName ' Excel username User = ENVIRON("USERNAME") ' Windows username
Hide a Worksheet using Password Protection
Hide a sheet from other users. The password is recorded in the VBA code. The code can be password protected tincrease protection from casual users but is not likely to keep out determined ones.
Sub HideSheet() Password = InputBox("Enter Password") If Password = "" Then Exit Sub ' Cancel pressed or empty If Password <> “password” Then MsgBox Title:="Warning", prompt:="Incorrect Password" Exit Sub End If If Worksheets("Secret").Visible = True Then Worksheets("Secret").Visible = xlSheetVeryHidden Else Worksheets("Secret").Visible = True End If End Sub
Locate MyDocuments folder
Method 1 – Look in the Registry
Dim MyDocsPath As String MyDocsPath = System.PrivateProfileString( FileName:="", _ Section:="HKEY_CURRENT_USER\Software\Microsoft\" _ & "Windows\CurrentVersion\Explorer\Shell Folders", _ Key:="Personal")
' Get MyDocuments folder path Dim MyDocumentsDirectory Dim wshShell Set wshShell = CreateObject("WScript.Shell") MyDocumentsDirectory =wshShell.SpecialFolders("MyDocuments")
Name of Current Workbook
Msgbox = ThisWorkbook.name
Version of office installed
Determine what version of office is installed. Can be useful to prevent code running that is unsuitable for some versions
7 = Office 95 8 = Office 97 9 = Office 2000 10 = Office 2002 11 = Office 2003 12 = Office 2007 If Application.Version = 11 Then MsgBox “You have Office 2003 installed”
Enabling macros in Excel
If is not already enabled this can be done by selecting Options from the Tools drop down menu. Click on the Security tab followed by clicking the Macro Security button. Select either Medium Security (recommended) or Low Security (not recommended). Medium Security will give a prompt each time you open a excel document that has a macro included. Once you have changed the security settings you will need to close the workbook and open it again for the new settings to take effect.
If a spreadsheet contains macros in office 2007 you will usually get a security warning. Cliick on Options then Enable content followed by OK.