Microsoft Excel and Automation

Microsoft Excel is one of the best Office Applications ever happened in the history of Computer Software and changed the way we work with complicated calculations and analysis of Business information in a quick way. Originated from Visicalc, the first Worksheet Program conceived by Dan Bricklin, refined by Bob Frankston brought out in 1979 for Apple II Computers. Later, clones of VisiCalc were 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. Now, we are referring to the Main Memory in terms of MBs and GBs but 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 life 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 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 into 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 need three Worksheets each for keeping - Gross, Adjustment & 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 lot of time and you can reach the target area with the Click of a Button.

We will look into a simple example (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 given below:

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.