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()

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.

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

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

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

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)

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

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.

If you find that Excel is dividing your input by 100 for no apparent reason, it’s probably because you have checked “Fixed Decimal Places” under Tools>Options>Edit.

Uncheck that box to return to normal behavior. Of course it doesn’t have to be 100, it’s just that Excel defaults to 2 decimal places when that box is checked.

The Paste Special command under the Edit menu has a few operations that allow you to update a range of cell values all at once. They are Add, Subtract, Multiply, and Divide and they are in the Operation section of the Paste Special dialog box.

Assume you have a list of merchandise and corresponding prices. If you need to update all your prices to reflect a 5% increase, you can do so without using a helper column and a formula. To do this, put 1.05 in an unused cell (we’ll delete it later) and copy it.

Then select the range you want to update and choose Paste Special.

Choose the Multiply radio button

And the range will be increased by 5%

You can then delete the cell with 1.05 in it.

There are two very simple ways to make your code run faster, and they are both properties of the Application object: ScreenUpdating and Calculation.

ScreenUpdating, set to False, causes your macro to run without refreshing the screen. This property can be useful if you don’t want the user to see the screen flicker as you write to cells or do other things. Even if you don’t care about flicker, refreshing the screen takes time and turning ScreenUpdating off allows Excel to devote its time to running you macro.

Calculation determines when Excel calculates as defined in Tools>Options>Calculation. If your code writes to cells, setting Calculation to xlCalculationManual can greatly reduce the execution time of your code. There are times when you need to calculate in your macro, such as if your macro uses Excel to get an intermediate calculation, but usually you can leave it set to manual until your done.

Don’t forget to set ScreenUpdating to True and Calculation back to xlCalculationAutomatic at the end of your code.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual