Super Excel Special!  Only $7.50, Click here

OzGrid's Free Excel Newsletter

Microsoft Excel and VBA for Excel Training Information (auto response)

The newsletter is divided into four sections:

1.    What's new at OzGrid.com

2.    Microsoft Excel tips

3.    Microsoft Excel VBA tips

4.    Helpful information

It is published on about the 10^th of each month and always written by myself Dave Hawley.

You are more than welcome to pass on this newsletter to as many people as you wish, all I ask is you pass it on in it's entirety. 

Should you wish to no longer receive our newsletter, send an email with the words "Leave Newsletter" as the subject field, or click here.

Contained at the bottom of each newsletter is Helpful Information on how to get the most from our newsletters. If you think there is something missing, please let me know.

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation

Excel Add-ins for Excel. Business, Financial & Accounting Software. Over 150 here!

Excel Download Training Specials.

What's new at OzGrid.com

Hi all

I trust all of those that celebrate Christmas and New Year have had a good one! I know I did, now it's time to make all those 'new year resolutions' that are usually broken before February.

This month I have posted some new tips on our Web site pertaining to:

• VBA code optimization
• Testing whether a range is hidden by Auto Filters
• A UDF to extract the last word from a String
• Collect an array of numbers and enter them
• Using Validation a Vlookup to make a table user friendly- Lookup.zip
• Some code to transfer Values and match them to another list - ListMatch.zip

Hopefully many of you will be able to use these.

Training

Due to the popularity of our level 1 Excel VBA training we are now able to offer this course on-line to individuals at a significant discount, click here for details or send a blank email with the word TrainingInfo as the subject line to training@ozgrid.com

Real Estate Investors

Just before Christmas I started doing some work for a New York area based software company that develops software for investors, developers, brokers, lenders and appraisers. They have a very neat Excel Workbook that they sell called Real Estate Investment Analysis, Version 11.0 if you are into Real Estate in any way why not take a look. http://www.realdata.com. This is not a paid advertisement in any way shape or form.

Credit Card Payments

As many of your may be aware we are currently in the process of allowing credit card payments via our Web site. This has proven to be a long and winding road for us as the banks here are very hard in negotiating deals with. The temptation is there to simply accept what they offer as a standard, but this would inevitably mean passing on the bank charges and fees to our clients. We now believe we are very close to having this facility made available to all our customers and we should be able to give a firm date in next months newsletter.

OzGrid Plus Add-in

As soon as we have our credit card payment system in place we will be offering our Excel Add-in for sale. The normal cost of this will be $18.95 or $28.95 for full access to code, but ALL newsletter subscribers will automatically receive a 20% discount. The Add-in itself will be called OzGrid Plus and will have the following features.

1. Formula Report Generator. Allows you to very easily create a report showing formulas within your Workbook that are either external references, internal references or both. You can also nominate which Worksheet(s) to use to base your report on.
2. Dynamic Range Namer. This will allow you to very quickly and easily create any one of eight different types of dynamic ranges that will expand down rows or across columns. You can even nominate the column or row to use as the criteria.
3. Real Calendar. This one will create a live calendar in an instant using real dates, so you can reference it if needed.
4. Sheet Creator and Sorter. This option will create x number of pre-named worksheets that can be Financial Years (1901-2050), Weekdays (Monday-Sunday), Months (January-December), Years (1901-2050), Quarters (Quart1-Quart4), Days (Day1-Day1460) and Weeks (Week1-Week208). It also has an option that allows you to Sort (ascending or descending) any Worksheets in any Workbook.
5. A Random Number Generator. This can produce up to 1000 non repeating random numbers that meet a criteria you specify.
6. Conditional Row Deletion. This one will save many hours of repetitive work when you need to quickly and easily delete rows in a Worksheet that meet any criteria. There is an option that allows you to easily nominate any one of Excels six Error Types, or all of them. There is also a simple option that allows you to nominate the criteria.
7. Duplication Report. Will produce a statistical table of your data. Including a Count of occurrences of each item, the total number of duplicates and a total of all entries. It will also produce a list of your data containing only unique entries. Much like a Pivot Table in a single click.
8. Do On Time. This feature allows you to run a macro (recorded or written) at a set time, display a personal message or reminder at a specified time or even open a nominated Workbook at a set time.

Microsoft Excel tips

 For this weeks Excel tips I thought I would show you a couple of Excels least known features. 

Fill Handle

The Fill Handle in Excel is possibly one of Excels most under utilized features. For those of you that don't know of it, it's the small black square in the bottom right of the active cell. In it's simplest form it will increment any series of numbers. For example, if you type the number 1 in any cell and then the number 2 in a cell that adjoins it, you can use the Fill Handle to increment up to any number desired. To do this you simply select you two cells (starting from the one with the number 1) and then hover your mouse pointer over the Fill Handle (until it changes to a small black cross), left click and drag in the direction you want the incremented numbers to show. The best bit about this feature is that we can alter the amount we increment by simply typing any two numbers we want eg 5 and 10 would result in 5, 10, 15, 20, 25 etc. If you wanted a blank cell between each number simply use the method below:

1. Type 5 in cell A1
2. Leave A2 blank
3. Type 10 in cell A3
4. Leave A4 blank
5. Select cells A1:A4
6. Drag down using the Fill Handle

The exact same principle applies to dates, after all dates in Excel are only numbers (Serial Values). There is an extract from the Excel help on dates and times at the end of this section that all Excel users should familiarize themselves with.

The other thing that you can do with the Fill Handle is drag it up or to the left to clear the contents of selected cell(s). You can even insert or delete rows or columns by holding down the SHIFT key while dragging the fill handle.

Lets assume you have a column of data in cells A1:A500 and you place a formula into cell B1. Normally you would copy and paste the formula down to row 500, but instead of this try double clicking the Fill Handle. The whole thing is done for you and will stop at the first blank cell in column A! The same thing happens if you put two different numbers in cells B1 and B2, select both cells then double click the Fill Handle.

The other little known feature is Excels pop-up Fill menu you get when you right click on the Fill Handle and drag. Try this.

1. Type any number in any cell
2. Select the cell
3. Right click on the Fill Handle
4. Drag down and then release
5. Select Fill Series

By using this method you eliminate the need for the secondary number. Now repeat steps 1 to 4 and for step 5 select Series instead of Fill Series. The option here are:

1. Series in: Determines whether the series is filled across selected rows or down selected columns. The contents of the first cell or cells in each row or column of the selection are used as the starting values for the series.
2. Type (Linear or Growth): Creates a growth series or geometric growth trend.
   If the Trend box is cleared, a series is calculated by multiplying the value in the Step value box by each cell value in turn. If the Trend box is selected, the value in the Step value box is ignored, and a geometric growth trend is calculated based on the selected values. The selected original values are replaced with values that fit the trend.
3. Type (Date): Fills a series with dates. The type of date series that is incremented depends on the option selected under Date unit. Date unit is only available when working with dates.
4. Type (AutoFill): Fills blank cells in a selection with a series based on data included in the selection. Selecting this option produces the same results as dragging the fill handle to fill a series. Any value in the Step value box and any selected Date unit option are ignored.
5. Date unit: Specifies whether a series of dates will increase by days, weekdays, months, or years. Available only when creating a date series.
6. Trend: Calculates a best-fit line (for linear series) or geometric curve (for growth series). The step values for the trend are calculated from the existing values at the top or left of the selection. Any value in the Step value box is ignored if the Trend check box is selected.
7. Set value: Enter a positive or negative number to indicate the amount by which you want a series to increase or decrease.
8. Stop value: Enter a positive or negative number to indicate the value at which you want the series to end. If the selection is filled before the series reaches the stop value, the series stops at that point. If the selection is larger than needed to fill the series, the remaining cells of the selection are left blank. You do not need a value in the Stop value box to fill a series.
   

As you can see this option allows many choices and is very useful for incrementing dates! The best way by far to familiarize yourself with this feature is to jump straight in and have a go.

You may also have noticed when we right clicked the Fill Handle and dragged we had many other options available on the Pop-up menu. The Fill day, Fill months etc will only be available if the cell(s) contain a date. But you can also Copy cells, Fill Values and Fill formats. This can be particularly useful, but there is another Pop-up menu that is better for this.

1. Type any formula into any cell
2. Right click on the cell border (not the Fill Handle)
3. Drag down then release.

As you will see you now have 10 options to choose from. Some of these are short-cut methods of the Paste Special feature. If you hold down the Alt key while dragging you can change sheets by hovering over the sheet name tab! I find the Copy here as values only particularly useful when converting formulas to permanent values. Try this example:

1. Type any number of formulas into any adjoining cells
2. Select all these cells and right click on the border.
3. Now drag down just one row
4. With the right mouse button still held down drag back up one row to where you started
5. Now release and choose Copy here as values only

This method is far quicker and easier than using Edit>Paste Special-Values!

To find out what the other options do, just jump right in and try them.

The information below is from the Excel help and goes some of the way to explaining how Excel stores dates and times.

How Microsoft Excel stores dates and times - FROM EXCELS HELP

Microsoft Excel stores dates as sequential numbers known as serial values. Excel stores times as decimal fractions because time is considered a portion of a day. Dates and times are values and, therefore, can be added, subtracted, and included in other calculations. For example, to determine the difference between two dates, you can subtract one date from the other. You can view a date as a serial value and a time as a decimal fraction by changing the format of the cell that contains the date or time to General format.

Excel supports two date systems: the 1900 and 1904 date systems. The default date system for Microsoft Excel for Windows is 1900. To change to the 1904 date system, click Options on the Tools menu, click the Calculation tab, and then select the 1904 date system check box.

The following table shows the first date and the last date for each date system and the serial value associated with each date.

Notes

Two-digit years   When you enter a date in a cell and you enter only two digits for the year, Excel interprets the year as follows:

• The years 2000 through 2029 if you type 00 through 29 for the year. For example, if you type 5/28/19, Excel assumes the date is May 28, 2019.
  
  
• The years 1930 through 1999 if you type 30 through 99 for the year. For example, if you type 5/28/98, Excel assumes the date is May 28, 1998.

If you are using Microsoft Windows 98 or Microsoft Windows 2000, you can, without the assistance of your system administrator, change the way two-digit years are interpreted.

Four-digit years   To ensure that year values are interpreted as you intended, type year values as four digits (2001, rather than 01). By entering four digits for the years, Excel won't interpret the century for you.

The DATE worksheet function   If you need to manipulate a part of a date — such as the year or month — within a formula, you can use the DATE worksheet function.

The TIME worksheet function   If you need to manipulate a part of a time — such as the hour or minute — within a formula, you can use the TIME worksheet function.

Display four-digit years by default   By default, as you enter dates in a workbook, the dates are formatted to display two-digit years. You can use Windows Control Panel to change the default date format to display four-digit years instead of two-digit years.

The Year 2000   The Microsoft Year 2000 Resource Center provides information about how Microsoft products are affected by the arrival of the year 2000. Learn about the Microsoft Year 2000 Resource Center.

Microsoft Excel VBA tips

ListBox 

For this months Excel VBA section I thought we would look at how to use a ListBox on a UserForm. The ListBox control is ideal to use if you wish to allow your users to make one or more selections from a list. They are also very handy if your list will contains more than one column of data.

You will get the most out of this example if you follow along! For those of you that don't have the time, you can download the complete Workbook here: http://www.ozgrid.com/download/default.htm under ListBoxTransfer.zip

Open a new Workbook and set up a table as shown below, with Name being in cell A1 of Sheet1:

Now create a dynamic range of this table, to do this follow the steps below:

1. Go to Insert>Name>Define
2. In the Names in workbook: type  MyRange
3. In the Refers to: box type (or copy in) =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$100),COUNTA(Sheet1!$A$1:$J$1))
4. Click Add then OK.

This range will now expand down as many rows as entries in A1:A100 and across as many Columns as A1:J1. We do this so that we can easily add more columns and/or rows to our table and have the change reflected in our ListBox. If you are not familiar with Dynamic ranges, you can read about them here: http://www.ozgrid.com/Excel/DynamicRanges.htm

Now open the VBE (Alt+F11) and go to Insert>Module then Insert>UserForm. Then click your UserForm to display the control Toolbox, or go to View>Toolbox, now click the ListBox control and place it on your UserForm. Ensure the Properties window is showing by right clicking on the ListBox and selecting "Properties"

ColumnCount

The first Property we will change is the ColumnCount, by default this will be 1. Change this to -1. Setting ColumnCount to 0 displays zero columns, and setting it to -1 displays all the available columns. For an unbound data source, there is a 10-column limit (0 to 9).

ColumnHeads

The second Property we will change is the ColumnHeads Property, by default this will be False. By setting this to True our ListBox will have headings. this will become apparent soon.

ColumnWidths

By default these will be blank, which is fine for this example. However, I do suggest reading up on this Property in the VBE help.

MultiSelect

We will be setting this up so that a user can select more than one entry from our list, so change this Property to fmMultiSelectMulti

RowSource

This is where we enter the name of our dynamic named range. So type in MyRange.

Now all we need to do is size our ListBox so that all columns are visible. Changing the Width Property to 295 should do it. This will probably mean making our UserForm wider also. While we are at it, we should also add a CommandButton to our UserForm!

Some Code

What we are going to do is code this UserForm so that whatever the user selects in the ListBox will be transferred to another table on another Worksheet (sheet2). Double click the CommandButton and insert this code:

Private Sub CommandButton1_Click()
Dim iListCount As Integer, iColCount As Integer
Dim iRow As Integer
Dim rStartCell As Range

'Set a range variable to the first cell to recieve our data
'Using "End(xlUp).Offset(1, 0)" _
 will give us the cell below the last entry
 Set rStartCell = Sheet2.Range("A65536").End(xlUp).Offset(1, 0)

    'Loop as many times (less one) as there are entries in our list.
    'We must start from zero to use this in the Selected Property.
     For iListCount = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(iListCount) = True Then 'User has selected
           ListBox1.Selected(iListCount) = False 'Deselect it
           iRow = iRow + 1
           'Now loop as many times as there are columns in MyRange
            For iColCount = 0 To Range("MyRange").Columns.Count - 1
                'place the selected data into the table, starting from _
                 range Ax and moving across as many columns as there are _
                 in the range MyRange.
                    rStartCell.Cells(iRow, iColCount + 1).Value = _
                            ListBox1.List(iListCount, iColCount)
            Next iColCount
        End If
    Next iListCount

Set rStartCell = Nothing

End Sub

There is more comments than code to help those that are not familiar with the ListBox.

All you need to do now is run the UserForm (select it and push F5) and select one or more entries then click the CommandButton.

Don't forget you can download the complete Workbook here: http://www.ozgrid.com/download/default.htm under ListBoxTransfer.zip

Until next month, keep Excelling!

Kind regards

Dave Hawley

DavidH@OzGrid.com

Helpful Information