MS Office: Excel and Automation

Author: Ramachandran Pillai

Microsoft Excel is one of the best office applications in the history of computer software and changed the way we work with complicated calculations and analysis of business information. Originated from Visicalc, the first worksheet program conceived by Dan Bricklin, refined by Bob Frankston and brought out in 1979 for Apple II Computers. Later, clones of VisiCalc emerged with more powerful features. SuperCalc in 1980, Multiplan in 1982, Lotus 1-2-3 in 1983, Spread Sheet Module in AppleWorks in 1984, Microsoft Excel for Macintosh in 1985 and for Windows 2.0 in 1987. (Source: http://en.wikipedia.org/wiki/VisiCalc).

I have worked with Lotus 1-2-3 under DOS. These days, we are referring to main memory in terms of MBs and GBs but in those days only 640KB maximum free main memory was available. Lotus 1-2-3 could run within these limited resources. There was an add-on program - Worksheet Publisher – for Lotus 1-2-3 to print the Worksheet contents and Graphs, as we print them today under Windows. It was an exciting experience to print the worksheets with fonts of different sizes in graphic shapes and graph charts with various shades on dot matrix printers, when these kinds of output from computers were very rare in those days. I came across the macro language in Lotus 1-2-3 and did some form of automation with customized menus.

Microsoft Excel is loaded with many features that can make our lives easier for home and business needs alike. We will look into the usage of a one line macro or VBA Statement and you can judge yourself how powerful it is. But to use that statement we have to do some ground work. Those of you who have not tried any of this before, let it be a starting point and I am sure that you will love it. I hope it will give you more insight into the power of Microsoft Excel and inspire you to look beyond what you see here.

We are not going to dwell on any complicated calculations or expressions. We will try to move the control from one worksheet to a distant worksheet among several worksheets, within the workbook, quickly with one click of a button.

Assume that our WorkBook has 12 month’s Account Receivable details and each month needs three worksheets each for keeping Gross, Adjustment, and Net values. That is 36 worksheets in all and reaching out to any one of these sheet’s contents requires several clicks or scrolling. But, if we carefully plan, organize, and automate some of the frequently used actions then it saves lots of time and you can reach the target area with the click of a button.

We will look into a simple example (it may not be simple for you for the first time) to see how to set up a Control Worksheet with two List boxes and a Command Button for our automation task. One List Box for Months, another one for Worksheet Category Names: Gross, Adjustment & Net and a Command Button to Click and transfer control to the selected Worksheet Area Quickly.

1. To try out our example, open a new WorkBook

and save it with the name Sample.xls. Create 8 Worksheets in the Workbook and change the Names of them as given below. Right-Click on the Worksheet and select Rename option and Type the new Name.

· Ctrl_Data

· Control

· JAN_NET

· JAN_ADJ

· JAN_GRS

· FEB_NET

· FEB_ADJ

· FEB_GRS

2. Select the Ctrl_Data Worksheet.

3. Enter JAN, FEB to DEC in Cells A1 to A12, see the image below:

Excel 2

4. Highlight the Cells A1 to A12 and Name the range as MTHLIST (select Insert - - > Name - - > Define and type MTHLIST in the Names in Workbook Control and Click OK).

5. Enter GRS, ADJ,NET in Cells B1, B2, and B3. Highlight B1 to B3 and define the name as SHEETLIST.

6. Select Cell A16 and Name the Cell as MTH.

7. Select Cell B16 and Name the Cell as RNG.

8. Select Cell B18 and Name the Cell as GotoName.

9. Write the Formula =MTH&"_"&RNG in Cell B18.

10. You may Format the Named Cells with Borders so that they can be spotted easily.

11. Now, we have to name a Range (say A1:D10 or even A1 alone will do) in each Worksheet from JAN_NET to FEB_GRS with the same name like JAN_NET in JAN_NET Worksheet and so on. The Worksheet name can be different but the range name must be as indicated.

12. Select the Control Sheet and highlight the Range: B2:I20 and fill with a gray Color so that it looks like a Form. See the image below:

13. Display the Control Toolbox (View - -> Toolbars - - > Control Toolbox).

14. Click on the Design Mode Control with the Green colored Triangle.

15. Click on the List Box Control and draw a List Box, tall enough to hold all 12 months Description.

16. While the List Box is still selected Click on the Properties Control on the Toolbox, which will display the Property Sheet of the List Box.

17. Change the following Property Values as given below and leave others as it is:

· BoundColumn : 1

· ColumnCount : 1

· ColumnWidths : 30 pt

· LinkedCell : MTH

· ListFillRange : MTHLIST

Now you will find all twelve Months’ Description appears in the List Box.

18. Create another List Box to the right of the Month’s List to hold GRS, ADJ and NET descriptions.

19. Select the List Box and display the Property Sheet.

20. Set the First three property values same as above and change the next two property values to RNG and SHEETLIST respectively.

21. Select the Command Button Tool from the Toolbox and draw a Command Button to the right of the List Boxes.

22. Display the Property Sheet and change the Caption Property Value to Go To and change the (Name) Property Value to CommandButton1, if it is different there.

23. Select the View Code Button on the Toolbox. You will find the VBA Code Module Window and the following Subroutine skeleton (the top and bottom lines). If the first line is different then select Click from the right-side Drop-down control.

Private Sub CommandButton1_Click()

Application.Goto Reference:=Worksheets("Ctrl_Data").Range("GotoName").Value

End Sub

24. Write the line given in the middle without mistakes giving all the punctuations correctly.

25. Click on the Design Mode Button on the Control Tool Box again to de-select it.

26. We are ready to test our Worksheet selection Automation. Select the month FEB from the first List Box and ADJ in the second List Box.

27. Click on the Command Button. If you followed the steps described above strictly you will find the control jumps to the Range you have named FEB_ADJ and the range is highlighted in FEB_ADJ Worksheet.

28. Open the Ctrl_Data Sheet and view how the selected values from the List Boxes are appearing in the Cells, which we have named MTH and RNG and how the Cell B18 formula combines them together to match the Range Names that we have given on Worksheet JAN to FEB Worksheets.

You may add all twelve months Worksheets in the same manner and try out.

Part 2 of 2

We have seen that with a simple Macro Statement we can transfer control to a particular area of a Worksheet very quickly, when several Worksheets are in a Workbook. We have designed a ControlSheet and a Control Data Sheet to store the user’s choices and use those values for running the Program.

Excel Macros can make our work easier by automating several tasks like Print Previewing or Printing of Worksheets, hiding all worksheets except the one that we are working on, transfer data from other worksheets to a specific range linked with the Graph Chart so that the Chart changes dynamically and no need for creating several Charts across sheets and so on.

We can bring in lot of improvement in that example Workbook that we have created earlier and in this Article we will concentrate on a simple issue, specific to what we did with the earlier macro. We have seen that we can transfer control to a particular worksheet among several worksheets in order to avoid scrolling sheets to the right to find the specific sheet location that we want to work with. If you have tried that example, then a Question is already on your mind that how do we pass control back to the Control Sheet, without clicking on the Tab Scrolling Buttons?

Since we have transferred control with a macro we need another macro to bring the control back to Main worksheet where we have designed the List boxes and Command Button for automation. But this macro we cannot run from the Main Control Sheet. We will run it conveniently from a Toolbar Button above. We need to create a custom Toolbar Button and attach the new Macro to the Toolbar Button. But, the new Toolbar Button will remain in the machine where you will create it. If the same workbook is open in a different machine the Button will not be available there, but we can make the Custom Toolbar Button to travel with the Workbook so that it can be used in any machine.

First we will write a Macro and then go for the Custom Toolbar Button creation.

1. Select Tools - -> Macros - -> Visual Basic Editor.

2. Click on the Project Explorer Toolbar Button (or press Ctrl+R) to split the Visual Basic Editor window into two. Left side panel will list the Worksheet names and the right-side window is for VBA Code editing. Check the left side panel under the heading This Workbookshows a Folder with the name Modules. If you are using the same workbook that you have created for the earlier example then this will be there. If the Module Folder is there then double click on the VBA Module below that to open up the VBA Code Editing window. If the Module Folder is not there then select Insert - -> Moduleto insert a Global VBA Module.

3. Click on the Code window at the right side and select Procedurefrom InsertMenu.

4. Type the Name GotoControlin the Namecontrol and select Subin the TypeCategory.

5. Select Publicin the Scopechoices.

6. Click OKto insert the empty Procedure skeleton in the VBA Window.

7. Type the following Macro Statementwithout making mistakes in the middle of the two lines. See that you are keying in a period (.) between the right parenthesis and the word select.

Worksheets ("Control") .select

1. Select View - -> Toolbars - -> Customize.

2. Select the Toolbars Tab (if it is not the current one)

3. Click New to create a new Empty Toolbar and type a Name (say myToolbar) in the Toolbar Name control and click OK. An Empty Toolbar will show up, look for it if you could not spot it. It may appear anywhere within the Excel Application Area. You may drag and place it among the existing Toolbars above.

4. Click on the Commands Tab on the Customize dialog control.

5. Click on File in the Categories List and Click New...

6. Right-Click on the Button and type &Go2Ctrl in the NameControl.

7. Select Text Only option in the displayed menu. The Name Go2Ctrl appears in the Button face.

8. Click on the Assign Macro to display all the existing Macros available and among them you will find the GotoControl that we have created, click on it and Click OK and Close the Customize Dialog Control.

9. Now we are ready to test our creation. Click on the Go2Ctrl Toolbar Button and you will be placed on the Control Worksheet.

10. Click on the GotoCommand Button after selecting a Worksheet and Month from the List to transfer control to that sheet.

11. Try clicking again on the Go2Ctrl Toolbar Button to jump the control back to the Main Worksheet.

Now, we need to make one more change for the Toolbar to travel along with the Workbook to other machines.

1. Select View - -> Toolbar - -> Customize.

2. Click on the Attach...Button.

3. You will find Custom Toolbars List at the left-side panel. Select myToolbarfrom the list and Click CopyButton to make a copy to the Toolbars in WorkbookList.

4. Click OK to Close the Customize dialog box.

If your Excel File can be put on a Network Drive then you can try opening it from a different Machine. The Custom Toolbar with the Go2CtrlButton will come along with the Workbook on that machine also.

About The Author

A.P.R. Pillai is a developer of several fully secured Microsoft Access Applications for Corporate Houses. From September 2006 onwards, publishing Blog Articles on Microsoft Access Programming Tips and Tricks, with easy to follow Examples & VBA Source Code. Web Site: http://www.msaccesstips.com