Announcing Multiple Columns

by admin
  1. Announcing Multiple Columns Pictures
  2. Announcing Multiple Columns Meaning
  3. Announcing Multiple Columns Images
  4. Announcing Multiple Columns Definition

Get the latest breaking news, sports, entertainment and obituaries in Augusta, GA from The Augusta Chronicle. The list contains large amount of text in a column and it would be nice to display the first 5 lines of text followed by a Read more link. I've seen examples of this for Sharepoint 2010 but not for. For higher volume uses, try announcement templates that can be printed 4 to a page. There’s even a handy and downloadable “Meeting in Progress” sign. Choose from the variety of free announcement templates with pre-built designs like the engaged couple profile, graduation announcement, sales flyer, event flyer, and party invitation.

-->

Most applications manipulate and display sets of data, such as a gallery of images or a set of email messages. The XAML UI framework provides ListView and GridView controls that make it easy to display and manipulate data in your app.

Important APIs: ListView class, GridView class, ItemsSource property, Items property

Note

ListView and GridView both derive from the ListViewBase class, so they have the same functionality, but display data differently. In this article, when we talk about list view, the info applies to both the ListView and GridView controls unless otherwise specified. We may refer to classes like ListView or ListViewItem, but the List prefix can be replaced with Grid for the corresponding grid equivalent (GridView or GridViewItem).

ListView and GridView provide many benefits for working with collections. They are both easy to implement and provide basic UI; interaction; and scrolling while still being easily customizable. ListView and GridView can be bound to existing dynamic data sources, or hard-coded data provided in the XAML itself/the code-behind.

These two controls are flexible to many use cases, but overall work best with collections in which all items should have the same basic structure and appearance, as well as the same interaction behavior - i.e. they should all perform the same action when clicked (open a link, navigate, etc).

Differences between ListView and GridView

ListView

The ListView displays data stacked vertically in a single column. ListView works better for items that have text as a focal point, and for collections that are meant to be read top to bottom (i.e. alphabetically ordered). A few common use cases for ListView include lists of messages and search results. Collections that need to be displayed in multiple columns or in a table-like format should not use ListView, but should look into using a DataGrid instead.

GridView

The GridView presents a collection of items in rows and columns that can scroll vertically. Data is stacked horizontally until it fills the columns, then continues with the next row. GridView works better for items that have images as their focal point, and for collections that can be read from side-to-side or are not sorted in a specific order. A common use case for GridView is a photo or product gallery.

Which collection control should you use? A Comparison with ItemsRepeater

ListView and GridView are controls that work out-of-the-box to display any collection, with their own built-in UI and UX. The ItemsRepeater control also is used to display collections, but was created as a building block for creating a custom control that fits your exact UI needs. The most important differences that should impact which control you end up using are below:

  • ListView and GridView are feature-rich controls that require little customization but offer plenty. ItemsRepeater is a building block to create your own layout control and does not have the same built in features and functionality - it requires you to implement any necessary features or interactions.
  • ItemsRepeater should be used if you have a highly custom UI that you can’t create using ListView or GridView, or if you have a data source that requires highly different behavior for each item.

Learn more about ItemsRepeater by reading its Guidelines and API Documentation pages.

Examples

XAML Controls Gallery

If you have the XAML Controls Gallery app installed, click here to open the app and see the ListView or GridView in action.

Create a ListView or GridView

ListView and GridView are both ItemsControl types, so they can contain a collection of items of any type. A ListView or GridView must have items in its Items collection before it can show anything on the screen. To populate the view, you can add items directly to the Items collection, or set the ItemsSource property to a data source.

Important

You can use either Items or ItemsSource to populate the list, but you can't use both at the same time. If you set the ItemsSource property and you add an item in XAML, the added item is ignored. If you set the ItemsSource property and you add an item to the Items collection in code, an exception is thrown.

Many of the examples in this article populate the Items collection directly for the sake of simplicity. However, it's more common for the items in a list to come from a dynamic source, like a list of books from an online database. You use the ItemsSource property for this purpose.

Add Items to a ListView or GridView

You can add items to the ListView or GridView's Items collection using XAML or code to yield the same result. You typically add items through XAML if you have a small number of items that don't change and are easily defined, or if you generate the items in code at run time.

Method 1: Add items to the Items Collection

Announcing Multiple Columns Pictures

Option 1: Add Items through XAML

Option 2: Add Items through C#

C# Code:
Corresponding XAML Code:

Both of the above options will result in the same ListView, which is shown below:


Method 2: Add items by setting the ItemsSource

You typically use a ListView or GridView to display data from a source such as a database or the Internet. To populate a ListView/GridView from a data source, you set its ItemsSource property to a collection of data items. This method works better if your ListView or GridView is going to hold custom class objects, as shown in the examples below.

Option 1: Set ItemsSource in C#

Here, the list view's ItemsSource is set in code directly to an instance of a collection.

C# Code:
XAML Code:

Option 2: Set ItemsSource in XAML

You can also bind the ItemsSource property to a collection in the XAML. Here, the ItemsSource is bound to a public property named Contacts that exposes the Page's private data collection, _contacts.

XAML

C#

Both of the above options will result in the same ListView, shown below. The ListView only shows the string representation of each item because we did not provide a data template.

Important

With no data template defined, custom class objects will only appear in the ListView with their string value if they have a defined ToString() method.

The next section will go into detail on how to visually represent simple and custom class items properly in a ListView or GridView.

For more info about data binding, see Data binding overview.

Note

If you need to show grouped data in your ListView, you must bind to a CollectionViewSource. The CollectionViewSource acts as a proxy for the collection class in XAML and enables grouping support. For more info, see CollectionViewSource.

Customizing the look of items with a DataTemplate

A data template in a ListView or GridView defines how the items/data are visualized. By default, a data item is displayed in the ListView as the string representation of the data object it's bound to. You can show the string representation of a particular property of the data item by setting the DisplayMemberPath to that property.

However, you typically want to show a more rich presentation of your data. To specify exactly how items in the ListView/GridView are displayed, you create a DataTemplate. The XAML in the DataTemplate defines the layout and appearance of controls used to display an individual item. The controls in the layout can be bound to properties of a data object, or have static content defined inline.

Note

When you use the x:Bind markup extension in a DataTemplate, you have to specify the DataType (x:DataType) on the DataTemplate.

Simple ListView Data Template

In this example, the data item is a simple string. A DataTemplate is defined inline within the ListView definition to add an image to the left of the string, and show the string in teal. This is the same ListView created from using Method 1 and Option 1 shown above.

XAML

Here's what the data items look like when displayed with this data template in a ListView:

ListView Data Template for Custom Class Objects

In this example, the data item is a Contact object. A DataTemplate is defined inline within the ListView definition to add the contact image to the left of the Contact name and company. This ListView was created by using Method 2 and Option 2 mentioned above.

Here's what the data items look like when displayed using this data template in a ListView:

Data templates are the primary way you define the look of your ListView. They can also have a significant impact on performance if your list holds a large number of items.

Your data template can be defined inline within the ListView/GridView definition (shown above), or separately in a Resources section. If defined outside of the ListView/GridView itself, the DataTemplate must be given an x:Key attribute and be assigned to the ItemTemplate property of the ListView or GridView using that key.

For more info and examples of how to use data templates and item containers to define the look of items in your list or grid, see Item containers and templates.

Change the layout of items

When you add items to a ListView or GridView, the control automatically wraps each item in an item container and then lays out all of the item containers. How these item containers are laid out depends on the ItemsPanel of the control.

  • By default, ListView uses an ItemsStackPanel, which produces a vertical list, like this.
  • GridView uses an ItemsWrapGrid, which adds items horizontally, and wraps and scrolls vertically, like this.

You can modify the layout of items by adjusting properties on the items panel, or you can replace the default panel with another panel.

Note

Be careful to not disable virtualization if you change the ItemsPanel. Both ItemsStackPanel and ItemsWrapGrid support virtualization, so these are safe to use. If you use any other panel, you might disable virtualization and slow the performance of the list view. For more info, see the list view articles under Performance.

This example shows how to make a ListView lay out its item containers in a horizontal list by changing the Orientation property of the ItemsStackPanel.Because the list view scrolls vertically by default, you also need to adjust some properties on the list view’s internal ScrollViewer to make it scroll horizontally.

  • ScrollViewer.HorizontalScrollMode to Enabled or Auto
  • ScrollViewer.HorizontalScrollBarVisibility to Auto
  • ScrollViewer.VerticalScrollMode to Disabled
  • ScrollViewer.VerticalScrollBarVisibility to Hidden

Important

These examples are shown with the list view width unconstrained, so the horizontal scrollbars are not shown. If you run this code, you can set Width='180' on the ListView to make the scrollbars show.

Announcing Multiple Columns Meaning

XAML

The resulting list looks like this.

In the next example, the ListView lays out items in a vertical wrapping list by using an ItemsWrapGrid instead of an ItemsStackPanel.

Important

The height of the list view must be constrained to force the control to wrap the containers.

XAML

The resulting list looks like this.

If you show grouped data in your list view, the ItemsPanel determines how the item groups are layed out, not how the individual items are layed out. For example, if the horizontal ItemsStackPanel shown previously is used to show grouped data, the groups are arranged horizontally, but the items in each group are still stacked vertically, as shown here.

AnnouncingAnnouncing multiple columns images

Item selection and interaction

You can choose from various ways to let a user interact with a list view. By default, a user can select a single item. You can change the SelectionMode property to enable multi-selection or to disable selection. You can set the IsItemClickEnabled property so that a user clicks an item to invoke an action (like a button) instead of selecting the item.

Announcing Multiple Columns Images

Note Both ListView and GridView use the ListViewSelectionMode enumeration for their SelectionMode properties. IsItemClickEnabled is False by default, so you need to set it only to enable click mode.

This table shows the ways a user can interact with a list view, and how you can respond to the interaction.

To enable this interaction:Use these settings:Handle this event:Use this property to get the selected item:
No interactionSelectionMode = None, IsItemClickEnabled = FalseN/AN/A
Single selectionSelectionMode = Single, IsItemClickEnabled = FalseSelectionChangedSelectedItem, SelectedIndex
Multiple selectionSelectionMode = Multiple, IsItemClickEnabled = FalseSelectionChangedSelectedItems
Extended selectionSelectionMode = Extended, IsItemClickEnabled = FalseSelectionChangedSelectedItems
ClickSelectionMode = None, IsItemClickEnabled = TrueItemClickN/A

Note Starting in Windows 10, you can enable IsItemClickEnabled to raise an ItemClick event while SelectionMode is also set to Single, Multiple, or Extended. If you do this, the ItemClick event is raised first, and then the SelectionChanged event is raised. In some cases, like if you navigate to another page in the ItemClick event handler, the SelectionChanged event is not raised and the item is not selected.

You can set these properties in XAML or in code, as shown here.

XAML

C#

Read-only

You can set the SelectionMode property to ListViewSelectionMode.None to disable item selection. This puts the control in read only mode, to be used for displaying data, but not for interacting with it. The control itself is not disabled, only item selection is disabled.

Single selection

This table describes the keyboard, mouse, and touch interactions when SelectionMode is Single.

Modifier keyInteraction
None
  • A user can select a single item using the space bar, mouse click, or touch tap.
  • Ctrl
  • A user can deselect a single item using the space bar, mouse click, or touch tap.
  • Using the arrow keys, a user can move focus independently of selection.
  • When SelectionMode is Single, you can get the selected data item from the SelectedItem property. You can get the index in the collection of the selected item using the SelectedIndex property. If no item is selected, SelectedItem is null, and SelectedIndex is -1.

    If you try to set an item that is not in the Items collection as the SelectedItem, the operation is ignored and SelectedItem isnull. However, if you try to set the SelectedIndex to an index that's out of the range of the Items in the list, a System.ArgumentException exception occurs.

    Multiple selection

    This table describes the keyboard, mouse, and touch interactions when SelectionMode is Multiple.

    Modifier keyInteraction
    None
  • A user can select multiple items using the space bar, mouse click, or touch tap to toggle selection on the focused item.
  • Using the arrow keys, a user can move focus independently of selection.
  • Shift
  • A user can select multiple contiguous items by clicking or tapping the first item in the selection and then the last item in the selection.
  • Using the arrow keys, a user can create a contiguous selection starting with the item selected when Shift is pressed.
  • Extended selection

    This table describes the keyboard, mouse, and touch interactions when SelectionMode is Extended.

    Modifier keyInteraction
    None
  • The behavior is the same as Single selection.
  • Ctrl
  • A user can select multiple items using the space bar, mouse click, or touch tap to toggle selection on the focused item.
  • Using the arrow keys, a user can move focus independently of selection.
  • Shift
  • A user can select multiple contiguous items by clicking or tapping the first item in the selection and then the last item in the selection.
  • Using the arrow keys, a user can create a contiguous selection starting with the item selected when Shift is pressed.
  • When SelectionMode is Multiple or Extended, you can get the selected data items from the SelectedItems property.

    The SelectedIndex, SelectedItem, and SelectedItems properties are synchronized. For example, if you set SelectedIndex to -1, SelectedItem is set to null and SelectedItems is empty; if you set SelectedItem to null, SelectedIndex is set to -1 and SelectedItems is empty.

    In multi-select mode, SelectedItem contains the item that was selected first, and Selectedindex contains the index of the item that was selected first.

    Respond to selection changes

    To respond to selection changes in a list view, handle the SelectionChanged event. In the event handler code, you can get the list of selected items from the SelectionChangedEventArgs.AddedItems property. You can get any items that were deselected from the SelectionChangedEventArgs.RemovedItems property. The AddedItems and RemovedItems collections contain at most 1 item unless the user selects a range of items by holding down the Shift key.

    This example shows how to handle the SelectionChanged event and access the various items collections.

    XAML

    C#

    Click mode

    You can change a list view so that a user clicks items like buttons instead of selecting them. For example, this is useful when your app navigates to a new page when your user clicks an item in a list or grid.To enable this behavior:

    • Set SelectionMode to None.
    • Set IsItemClickEnabled to true.
    • Handle the ItemClick event to do something when your user clicks an item.

    Here's a list view with clickable items. The code in the ItemClick event handler navigates to a new page.

    XAML

    MeaningMultiple

    C#

    Select a range of items programmatically

    Sometimes, you need to manipulate a list view’s item selection programmatically. For example, you might have a Select all button to let a user select all items in a list. In this case, it’s usually not very efficient to add and remove items from the SelectedItems collection one by one. Each item change causes a SelectionChanged event to occur, and when you work with the items directly instead of working with index values, the item is de-virtualized.

    The SelectAll, SelectRange, and DeselectRange methods provide a more efficient way to modify the selection than using the SelectedItems property. These methods select or deselect using ranges of item indexes. Items that are virtualized remain virtualized, because only the index is used. All items in the specified range are selected (or deselected), regardless of their original selection state. The SelectionChanged event occurs only once for each call to these methods.

    Important

    You should call these methods only when the SelectionMode property is set to Multiple or Extended. If you call SelectRange when the SelectionMode is Single or None, an exception is thrown.

    When you select items using index ranges, use the SelectedRanges property to get all selected ranges in the list.

    If the ItemsSource implements IItemsRangeInfo, and you use these methods to modify the selection, the AddedItems and RemovedItems properties are not set in the SelectionChangedEventArgs. Setting these properties requires de-virtualizing the item object. Use the SelectedRanges property to get the items instead.

    You can select all items in a collection by calling the SelectAll method. However, there is no corresponding method to deselect all items. You can deselect all items by calling DeselectRange and passing an ItemIndexRange with a FirstIndex value of 0 and a Length value equal to the number of items in the collection. This is shown in the example below, along with an option to select all items.

    Announcing Multiple Columns Definition

    XAML

    C#

    For info about how to change the look of selected items, see Item containers and templates.

    Drag and drop

    ListView and GridView controls support drag and drop of items within themselves, and between themselves and other ListView and GridView controls. For more info about implementing the drag and drop pattern, see Drag and drop.

    Get the sample code

    • XAML ListView and GridView sample - Demonstrates the ListView and GridView controls.
    • XAML Drag and drop sample - Demonstrates drag and drop with the ListView control.
    • XAML Controls Gallery sample - See all the XAML controls in an interactive format.

    Related articles

    Most Recent Posts‎ > ‎

    XLOOKUP() - the Easy Multi-Key VLOOKUP()

    posted Dec 8, 2017, 6:55 AM by Craig Hatmaker [ updated Dec 7, 2020, 1:37 PM]
    NOTE! This was originally posted Dec 8, 2017. On Aug 29, 2019 Microsoft introduced it's own XLOOKUP(). Excel's new XLOOKUP shares the same name and includes this functionality, but, I'm sure it's coincidence.
    Or is it?

    Searching Excel tables on multiple criteria is a pain. So I created XLOOKUP(), a fast and easy to use VBA function that also works well as a UDF (User Defined Function).
    Syntax:
    [Return Column],

    =XLOOKUP(MyTable,'MyField','MatchThis','MatchThisToo')
    This returns the value in MyTable[MyField] where MyTable's first column matches 'MatchThis' and the second column matches 'MatchThisToo'.

    TableCan be a table name (ex: 'MyTable') or a table references (ex: MyTable) or any reference inside the table (ex: $A$1, MyTable[MyField]). I do not recommend using cell references because formulas can break if the table is moved.
    Return ColumnCan be either a table column # (ex: 2) or a column heading ('MyField'). I recommend headings because they help self-document formulas and formulas won't break if columns are rearranged.
    Lookup Key(s)Can be one or more values to search for. The first value must be located in the first column. The second value must be in the second column and so forth.

    • XLOOKUP() only works with tables because tables are awesome and no one with XL 2007 or later should be making lists that aren't tables.
    • This only works with keys located in the left most columns (like VLOOKUP) because unique identifiers should always be on the left. BUT! If you need to match columns that could be anywhere in a table and in any order, see MLOOKUP()
    • Can't we do the same thing with VLOOKUP()?
      Not without adding helper columns to data (see: https://exceljet.net/formula/vlookup-with-multiple-critiera).
    • Can't we do the same thing with INDEX()/MATCH()?
      Yes, if we concatenate keys and columns in an array formula which is how this works. The major difference is ease of use. XLOOKUP() is much easier to setup and read/understand.
    • If INDEX/MATCH can do the same thing, why go to so much effort to create this routine?
      Because looking up data in tables is something we do very often so I wanted something that is as easy to setup as possible, as easy to read and maintain as possible, and fast.
    Summary:
    XLOOKUP() is amazingly easy to use and fast. I built it primarily to simplify common VBA tasks but it also works well as a UDF. So if our project requires VBA we could use XLOOKUP() because its column heading parameters document formulas better than VLOOKUP's column number, and XLOOKUP is far less complex than INDEX()/MATCH() combinations as long as our keys are in the left most columns. And if our keys are not in the left most column, see MLOOKUP().

    This code may not be the most up-to-date. The most up-to-date version can be downloaded from here:

    WARNING! Do not just copy and paste this code. It will not compile as is. The code below is from module modGeneral which includes required module level constants, module level options, and the DspErrMsg function. It is shown here in isolation for discussion purposes. See the Demo Download section (just above this) for how to get this routine in proper context.

    NOTE! Most of this code is comments, making sure inputs are correct and reporting errors. This is particularly important with UDFs.
    Public Function XLookup(ByVal vTable As Variant, _
    ParamArray vKeyVals() As Variant) As Variant
    ' Description:Multiple Key (Left most) lookup
    ' vResult Result Column (heading or #)
    ' Outputs: Me Success: Cell
    ' Requisites: *None
    ' Example: ?XLookup('tblCities', 'USA', 'VA', 'Norfolk', 'Population')
    ' Date Ini Modification
    ' 12/20/17 CWH Added special section for 1 key lookup
    ' Declarations
    Dim oLo As ListObject 'Table containing data
    Dim sCol As String 'Column Address Range to search
    Dim lKey As Long 'Current key
    Dim lCol As Long 'Found Column
    On Error GoTo ErrHandler

    ' Table
    Select CaseTypeName(vTable)
    Case Is = 'ListObject': SetoLo = vTable
    Case Is = 'Range': SetoLo = vTable.ListObject
    Case Else: SetoLo = ActiveSheet.Evaluate(vTable).ListObject
    End Select
    ' Return Column
    If TypeName(vResult) = 'Range' Then vResult = vResult.Value2
    If UBound(vKeyVals) = -1 Then Err.Raise DspError, , '#Key(s) required'
    ' When called by VBA, ParamArrays sometimes are stuffed in the first element
    If IsArray(
    vKeyVals(LBound(vKeyVals))) Then _
    vKeys =
    vKeyVals(LBound(vKeyVals)) Else _
    vKeys =
    vKeyVals
    ' Procedure
    If Not .DataBodyRange Is Nothing Then
    ' Just 1 key - Use Worksheet.Function because it is fastest w/1 Key
    vKey = vKeys(UBound(vKeys))
    lRow = Application.WorksheetFunction.Match( _
    .ListColumns(1).DataBodyRange, _
    ' More than 1 key - Use Worksheet.Evaluation because it is fastest w/multiple keys
    ' Concatenate Key Values and Search Column Addresses
    sCol = IIf(sCol <> vbNullString, sCol & ' & ', vbNullString) & _
    If TypeName(vKeys(lKey)) = 'Range' Then _
    If IsDate(vKeys(lKey)) Then _
    vKey = vKey & vKeys(lKey)
    ' Find Row # by Evaluating MATCH within the Table's worksheet
    lRow = .Parent.Evaluate('=Match('' & vKey & '',' & sCol & ',0)')
    ' Get Column #
    ' Return result
    End If
    ' Create sAddTxt (Additional Error Text) if needed
    Case Is = 9: sAddTxt = 'Column ' & vResult & ' not found in ' & oLo.Name
    Case Is = 13, 1004: sAddTxt = 'Key(s) ' & Join(vKeys, ',') & ' not found'
    End Select
    ' Customize Errors based on UDF of VBA caller
    If TypeName(Application.Caller) = 'Range' Then 'Called from UDF
    Debug.Print cRoutine & ':' & Err.Description & vbLf & sAddTxt
    Select Case Err.Number
    Debug.Print cRoutine & Err.Description & vbLf & sAddTxt
    Select Case DspErrMsg(cModule & '.' & cRoutine, sAddTxt)
    Case Is = vbAbort: Stop: Resume 'Debug mode - Trace
    Case Is = vbIgnore: 'End routine
    End Select
    End If
    End Function

    Some of the techniques used in this routine are above the beginner level. If that's you, know that everyone starts as beginners and that a good way to advance is to study more advanced code. Below are some areas that I believe most beginners will have difficulty understanding.

    The ability to handle as many table keys as needed is what sets this routine apart. ParamArray vKeyVals() makes this possible. ParamArray instructs VBA to place all parameters from this point and forward into an array and pass the array to our routine. When using ParamArraywe must be aware of a few things. ParamArray:
    1. Must always be the last parameter in the parameter list
    2. Can have all of its values stuck into its first array element when call by VBA. If this happens in XLOOKUP(), vKeys(0) contains the array it needs.
    Arrays
    VBA's arrays normally start with 0. Exceptions include when arrays are created from ranges or if the module where the array is created has Option Base 1 at its top. For this reason it is a safe practice to use LBOUND() and UBOUND() to determine the array's lowest and upper dimension values.
    NOTE! If LBOUND() of an array variable is -1 the array variable has not be set. See Err.Raise below.


    Dates
    Dates are tricky. Excel stores dates as single numbers (ex 42767) also known as Microsoft Excel date-time code. As you know Excel often displays dates with separate year, month, and day values (ex. 2/1/2017) and dates can be displayed in various formats. Those formatted values are kept in the cell's VALUE and TEXT properties. The single number valueis in the cell's VALUE2 property. Excel's MATCH() function looks at each cell's VALUE2 property so date lookup parameters must be the single number version. For this reason XLOOKUP() uses cell parameters' VALUE2 for all range values.
    If it were not for the possibility of errors this routine would just be a dozen lines. XLOOKUP() uses BXL's standard error handling routines. So if you are using BXL's DspErrMsg() and module naming standards this will work as is. If not, you must:
    1. Replace DspErrMsg() with your own routine and its module level constants (DspError).
    2. Either remove all references to cModule or define that in the routine or in your module's header.
    We should handle errors differently depending on if we call XLOOKUP() from a spreadsheet formula or from VBA. We can determine which is the case by examining TypeName(Application.Caller). If it equals 'Range', XLOOKUP() was called by a UDF as part of an Excel formula; otherwise, it was called by a VBA routine.
    • If we call XLOOKUP() from the spreadsheet we must return one of Excel's #ERROR values. In this case XLOOKUP() returns #REF if one of the parameters is wrong by setting XLookup = CVErr(xlErrRef).
    • If we call XLOOKUP() from a VBA routine XLOOKUP():
    • Returns results as objects or nothing so we can always SET our result variables.
    • Reports errors in message boxes.
    BXL routines have a section labeled Check Inputs and Requites. This section makes sure parameters are passed correctly and requisites are in place before processing begins. Incorrect parameters and/or missing requisites are not VBA errors but they are errors to the routine. We can let VBA handle these errors like VBA errors using the Raise method of the Err object via Err.Raise.

    This routine is designed primarily to simplify finding cells (the range object, not just the value) using multiple keys. Where performance is critical and VLOOKUP works (sorted list,single key in left most column, return value not range) use VLOOKUP because VLOOKUP is by far quicker. But for multiple key lookups, over larger tables this is very nearly as fast as it gets using VBA. Below are the results recorded from modCustom.Test in the demo download.

    Time comparison in milliseconds of multiple key lookups over table CDC - 10,000 iterations.
    Keywords:
    Free, Excel, XL, Tutorial, How To, Errors, UDF, User Defined Function, VBA, VLOOKUP, INDEX, MATCH, Download, Craig Hatmaker, BXL, Beyond Excel