OzGrid Excel and VBA Newsletter AUGUST 2007

Excel Newsletter Index

EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS

Here are some excellent ways to enhance your Excel skills and/or VBA skills with training and/or software

EXCEL TIPS AND TRICKS

DEPENDENT LISTS WITH LOOKUP FUNCTIONS

We are going to look at how to use just ONE Vlookup Function to look in any number of named tables/ranges. To make it user friendly we are going to use a Data Validation trick with lists and have the 2 (or more) lists linked. That is, choosing an item from our 1st list will result in a corresponding list showing the second. To see how to do this, see Dependent Data Valiation Lists and even download the basic example.

1 VLOOKUP FOR MANY TABLES

Download Example

The principle we are going to use is the same as we used to link the 2 validation lists in the basic download example. That is, we use the Indirect Function which will allow Excel to see the content of any cell as either a range address, or, as in our case, a named range.

Our end Vlookup Function, based on the download example above, will be this;
=VLOOKUP($B$2,INDIRECT(SUBSTITUTE($A$2&2," ","_")),$C$2,FALSE)

$B$2 (Lookup_value) is the value we are going to be looking for.

INDIRECT(SUBSTITUTE($A$2&2," ","_")) (Table_array) is the named table we are going to look for $B$2 in and in the left most column of that table. Note the use of the SUBSTITUTE Function which substitutes any spaces with the underscore. This is because named ranges can never have spaces in their names. Also note the use of $A$2&2 and not simply $A$2! This is because we have already named the lists we use in the Data Validation list the same as whatever is chosen from A2. So, when we name our tables, we use the same name as their lists, but add a 2 on the end. For example, our table for Cities has been named "Cities2".

$C$2 (Col_index_num) contains a Data Validation list with the numbers 2 and 3 (only 3 columns in example). Our Vlookup will offset (right) that many columns from the left most column in the named table it looks in.

FALSE (Range_lookup) tells Vlookup we want an exact match (not case sensitive).

#STOP THE N/A!

As you change items in the Data Validation lists you will get #N/A! until a existing item or the correct named table is chosen. This can be rectified in a number of way, some less efficient that others though. See Stop #N/A! in Lookups

EXCEL VBA TIPS AND TRICKS

WORKING WITH SHAPES

Shapes are those from the Drawing toolbar or the Forms toolbar.

LISTING SHAPE PROPERTIES OF ACTIVE WORKSHEET

The code below will create a new Worksheet where the Shape Properties are listed under their appropriate heading.

Sub GetShapeProperties()
    Dim sShapes As Shape, lLoop As Long
    Dim wsStart As Worksheet, WsNew As Worksheet
    
    ''''''''''''''''''''''''''''''''''''''''''''''''
    ''''''''''LIST PROPERTIES OF SHAPES'''''''''''''
    ''''''''''Dave Hawley www.ozgrid.com''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''
    
    Set wsStart = ActiveSheet
    Set WsNew = Sheets.Add
     
    'Add headings for our lists. Expand as needed
    WsNew.Range("A1:F1") = _
     Array("Shape Name", "Shape Type", "Height", "Width", "Left", "Top")
     
     
    'Loop through all shapes on active sheet
    For Each sShapes In wsStart.Shapes
        'Increment Variable lLoop for row numbers
        lLoop = lLoop + 1
        With sShapes
            'Add shape properties
            WsNew.Cells(lLoop + 1, 1) = .Name
            WsNew.Cells(lLoop + 1, 2) = .OLEFormat.Object.Name
            WsNew.Cells(lLoop + 1, 3) = .Height
            WsNew.Cells(lLoop + 1, 4) = .Width
            WsNew.Cells(lLoop + 1, 5) = .Left
            WsNew.Cells(lLoop + 1, 6) = .Top
            'Follow the same pattern for more
        End With
    Next sShapes
     
    'AutoFit Columns.
    WsNew.Columns.AutoFit
End Sub

LISTING SHAPE PROPERTIES OF ALL WORKSHEETS

The code below will create a new Worksheet where the Shape Properties from all Worksheets are listed under their appropriate heading.

Sub GetShapePropertiesAllWs()
    Dim sShapes As Shape, lLoop As Long
    Dim WsNew As Worksheet
    Dim wsLoop As Worksheet
    
    ''''''''''''''''''''''''''''''''''''''''''''''''
    ''''''''''LIST PROPERTIES OF SHAPES'''''''''''''
    ''''''''''Dave Hawley www.ozgrid.com''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''
    
    Set WsNew = Sheets.Add
     
    'Add headings for our lists. Expand as needed
    WsNew.Range("A1:G1") = _
     Array("Shape Name", "Shape Type", "Height", "Width", "Left", "Top", "Sheet Name")
     
    'Loop through all Worksheet
    For Each wsLoop In Worksheets
        'Loop through all shapes on Worksheet
        For Each sShapes In wsLoop.Shapes
            'Increment Variable lLoop for row numbers
            lLoop = lLoop + 1
            With sShapes
                'Add shape properties
                WsNew.Cells(lLoop + 1, 1) = .name
                WsNew.Cells(lLoop + 1, 2) = .OLEFormat.Object.name
                WsNew.Cells(lLoop + 1, 3) = .Height
                WsNew.Cells(lLoop + 1, 4) = .Width
                WsNew.Cells(lLoop + 1, 5) = .Left
                WsNew.Cells(lLoop + 1, 6) = .Top
                'Follow the same pattern for more
                WsNew.Cells(lLoop + 1, 7) = wsLoop.name
            End With
        Next sShapes
    Next wsLoop
     
    'AutoFit Columns.
    WsNew.Columns.AutoFit
End Sub

LISTING SHAPE PROPERTIES OF SOME WORKSHEETS

The code below will create a new Worksheet where the Shape Properties from chosen Worksheets (those NOT named in Select Case) are listed under their appropriate heading.

Sub GetShapePropertiesSomeWs()
    Dim sShapes As Shape, lLoop As Long
    Dim WsNew As Worksheet
    Dim wsLoop As Worksheet
    
    ''''''''''''''''''''''''''''''''''''''''''''''''
    ''''''''''LIST PROPERTIES OF SHAPES'''''''''''''
    ''''''''''Dave Hawley www.ozgrid.com''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''
    
    Set WsNew = Sheets.Add
     
    'Add headings for our lists. Expand as needed
    WsNew.Range("A1:G1") = _
     Array("Shape Name", "Shape Type", "Height", "Width", "Left", "Top", "Sheet Name")
     
    'Loop through all Worksheet
    For Each wsLoop In Worksheets
        Select Case UCase(wsLoop.name)
           Case "SHEET5", "SHEET8" 'add sheet names to exclude
              'Do nothing
           Case Else
            'Loop through all shapes on Worksheet
            For Each sShapes In wsLoop.Shapes
                'Increment Variable lLoop for row numbers
                lLoop = lLoop + 1
                With sShapes
                    'Add shape properties
                    WsNew.Cells(lLoop + 1, 1) = .name
                    WsNew.Cells(lLoop + 1, 2) = .OLEFormat.Object.name
                    WsNew.Cells(lLoop + 1, 3) = .Height
                    WsNew.Cells(lLoop + 1, 4) = .Width
                    WsNew.Cells(lLoop + 1, 5) = .Left
                    WsNew.Cells(lLoop + 1, 6) = .Top
                    'Follow the same pattern for more
                    WsNew.Cells(lLoop + 1, 7) = wsLoop.name
                End With
            Next sShapes
         End Select
    Next wsLoop
     
    'AutoFit Columns.
    WsNew.Columns.AutoFit
End Sub

Got any Questions? Free Excel Help

Complete Excel Training Course Special!
We don't teach Excel from a manual, we teach Excel from experience!

Free Help Forum, not just Excel!

Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

FREE Download!

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

FREE Excel Help