Ozgrid, Experts in Microsoft Excel Spreadsheets
Javascript DHTML Drop Down Menu Powered by dhtml-menu-builder.com
Learn how to create Excel dashboards.

Ozgrid Excel Newsletter. Excel Newsletter Archives  

VISIT OUR SPECIALS PAGE | ADVANCED SEARCH |FREE EXCEL & VBA LIFETIME SUPPORT | FREE DEMO DOWNLOADS

Create a Personal View of Your Workbooks

Excel enables you to have multiple workbooks showing simultaneously, and to have a customized view of your workbooks arranged in different windows. Then you can save your view workspaces as .xlw files and use them when it suits you.

Works with all versions of Excel

Sometimes when working in Excel, you might need to have more than one workbook open on your screen. This makes it easier to use or view data from multiple workbooks. The next few paragraphs describe how to do this in a neat and organized way.

First, open all the workbooks you will need.

To open more than one workbook at a time, select the Office button>Open..., press the Ctrl key while selecting the workbooks you want to open, and then click Open. (Users of pre-2007 versions select File>Open)

From any of the workbooks (it doesn’t matter which one), select View>Arrange All under Windows options. (for versions earlier than 2007, select Window>Arrange). If “Windows of active workbook” is checked, uncheck it, and then select the window arrangement you prefer and click OK.

If you select Tiled, you will be presented with your workbooks in a tiled fashion, as shown with blank workbooks in Figure 1-1.

Selecting Horizontal gives you a view of your workbooks in a single stack, one on top of the other, as in Figure 1-2.

Checking the Vertical option will place all your open workbooks side by side, as shown in Figure 1-3.

Finally, as shown in Figure 1-4, selecting the Cascade option will layer all your open workbooks one on top of the other.


Once your workbooks are displayed in your preferred view, you can easily copy, paste, drag-and-drop, etc., between them.

If you think you might want to return to a view you created, you can save this preferred view as a workspace. To save a workspace, simply select the Save Workspace choice under Window options (File>Save Workspace in previous versions), enter the workspace’s filename in the File Name box, and click OK. When saving your workspace, the file extension will be .xlw rather than the standard .xls. To restore your Excel workspace to one full window of a particular workbook, just double-click the blue title bar appearing on any one of your workbooks. You can also click the Maximize button on any of the windows in your workspace. Close your workbooks as usual when you’re finished.

Whenever you need to open those same workbooks, simply open the *.xlw file, and the view you initially set up will be magically restored for all workbooks. If you need to open just one of these workbooks, open the file as usual. Any changes you make to the workbooks in the *.xlw file will be saved automatically as you close the workspace as a whole, or you can save workbooks individually.

If you spend a small amount of time setting up some custom views for repetitive tasks that require multiple open workbooks, you’ll find that these tasks become easier to manage. You might decide to use different views for different repetitive tasks, depending on what the task is or how you’re feeling that day.

EXCEL VBA: Working with arrays

This Month I would like to work with array and their elements. In case you are not aware, array elements start at Zero and NOT 1. You can force array elements to start at 1 by using;
Option Base 1
at the very top of the Module.

FILLING ARRAYS

To fill an array we can use 1 of 2 methods.

Sub FillArray1()
'Can store up to 10 string elements
Dim strArray(9) As String
Dim lLoop As Long

    For lLoop = 0 To 9
        If lLoop = 0 Then
            'Choose doesn't work with Zero
            strArray(lLoop) = "Zoo"
        Else
            strArray(lLoop) = Choose(lLoop, "Farm", "Paddock", "Sheep", _
                        "Cow", "Bird", "Mice", "Chicken", "Fence", "Post", "Lamb")
        End If
    Next lLoop
    
    'Loop from 0 to UpperBound array element. 0-9
    For lLoop = LBound(strArray) To UBound(strArray)
       MsgBox strArray(lLoop)
    Next lLoop
End Sub

The second method requires that we use a Variant for our Array as we have both numbers and text;

Sub FillArray2()
Dim strArray() As Variant
Dim lLoop As Long

            strArray = Array("Zoo", 2, "Paddock", "Sheep", _
                        "Cow", "Bird", 3, "Chicken", "Fence", "Post", "Lamb")

    'Loop from 0 to UpperBound array element. 0-9
    For lLoop = LBound(strArray) To UBound(strArray)
       MsgBox strArray(lLoop)
    Next lLoop
End Sub

Or, if we want to put a range of cells into an Array;

Sub ArrayOfCells()
Dim vArray()

'Transpose IF range is vertical
vArray = Application.Transpose(Range("A1:A10"))

'Display each element in our array
For lLoop = LBound(vArray) To UBound(vArray)
  MsgBox vArray(lLoop)
Next lLoop

End Sub

Next Month we will look at how to output Arrays to Ranges.

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Become an ExcelUser Affiliate & Earn Money

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft