Packaging the Application Packaging the application consists of bending the appearance and behavior of the Excel workspace to match your wishes. Many of the settings you'll need to change-such as the window size and the appearance of the toolbars-can also be customized by the user. Excel stores changes to those settings. For example, after a user changes which toolbars are visible, Excel saves the settings when the program closes and restores them the next time the program starts. If your application changes customization settings, it must restore them to the original state when the application closes. For example, if your application hides all the toolbars when it opens, it should redisplay the toolbars when it closes. Replace and Restore the Menu Bar A Windows application can have many toolbars but only one menu bar. The way to remove the Excel menu bar is to replace it with a custom menu bar. When the application closes, removing the custom menu bar automatically restores the standard Excel menu bar. A menu bar is simply a command bar-just like a toolbar-that you designate as a menu bar. You give the menu bar a name when you create it, and you can then use that name to delete it. For this application, name the menu bar EIS. -
In the Visual Basic Editor, add this procedure to the module: Sub ZapMenu() On Error Resume Next CommandBars("EIS").Delete End Sub The On Error Resume Next statement allows you to use this macro to ensure that the custom menu is deleted, without worrying about whether it was ever created or not. When the application is working perfectly, the menu bar should never exist when the macro creates it, and it should always exist when the macro deletes it; but while you're developing and testing the application, you might sometimes run the ZapMenu macro when the menu has already been deleted. In that case, having the macro ignore the error makes your life simpler. The menu bar for this application will have two commands: Exit and Return To Main. Before creating the macro that adds the custom menu bar, you can create the Sub procedures the commands will need. -
Add this procedure to the module: Sub ExitEIS() ZapMenu ActiveWorkbook.Close End Sub This procedure closes the active workbook. This is the macro that the Exit button will run. -
Add this procedure to the module: Sub ReturnToMain() Worksheets("Main").Select End Sub This procedure activates the Main worksheet. This is the macro that the Return To Main button will run. -
Now you're ready to add the macro that adds the custom menu bar. Click at the bottom of the module, and from the Insert menu, click File and double-click the Code12c file to add this procedure to the module: Sub SetMenu() Dim myBar As CommandBar Dim myButton As CommandBarButton ZapMenu Set myBar = CommandBars.Add(Name:="EIS", _ Position:=msoBarBottom, _ MenuBar:=True) Set myButton = myBar.Controls.Add(msoControlButton) myButton.Style = msoButtonCaption myButton.Caption = "E&xit" myButton.OnAction = "ExitEIS" Set myButton = myBar.Controls.Add(msoControlButton) myButton.Style = msoButtonCaption myButton.Caption = "&Return to Main" myButton.OnAction = "ReturnToMain" myButton.Visible = False myBar.Protection = msoBarNoMove msoBarNoCustomize myBar.Visible = True End Sub This macro consists of five parts separated by blank lines. The first part simply declares a couple of variables. The second part of the macro runs the ZapMenu macro to make sure the EIS menu bar doesn't already exist, and then it creates a new EIS menu bar. Passing True as the value of the MenuBar argument is what makes this new command bar into a menu bar. Putting the menu bar at the bottom of the screen makes it look less like a conventional menu bar. The third and fourth parts of the macro add the two commands to the menu bar. Initially, the Return To Main command is invisible. The fifth and final part of the macro protects the new menu bar. This property has an enumerated list of values that you can assign to it to control what you will and will not allow users to do to the menu bar. You can add values together to further control what you will allow. This macro doesn't allow the user to move or customize the new menu bar. -
Save the Chapter12 workbook, and run the SetMenu macro and the ZapMenu macro. In summary, replacing the Excel menu bar is easy-you just create a new one of your own. Restoring the Excel menu bar is even easier-you just delete the one you created. Show and Hide a Menu Command You still need to make the Return To Menu command visible whenever the Data worksheet becomes active, and to make it invisible whenever the Data worksheet becomes inactive. This looks like a job for event handler procedures-one to hide the command and one to show it. You can create a single procedure with an argument, and then you have the event handlers call that procedure. -
Insert this procedure into the module: Sub CommandVisible(IsVisible) On Error Resume Next CommandBars("EIS").Controls(2).Visible = IsVisible End Sub The On Error Resume Next statement again allows you to avoid inconveniences while building and testing the application-in case this procedure runs when the menu hasn't been created. The other statement makes the command visible or invisible, depending on the value of the argument. Project Explorer  -
Click the Project Explorer button, and double-click the entry for the Data worksheet. -
Insert these two event handler procedures: Private Sub Worksheet_Activate() CommandVisible True End Sub Private Sub Worksheet_Deactivate() CommandVisible False End Sub Whenever the Data worksheet becomes active, the Return To Menu command will become visible. Whenever the worksheet becomes inactive, the command will disappear. -
Reactivate the module, close the Project window, save the Chapter12 workbook, and run the SetMenu macro. -
Activate Excel, and switch back and forth between the Data and Main worksheets. Watch to see the command appear and disappear. -
Run the ZapMenu macro. Change and Restore Windows You can create a pleasant-looking package for the application by creating a window that's precisely the right size for the table and chart. When the application closes, however, you should restore the window to its previous state. Restoring the window is harder than restoring the Excel menu bar because you must make the macro remember the original size of the window. You can store the size of the window in a variable, but when you use Dim to declare a variable inside a procedure, the variable lasts only as long as the procedure is running. You can keep a variable from disappearing by using the word Static to declare the variable. -
Insert this partial procedure into the module: Sub SetWindow(State) Const myWidth = 540 Const myHeight = 340 Static myOldWidth Static myOldHeight Static myOldState End Sub You'll use this same procedure to change the window and to restore it. The State argument will determine which task the procedure will carry out. By using a single procedure for both tasks, you can store the old values right here in the SetWindow procedure using the Static keyword. The Const statements give the new custom values for the height and width. A Const is a constant value. You can use it like a read-only variable. Giving the width and height new values at the top like this makes them easy to change if you want to adjust your application later. -
Click in the blank line before the End Sub statement of the SetWindow procedure, and from the Insert menu, click File and then double-click the file Code12d to insert the following part of the macro: If State = xlOn Then myOldWidth = Application.Width myOldHeight = Application.Height myOldState = Application.WindowState Application.WindowState = xlNormal Application.Width = myWidth Application.Height = myHeight Application.Caption = "The Garden Company EIS" ActiveWorkbook.Unprotect ActiveWindow.WindowState = xlMaximized ActiveWindow.Caption = "" ActiveWorkbook.Protect , True, True ProtectSheet xlOn, "Main" ProtectSheet xlOn, "Data" Application.DisplayFormulaBar = False Application.DisplayStatusBar = False ActiveWindow.DisplayHorizontalScrollBar = False ActiveWindow.DisplayVerticalScrollBar = False ActiveWindow.DisplayWorkbookTabs = False This is the first half of an If…Else…End If structure. It runs if the value of the State argument is xlOn. The value xlOn is a built-in Excel constant. Using the constant makes the macro easier to read than using an arbitrary number, and using a built-in constant is easier than creating a custom constant. Setting the window consists of three parts. The first part stores the old height, width, and window states of the Excel application window in the static variables. It then assigns new values to those properties. When you resize the application window, you should always set the WindowState property to xlNormal first because if the application is maximized, you can't change the width or the height. This part also customizes the Excel application caption. The second part of setting the window makes sure that the workbook window is maximized and protected. You must unprotect it before attempting to maximize it. Setting the caption to an empty text string keeps the workbook name from appearing in the caption bar. The final statement of this part of the structure protects both the structure and the windows of the workbook. ProtectSheet is a procedure you'll create shortly that protects or unprotects a sheet. You give it the sheet name and specify whether protection should be on or off. The third part of setting the window is mostly for your convenience as you develop the application. You could protect the worksheets interactively, but then you'd always have to unprotect them interactively to make any changes. Likewise, you could hide the scroll bars, the sheet tabs, the formula bar, and the status bar interactively, but sometimes they're useful while you're developing the application. -
Click before the End Sub statement of the SetWindow procedure, and insert the file Code12e to add the remainder of the procedure: Else Application.Caption = Empty If Not IsEmpty(myOldWidth) Then Application.Width = myOldWidth Application.Height = myOldHeight Application.Top = myOldTop Application.Left = myOldLeft Application.WindowState = myOldState End If ProtectSheet xlOff, "Main" ProtectSheet xlOff, "Data" ActiveWorkbook.Unprotect Application.DisplayFormulaBar = False Application.DisplayStatusBar = False Application.DisplayFormulaBar = True Application.DisplayStatusBar = True ActiveWindow.DisplayHorizontalScrollBar = True ActiveWindow.DisplayVerticalScrollBar = True ActiveWindow.DisplayWorkbookTabs = True End If These statements are the second half of the If…Else…End If structure. Basically, they undo everything the statements in the first half did. Again, checking whether the myOldWidth variable is empty is for your convenience while you're developing the macro. When you make certain changes in Visual Basic for Applications-such as adding or deleting a procedure-the value of static variables can be lost, effectively replacing the value with zero. Checking to see whether the myOldWidth variable is empty keeps the macro from shrinking the application window to a tiny block on the screen if you happen to do something that resets the static variables. -
Click at the bottom of the module, and insert the file Code12f to add the ProtectSheet procedure: Sub ProtectSheet(State, SheetItem) If State = xlOn Then Worksheets(SheetItem).EnableSelection = xlNoSelection Worksheets(SheetItem).Protect , True, True, True, True Else Worksheets(SheetItem).Unprotect End If End Sub This is the macro that the SetWindow macro calls to protect a worksheet. Setting the EnableSelection property to xlNoSelection prevents the user from selecting any cells when the worksheet is protected. -
You need a way to run the SetWindow macro with the appropriate arguments. Insert these two macros in the module: Sub InitView() SetMenu SetWindow xlOn End Sub Sub ExitView() ZapMenu SetWindow xlOff End Sub -
Save the Chapter12 workbook, and test the InitView and ExitView procedures. -
Activate the Main sheet and click WA. The macro stops with an error when it attempts to hide the border lines on the chart. Even though the current protection for the worksheet should allow macro commands to execute, you do need to completely unprotect the worksheet in order to change the border of the chart. Fortunately, you can take advantage of the ProtectSheet procedure you created. -
Click Debug and type the statement ProtectSheet xlOff "Data" before the loop and ProtectSheet xlOn "Data" after the loop. The final code section of code should look like this: ProtectSheet xlOff "Data" For Each mySeries In myChart.SeriesCollection MySeries.Border.LineStyle=xlNone NextmySeriesProtectSheet xlOn"Data" -
Save the workbook, initialize the view, and click OR. Effectively protecting a workbook always requires a lot of testing and often requires minor adjustments to the code. Remove and Restore Toolbars The procedure for removing and restoring toolbars is very similar to that for changing and restoring windows. Store the old values before making changes, and then use the stored values to restore the workspace. Storing toolbars, however, adds a new twist. Storing the size of the window always requires exactly three static variables for three and only three values (height, width, and state), but storing the list of visible toolbars can involve an unknown and varying number of toolbars. As you know, Excel organizes multiple objects into collections. In fact, the toolbars themselves are in a collection. Visual Basic will actually allow you to create your own custom collection; you can make a collection of only those toolbars that need to be restored. Collections are powerful tools, and this example shows only a very simple (but extremely useful) way to take advantage of them. -
Click at the bottom of the module, and insert the file Code12g to create this procedure: Sub SetBars(State) Static myOldBars As New Collection Dim myBar If State = xlOn Then For Each myBar In Application.CommandBars If myBar.Type <> 1 And myBar.Visible Then myOldBars.Add myBar myBar.Visible = False End If Next myBar Else For Each myBar In myOldBars myBar.Visible = True Next End If End Sub Once again, a single procedure handles both the changing and the restoring so that a static variable can store the old values. This time, however, the static variable is declared as a New Collection. Declaring a variable as a New Collection tells Visual Basic that you want to create a collection of your own. The first half of the If…Else…End If structure loops through each of the items in the application's CommandBars collection. If the command bar is a menu bar, its Type property is 1 and you should not hide or restore it. Otherwise, if the command bar is visible, you want to add it to your custom collection and then make it invisible. To add an item to a custom collection, you use the Add method followed by a reference to the item you want to add. The second half of the If…Else…End If structure simply loops through the custom collection, unhiding every toolbar in it. -
You can launch SetBars from the InitView and ExitView macros, the same as you did with SetWindow. Insert the statement SetBars xlOn before the End Sub statement of the InitView macro. -
Insert the statement SetBars xlOff before the End Sub statement of the ExitView macro. -
Save the Chapter12 workbook, and test the InitView and ExitView procedures. This section didn't give details about all the ways you can use a custom collection, but even if you use a custom collection only for storing items from a standard collection- essentially copying the code from this chapter-you'll find it a valuable tool. Complete the Package All the pieces are in place for the finished application. You just need to make it happen automatically when the workbook opens. -
Activate the Project Explorer window, and double-click ThisWorkbook. -
Insert this event handler for when the workbook opens: Private Sub Workbook_Open() Application.ScreenUpdating = False ProtectSheet xlOff, "Data" ActiveWorkbook.PivotCaches(1).Refresh InitView StartUpAnimation End Sub Every time the workbook opens, you want to check for new data in the database, customize the environment, and play the initial animation. To check for new data, you refresh the PivotCache that stores the data for the PivotTable. There is only one PivotTable in the workbook, so you can simply specify the first item in the PivotCache collection. You do, however, need to be sure that the worksheet containing the PivotTable is unprotected before refreshing the PivotCache. Fortunately, the ProtectSheet procedure you previously created is easy to use. Setting ScreenUpdating to False restricts the amount of flashing you see on the screen. -
Insert this event handler for when the workbook closes: Private Sub Workbook_BeforeClose(Cancel As Boolean) ExitView ActiveWorkbook.Saved = True End Sub Every time the workbook closes, you want to restore the environment. You also want to keep Excel from asking whether to save changes. Setting the Saved property of the active workbook to True makes Excel believe that it has been saved, so it doesn't ask. -
In the ExitEIS macro, insert the statement ExitView before the ActiveWorksheet.Close statement. The Workbook_BeforeClose event handler needs to run ExitView in case the user closes the workbook by clicking the Excel Close Window button. | Tip | Theoretically, the ExitEIS macro shouldn't have to run ExitView. ExitEIS closes the window, and the event handler should run when the window closes regardless of what causes it to close. For some reason, however, the event handler doesn't run the ExitView macro if the ExitEIS macro triggered the event. It's just another reminder that Visual Basic for Applications was created by humans. | -
If you have a digital signature, sign the Visual Basic for Applications project to avoid the warning message when you open it. -
In Excel, hide the ChartData sheet. (On the Format menu, point to the Sheet submenu and click the Hide command.) -
Add a new sheet if necessary and name it Blank. Turn off the row and column headers. Select a cell several rows and columns away from cell A1. Save the workbook while the Blank sheet is active so that the user won't see anything when the workbook first opens. -
Close the workbook, and reopen it. Test the application, and close the workbook. The application is beautiful. It has functionality. It has an effective user interface. It's well packaged. Congratulations! |
