Excel VBA

VBA – Visual Basic for Applications

VBA is a programming language that microsoft has included in Excel and some other office applications.

Tips

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

Get username

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")


Method 2

' 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

Excel 2003

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.

Excel 2007

If a spreadsheet contains macros in office 2007 you will usually get a security warning. Cliick on Options then Enable content followed by OK.

Enabling macros in Excel 2007

Advertisements
  1. This is a message for Garry Edmonds

    Hi Garry,

    I was looking for a method to create an activation code for an MS Access application I am writing. I found your Excel code for the Vigenere cypher online and modified it slightly as part of a larger piece of VBA code to produce an activation code containing an expiry date.

    I would like to use your code in the final product. It isn’t something I’m going to make a mass of money out of. it is for a single customer who will distribute this to his franchisees.

    I hope this is OK, please let me know otherwise. I am quite happy for you to have the code creation app is you wish. (it’s written in Access 2010)

    Many thanks for your code and your consideration

    Cheers

    Tony Greenwood
    Derbyshire, UK

  2. Want to copy posts from other pages rewrite them in seconds and post on your page, or use for contextual backlinks?
    You can save a lot of writing work, just search in google:
    Daradess’s Rewriter

  3. Hi!!
    I am supposed to make a tic tac toe game for a school exhibition.I wanted the second player to be computer so can you please tell me code for doing it…Its Urgent…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: