< Continued from page 2

Let's write a program that creates a new customized worksheet automatically to see how this works. I created a background graphic that I want to be used as a background for my new customized worksheet. I also want to save my customized workbook with a different default name in a specific location.

For this example, I'm going to use the Personal Macro Workbook to save the VBA macro. You might want to start with a fresh, blank workbook as you do this example.


- >Click Record Macro so Excel will record the steps of customizing a worksheet and saving it.
- >In the Page Layout ribbon, select Background and browse to the graphic file that you want to be used as a background.
- >Select File > Save As and browse to the folder that you want to use to save your customized workbook. Give the file a new name before saving it.
- >Click the Stop Recording button to stop the Recorder.

(Remember to click Yes when Excel asks you whether you want to save the Personal.xlsb workbook when you close Excel.)

Now let's examine the VBA program that has been recorded. Click Visual Basic to open the editor as before and then click Module1 under Personal.xlsb to display the VBA code window.

Sub AVBLogoBackground()'' AVBLogoBackground Macro' Creates a new spreadsheet with' the About logo as a background.'ActiveSheet.SetBackgroundPicture Filename:= _"C:\Users\TEMP\ExcelApp\aboutlogo.bmp"ActiveWorkbook.SaveAs Filename:= _"C:\Users\TEMP\ExcelApp\AboutVBWB.xlsx", _FileFormat:= _xlOpenXMLWorkbook, CreateBackup:=FalseEnd Sub

(I added line continuations to the statements to break them into shorter lines.)

--------
Click Here to display the illustration
--------

This VBA program only has two statements in it.

ActiveSheet.SetBackgroundPicture(Followed by the path and file for the graphic you select.)ActiveWorkbook.SaveAs(Followed by parameters for the SaveAs method.)
This simple program might be most valuable as an example. If you were writing a program and you couldn't remember the way to write the code for statements like these, you could quickly record this macro and then just copy what you need to your own program.

While this program will do the job, it still has at least one serious problem. When you Run it a second time, the program attempts to save another copy of the same XLS workbook on top of the first one and displays a confirmation dialog asking if you want to overwrite it. What you want is something more like what Excel itself does. In Excel, if you select File > New and Book1.xlsx already exists, Excel will create a workbook file as Book2.xlsx.

How can we modify our program to work the way Excel does? One way would be to save the current maximum value of a sequence number added to the name of the workbook. But we would need a storage location that's always available. We're in luck! Excel makes sure that the Personal Workbook is always available.

The main purpose of the Personal Workbook is to provide a place to store your personal VBA programs. But it's also a normal workbook. Since we have to have one anyway (because Excel needs a host file for the VBA program) we can use the spreadsheet part of it to save the sequence number of our custom file.

To accomplish this, we have to "Unhide" the Personal Workbook. Select Unhide under the View ribbon. Personal.xlsb will probably be the only file. The Personal.xlsb workbook should then be visible. I simply added a description and the starting value 0 in a cell. (I also changed the name of the worksheet to AboutParms mainly to add some documentation to the workbook.)

--------
Click Here to display the illustration
--------

Now open Personal.xlsb in the Visual Basic Editor again. The code that you recorded earlier should still be in it. Modify the code so that it looks like the code shown below with your file paths and names substituted where noted. (You can use longer lines of code instead of the line continuations I used to keep the lines short.)

ActiveSheet.SetBackgroundPicture Filename:= _"C:\Users\TEMP\ExcelApp\aboutlogo.bmp"Workbooks("Personal.xlsb").Worksheets(1).Range("A2") = _CStr(CInt(Workbooks( _"Personal.xlsb").Worksheets(1).Range("A2") + 1))Workbooks("Personal.xlsb").SaveActiveWorkbook.SaveAs Filename:= _"C:\Users\TEMP\ExcelApp\ExcelApp\AboutVBWB" & _Workbooks("Personal.xlsb"). _Worksheets(1).Range("A2") & ".xlsx", _FileFormat:= _xlOpenXMLWorkbook, CreateBackup:=False
This program gets the current maximum sequence number from cell A2 of Personal.xlsb and adds 1 to it. The workbook is saved for use when the macro is executed again in the future. Then the number is concatenated to the end of the file name and the updated file is saved.

The real point of this example is to demonstrate how you could automate any spreadsheet operation. You could, for example, insert a default financial calculation, get some key numbers from the user of your spreadsheet, or even update a database across a network.