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 10th 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
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:
Hopefully many of you will be ableto use these.
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]
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.
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.
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.
Microsoft Excel tips
Forthis weeks Excel tips I thought I would show you a couple of Excels least knownfeatures.
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:
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.
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:
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.
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:
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.
|1900||January 1, 1900 |
(serial value 1)
|December 31, 9999 |
(serial value 2958465)
|1904||January 2, 1904 |
(serial value 1)
|December 31, 9999 |
(serial value 2957003)
Two-digit years When you enter a date in a celland you enter only two digits for the year, Excel interprets the year asfollows:
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
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:
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"
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).
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.
By default these will be blank,which is fine for this example. However, I do suggest reading up on thisProperty in the VBE help.
We will be setting this up so thata user can select more than one entry from our list, so change this Property to fmMultiSelectMulti
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!
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 = _
Set rStartCell = Nothing
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!