Super Excel Special!  Only $7.50, Click here

OzGrid's Free Excel Newsletter

MicrosoftExcel and VBA for Excel Training Information (auto response)

The newsletter isdivided into four sections:

1.   What's new at OzGrid.com

2.   Microsoft Excel tips

3.   Microsoft Excel VBA tips

4.   Helpful information

It ispublished on about the 10^th of each month and always written bymyself Dave Hawley.

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

Should you wish to no longer receive ournewsletter, send an email with the words "Leave Newsletter" as thesubject field, or clickhere.

Contained at the bottom of eachnewsletter is Helpful Information on how to get the most from ournewsletters. If you think there is something missing, please let meknow.

Microsoft and Microsoft Excel are registered trademarks of MicrosoftCorporation

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

Excel DownloadTraining Specials.

What's new at OzGrid.com

Hiall

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

Thismonth 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 ableto use these.

Training

Due to the popularity of our level1 Excel VBA training we are now able to offer this course on-line toindividuals at a significant discount, click herefor details or send a blank email with the word TrainingInfo as thesubject line to [email protected]

RealEstate Investors

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

CreditCard Payments

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

OzGridPlus Add-in

As soon as wehave our credit card payment system in place we will be offering our ExcelAdd-in for sale. The normal cost of this will be $18.95 or $28.95 forfull access to code, but ALLnewsletter subscribers will automatically receive a 20% discount. The Add-initself will be called OzGrid Plus and willhave 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

 Forthis weeks Excel tips I thought I would show you a couple of Excels least knownfeatures. 

FillHandle

The Fill Handle in Excel ispossibly one of Excels most under utilized features. For those of you that don'tknow 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, ifyou type the number 1 in any cell and then the number 2 in a cell that adjoinsit, you can use the Fill Handle to increment up to any number desired. To dothis 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 asmall black cross), left click and drag in the direction you want theincremented numbers to show. The best bit about this feature is that we canalter the amount we increment by simply typing any two numbers we want eg 5 and10 would result in 5, 10, 15, 20, 25 etc. If you wanted a blank cell betweeneach 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 todates, after all dates in Excel are only numbers (Serial Values). There is anextract from the Excel help on dates and times at the end of this section thatall Excel users should familiarize themselves with.

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

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

The other little known feature is Excels pop-up Fill menu you get whenyou 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 eliminatethe need for the secondary number. Now repeat steps 1 to 4 and for step 5 select Seriesinstead 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 allowsmany choices and is very useful for incrementing dates! The best way byfar to familiarize yourself with this feature is to jump straight in and have ago.

You may also have noticed when we right clicked the Fill Handle and dragged wehad 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. Butyou can also Copy cells, Fill Values and Fill formats. Thiscan be particularly useful, but there is another Pop-up menu that is better forthis.

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 10options to choose from. Some of these are short-cut methods of the Paste Specialfeature. If you hold down the Alt key while dragging you can changesheets by hovering over the sheet name tab! I find the Copy here as values only particularly useful whenconverting 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 andeasier than using Edit>Paste Special-Values!

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

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

HowMicrosoft Excel stores dates and times - FROMEXCELS HELP

Microsoft Excel stores dates as sequential numbers known as serialvalues. Excel stores times as decimal fractions because time is considered aportion of a day. Dates and times are values and, therefore, can be added,subtracted, and included in other calculations. For example, to determine thedifference between two dates, you can subtract one date from the other. You canview a date as a serial value and a time as a decimal fraction by changing theformat 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 tothe 1904 date system, click Options on the Tools menu, click the Calculationtab, and then select the 1904 date system check box.

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

Notes

Two-digit years   When you enter a date in a celland you enter only two digits for the year, Excel interprets the year asfollows:

• 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, changethe way two-digit years are interpreted.

Four-digit years   To ensure that year values areinterpreted as you intended, type year values as four digits (2001, rather than01). By entering four digits for the years, Excel won't interpret the centuryfor you.

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

The TIME worksheet function   If you need tomanipulate a part of a time — such as the hour or minute — withina 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-digityears. You can use Windows Control Panel to changethe default date format to display four-digit years instead of two-digit years.

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

Microsoft Excel VBA tips

ListBox 

For this months Excel VBA section Ithought we would look at how to use a ListBox on a UserForm. The ListBox controlis ideal to use if you wish to allow your users to make one or more selectionsfrom a list. They are also very handy if your list will contains more than onecolumn of data.

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

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

Now create a dynamic range of thistable, 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 asmany 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 andhave the change reflected in our ListBox. If you are not familiar with Dynamicranges, 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 Propertieswindow is showing by right clicking on the ListBox and selecting "Properties"

ColumnCount

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

ColumnHeads

The second Property we will changeis the ColumnHeads Property, by default this will be False. Bysetting this to True our ListBox will have headings. this will becomeapparent soon.

ColumnWidths

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

MultiSelect

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

RowSource

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

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

Some Code

What we are going to do is codethis UserForm so that whatever the user selects in the ListBox will be transferredto another table on another Worksheet (sheet2). Double click the CommandButtonand 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

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

Allyou need to do now is run the UserForm (select it and push F5) and select one or more entriesthen click theCommandButton.

Don'tforget you candownload the complete Workbook here: http://www.ozgrid.com/download/default.htmunder ListBoxTransfer.zip

Until next month, keep Excelling!

Kind regards

Dave Hawley

[email protected]

HelpfulInformation