VBA - The Excel Working Partner

Visual Basic

Filed In:

Visual Basic

VBA - The Excel Working Partner

Customizing the Personal Workbook

More of this Feature

• Part 1: Using VBA with Microsoft Excel
• Part 2: Record a Code Sample For Yourself
• Part 3: Learning From Your Code Sample

Join the Discussion

Do you use Excel VBA?
Tell us how !

Related Resources

• Beginning Visual Basic
• Visual Basic 6

From Other Guides

• About Business Software

Elsewhere on the Web

• An Excel VBA book
From the great publisher, APress
• Excel Updates from Microsoft
(Browse to Office 2000)

The VBA program is saved in this same workbook. Use the familiar Tools > Macro > Visual Basic Editor to open Module1 in PERSONAL.XLS. The code that you recorded earlier should still be in it. Modify the code so that it looks like this with your file paths and names substituted where noted. Also note that you can use longer lines of code instead of the line continuations I used to keep the lines short. Finally, don't include the numbers in the left margin. These are there only to identify the changes to the recorded code.

Sub Macro1()
    ActiveSheet.SetBackgroundPicture Filename:= _
        "C:\{your graphic file path}\{your graphic file}"
    ChDir _
        "C:\{your spreadsheet folder path}"
1   Workbooks("Personal.xls").Worksheets(1).Range("A2") = _
        CStr(CInt(Workbooks("Personal.xls"). _
 	Worksheets(1).Range("A2") + 1))
2   Workbooks("Personal.xls").Save
3   ActiveWorkbook.SaveAs Filename:= _
        "C:\{your spreadsheet folder path}\AboutWB" & _
        Workbooks("Personal.xls"). _
 	Worksheets(1).Range("A2") & ".xls", _
        FileFormat:=xlNormal, Password:="", _
	WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

The statement marked 1 is completely new. This statement adds 1 to the number found in cell A2 of Personal.xls. Statement 2 then saves the updated file. Finally, statement 3 has been changed to add the number from the Personal.xls spreadsheet at the end of the file name, AboutWB and before the file qualifier .xls. Everything else is exactly as created by the Recorder.

Now that we have a VBA program that does what we want, we can attach it to a button or change the Sub name so that it is called by an Excel event in a way similar to what we did in our Word VBA program. To test the program, you can simply go to Tools > Macro > Macros (or press Alt-F8). Notice how a new default document is created in the folder you selected in the VBA code.

Final Custom Workbook

You may not need a VBA program to creat a new default worksheet, but the real point of the example is to demonstrate how you could do any kind of initialization when you open Excel. You could, for example, create a default financial calculation, open a form (as we did with Word) or get some key numbers from the user of your spreadsheet. In more advanced applications, we could query a database or even retrieve data from the Internet.

In the next segment, we're going to combine Excel and Word and demonstrate how to use both together in the same application.

Download the Personal.xls and the About graphic file (includes all of the VBA) and try it yourself!

First page > Using VBA with Microsoft Excel > Page 1, 2, 3, 4

Guide since 2002

Dan Mabbutt
Visual Basic Guide

Sign up for my Newsletter

Explore Visual Basic

Must Reads