Daily Dose of Excel

Data Validation is a nice feature for restricting cell inputs, but sometimes you want the user to be able to add an item to the DV list. With the Worksheet_Change event and a dynamic range name, you can allow the user to do just that.

First, set up a dynamic named range for your list

Note the first item in the list is “(new entry)”. This will be used to trigger the macro that allows the user to add to the list. Next, create the data validation

Finally, create the code in the Change event to catch when the user selects (new entry).

You can beef up the code to make sure the user is entering something reasonable. The code can also be modified for use with a hard-coded list, as opposed to a range. But I have to leave something for you to experiment with.

This one’s for Ron, who posted a comment yesterday. If you have names in various forms like

First Last
First M. Last
First Last, Jr.

and you need to split them apart, then you need some text manipulation functions. I can’t explain it any better than Chip Pearson on his Extract First and Last Names page, so I refer you there.

When using a MultiSelect ListBox, there’s no built-in way to preserve the user’s selections from one instance of a Userform to the next. When I need to do that, I use a hidden worksheet and store the selections in a range. Then I use the Initialize and QueryClose events to restore the selections.

Private Sub UserForm_Initialize()

    Dim i As Long
    Dim Rng As Range
    
    ‘define the range where the selections are stored
    Set Rng = ThisWorkbook.Sheets(”lbSelections”).Range(”lbStored”)
    
    ‘clear previously stored selections
    Rng.Parent.UsedRange.ClearContents
    
    ’store the current selections
    For i = 0 To Me.ListBox1.ListCount - 1
        Rng.Cells(i + 1).Value = Me.ListBox1.Selected(i)
    Next i
    
    ‘redefine the named range just in case the number of items have changed
    Rng.Parent.Names.Add “lbStored”, _
        Rng.Parent.Range(”A1″).Resize(Me.ListBox1.ListCount)
    
End Sub

The hidden worksheet will show a bunch of TRUE and FALSE entries that correspond to what was selected. These values are then used to set the Selected property.

The SQRT() function can be used to return the square root of a number. To find a different root, use the exponentiation operator (^ - the caret above the 6) and the inverse of the root. For instance:

=27^(1/3) returns the cube root of 27, or 3.

=16^(1/4) returns the 4th root of 16, or 2.

Put the “inverse” calculation in parentheses because exponentiation takes precedent over division, and you want the division to happen first.

Excel doesn’t provide MAXIF and MINIF functions, but you can roll your own with array functions.

array entered will return 7, the max for Tom

array entered will return 1, the minimum for Tom.

You’ll note that the MIN function is a little less straightforward. The problem with MIN and array formulas is that it almost always returns zero. For instance, if the MIN was written like the MAX, then any rows with ‘Harry’ will return zero and that will be the minimum value in the array. To get around that, an IF function is used to return the values for the name we want and an empty string for any other names. The empty string (like any string) is ignored by the MIN function.

Update: Another application - John comments

I have a column of numbers (16520, 16345, 19456, 16123, 16201, 19325, 19113, 16350, 16787, 16675) and want to use a function to find the highest number in the 16000 group. I think I need to use the MAX and IF Worksheet Functions, but I can’t get it. Any help?

Try this formula

VBA includes a built-in Timer function. Timer returns the number of seconds since 12:00 AM. Windows users get some decimals, but Mac users have to settle for the nearest second. I’ve never been all that convinced of Timer’s accuracy anyway. If a fraction of a second is important, I’d be testing Timer before I used it to test my application.

Inspired by a previous comment, here’s an example of how you might use Timer

Sub test()

End Sub

And the results

My analysis of this is that it doesn’t matter which one you use. For me, I use the Value property for values and the Formula property for formulas. Call me old fashioned.

Array variables can be static or dynamic. That’s determined by the Dim statement. If you specify dimensions when you declare the variable, it’s static and always will be. If you leave the dimensions blank in the Dim statement, it’s dynamic and can be changed.

Dim Array1(1 To 10) As String ’static array
Dim Array2() As String ‘dynamic array

Dynamic arrays can be changed using the Redim statement.

Dim Arr1() As Double

ReDim Arr1(Selection.Columns.Count, Selection.Rows.Count)

If you use Redim, all the data in your array is lost, unless you use the Preserve keyword. This keeps the data in tact, but limits what you can change with a Redim. For instance, when you use Preserve, you can only change the last dimension of the array. Sometimes you have to organize your array horizontally to accomodate this restriction.

Dim Arr1() As Double
Dim cell As Range
Dim i As Long

For Each cell In Range(”A1:A100″).Cells
    If cell.Value < 0.5 Then
        i = i + 1
        ReDim Preserve Arr1(1 To 2, 1 To i)
        Arr1(1, i) = cell.Value
        Arr1(2, i) = cell.Row
    End If
Next cell

Preserve is an expensive keyword, so you use it sparingly. Many people will Redim their arrays in blocks to avoid having to do it in every iteration of a loop.

ReDim Preserve Arr1(1 To 2, 1 To 10)

For Each cell In Range(”A1:A100″).Cells
    If cell.Value < 0.5 Then
        i = i + 1
        If i Mod 10 = 0 Then
            ReDim Preserve Arr1(1 To 2, 1 To i + 10)
        End If
        Arr1(1, i) = cell.Value
        Arr1(2, i) = cell.Row
    End If
Next cell

I’m not a big fan of the block Redim, but if you have a really time intensive procedure and this shaves some valuable milliseconds, then go for it. If there’s a way to figure out the upper bounds of the array before you add data, then you may save time there also.

Dim SmallCells As Long

For Each cell In Range(”A1:A100″).Cells
    If cell.Value < 0.5 Then
        i = i + 1
        Arr1(1, i) = cell.Value
        Arr1(2, i) = cell.Row
    End If
Next cell

Saturday was my birthday. I’m this many years old:

=FACT(4)+PV(1,30,28)+MONTH(38166)+SIN(RADIANS(90))+ LEN(”dicksblog”)+CHOOSE(3,10,14,22,36,88)+TRUE

It was a lovely birthday. I played golf, ate some barbequed ribs, drank some beer at the College World Series and capped of the day with a little table tennis. I dominated the table until we started playing for money, then I choked.

My wife bought me a Hohner Special 20 Marine Band harmonica and a book for which I cannot find a link. The guys at Millard Music House sold her a harmonica in the key of G and instruction book in the key of C. Nice going.