Wednesday, December 29, 2004

Best Practices for VBA

From Automate Excel this brief article and links for VBA best practice documents.

A couple of good links on VBA best practices:
RVBA Naming Conventions
RVBA Coding Conventions
Eight Bug Reduction Tips
Tricks and techniques are abundant on the web. Documented best practices are not as easy to stumble on. Feel free to add a link if you think it fits.


Posted by: Jethro | 2:36 PM |

Tuesday, December 28, 2004

Excel Easter Eggs

I know its Christmas not easter but here is how to access the hidden 'games' inside Excel.
About.com

Easter Eggs are a way that developers can add in credits or show off with hidden games. Excel Easter Eggs are generally hard to find, but since Excel version 5, there has been a tradition of inserting games and/or credits that can only by access by strange keystrokes and cell combinations.

Excel 2000

Credits
Launch Excel and press F5. Type: x2000:|2000 and press Enter. Hold Ctrl + Shift. Click the Chart Wizard and enjoy the credits.
Game
Open Excel and on the blank worksheet, click File > Save As and choose Save as web page. Click on Publish and Add interactivity and save the file with any name you like. Load the page with Internet Explorer and go to row 2000 Column WC. Highlight all of row 2000 and press Tab to make column WC the active column. Hold down Ctrl + Alt + Shift and click on the Office Icon. Use the arrow keys to drive your car. The "0" key to drop paint slicks. The space bar to shoot. The "H" key for headlights. Note: You need DirectX for this to work.


Posted by: Jethro | 3:55 PM |

Saturday, December 25, 2004

Closed for Christmas

Closed for Christmas!
Normal programming will resume shortly.
Have a safe and Merry Christmas.



Hint - Do NOT leave the 3 yr old unsupervised with the 6 tear old's paints she got for Christmas!


Posted by: Jethro | 5:52 PM |

Thursday, December 23, 2004

Goal Seek in Excel

The Goal Seek Tool in Excel is one of the best easy to use and understand functions there is.

Today I was helping a client calculate a breakeven profit and to do this we changed the sale price of his product using the Goal Seek Tool.

Here's how we did it.

From the menu select Tools - Goal Seek.
Set cell: should be the cell that you are wanting to acheive the result in. In this case it was the cell showing his calculated profit.
To value: is the result you want to achieve; in this case zero.
By changing cell: is the cell that you want the Goal Seek tool to change in order to achieve your desired result. In this example it was the sale price of his product.
Clicking OK sends the tool into a number of iterations (maximum 100) to attempt to resolve the solution. Note: the Set cell and the By changing cell must be related dependantly. That is the Set to cell must change if you change the value in the By changing cell.

In our example the clients sale prices was modifed to the price required to breakeven given his quantity of sales, direct costs and over heads to be recovered.


Remember all archived tips are available on the excel tips website for your use.


Posted by: Jethro | 4:30 PM |

Wednesday, December 22, 2004

Correctly Entering and Deleting Data in Excel

One of my pet hates is poor spreadsheet use. Probably the single biggest cause of errors that I get asked to fix is incorrectly entered or deleted data.

A lot of people are used to word processing applications and tend to treat Excel as one beacuse it appears to mimic one. Nothing could be further from the truth.

Here are the most common causes of errors I find.

Spaces entered after a word or phrase typed into a cell.
A cell "deleted" by pressing the space bar and hitting enter or down arrow. Note this cell is not deleted - it now has a space character in it.
Cells formatted as dates when they should be values.
Cells formatted as text when they are supposed to be numbers or values.
Merged cells. (See separate entry about merged cells.)
Cells "deleted" by using the Edit Clear Contents (or Delete Key).
Hiding rows and columns and then deleting through them and deleting required data.

I will discuss the impact of the spaces errors listed above in two future posts on SUMIF / SUMPRODUCT and VLOOKUP / HLOOKUP.


Posted by: Jethro | 10:44 PM |

Tuesday, December 21, 2004

Close all files in Excel

There is a little known shortcut to a menu item that only appears when you use the shortcut.
Next time you have multiple workbooks open try this.
Hold down the SHIFT key and then click on File. You will see that the menu option Close has gone and in its place Close All appears.
Use this to close down all open workbooks.


Posted by: Jethro | 9:48 AM |

Sunday, December 19, 2004

Saving Autofilter Criteria

If you use some regular filters in a auto filter list, then you may wish to be able to save them and select them easily. To do this use a toolbar icon called Custom Views.

Step 1: Add the Custom Views icon to the toolbar.
1. Place the mouse over one of the toolbars, right click, and select Customize from the shortcut menu.
2. Select the Commands tab.
3. From Categories, select View and drag the Custom Views icon to the Menu bar.

Step 2: Save the filtering criteria
1. From Data select Filter, AutoFilter.
2. Select a cell from the column containing the criteria you want to filter with and from drop-down button filter the list.
3. Type any text criteria you want into the Custom Views box and Hit Enter twice to save.
4. Repeat the previous step and save as many filtering criteria as needed from any sheet within the workbook.

Notes:
Save as many filtering criteria in Custom Views as needed. These are saved in the local workbook only.
Filters from the Custom View are not actually filters but a memory of the saved filter selection. Using it acts on the filter table.
Multiple filters can be performed on different columns and saved.

I found this concept on ExcelTip.com and then added to the basic instructions.


Posted by: Jethro | 11:08 PM |

Friday, December 17, 2004

Speeding up an Excel VBA macro

The quickest easiest way to speed up the execution of a macro is to turn off the screen updating. What this does is stop the computer having to refresh every execution of the macro on the screen thus enabling it to operate faster as it doesn't have to wait for each screen refresh to complete.

This is done by putting these two lines at the beginning and end of the macro respectively.

Application.ScreenUpdating = False

Application.ScreenUpdating = True


Posted by: Jethro | 2:58 AM |

Thursday, December 16, 2004

Using COUNTIF in Excel

COUNTIF is the function to use when you want to count the number of instances a certain text or value appears.
Both of the two examples below only count entire cells. Counting part cells is more complicated and will be discussed separately.

Spreadsheet Example
In a range of data you have something for which you want to count the number of instances. Eg a range of names in cells A1:A50 and you want to see how many times bob's name appears.
the formula is =COUNTIF(A1:A50,"bob") Note that "bob" is case sensitive. and the actual string you are searching for is inside the quotes. To search for a value (eg 15) use =COUNTIF(A1:A50,15). No quotes this time as you are not looking for a text string.
To find multiple strings or values in the same range use this extended formula =COUNTIF(A1:A50,"bob")+COUNTIF(A1:A50,15) to count the number of times bob and 15 appear.

VBA example using a User Defined Function and excludes hidden rows (eg in a filter)

Public Function MyCount(Rng As Range, CountValue)
Dim Sel As Range
MyCount = 0
For Each Sel In Intersect(Rng, Rng.Parent.UsedRange)
If Sel.EntireRow.Hidden = True Then
Else
If Sel.Value = CountValue Then
MyCount = MyCount + 1
End If
End If
Next
End Function

Copy the above text into a module in your workbook (press ALT F11 and then insert a module and paste the text in.)

You can then use the following formula =MyCount(A1:A50,"bob")

If you want to learn how to do multiple criteria counts then look for an article soon on CSE (CTRL SHIFT ENTER) or array formulas or email me with a request.

I am slowly processing the requests for tips I have been sent, so if you haven't seen yours answered yet please be patient. Feel free to send in more requests.


Posted by: Jethro | 10:42 AM |

Wednesday, December 15, 2004

XML and RSS and HAlO2 in Excel

This is pure geeks at play now!

Mark over at AutomateExcel talks about the "fun" you can have with Excel.

"Sean Alexander plays Halo 2 and subscribes to his stats in an RSS feed. He then maps the RSS feed(XML) to an Excel workbook and creates graphs and pivot tables from it."

Wow!
I have to get an xbox and get me a copy of HALO2 just so i can do this! Not!
However I may ask some other geek friends to look into it... Troy?

Back to more "serious" excel stuff tomorrow!


Posted by: Jethro | 1:29 AM |

Tuesday, December 14, 2004

Server outage

Sorry for not posting last couple of days.
Transferring servers for this site followed by a lightning strike taking out my ADSL router on my other server are my excuses. I haven't had connectivity for 2 days (felt like I was blind!)
Back online now!


Posted by: Jethro | 9:53 PM |

Friday, December 10, 2004

Using the Advanced Filter Function in Excel to lcoate unique records

Identifying the unique records or items in a long list can be arduous. However there is actually a very easy way.
I use the unique records option within the Advanced Filter function as follows.

1. Select the list of items you want to find the unique records in. This should be a single column of data and it needs to have a header. The selection can include spaces (blank cells).
2. Copy the selection and paste into a blank workbook (CTRL + N to open a blank workbook)
3. Ensure that the selection includes the header (this can be anything)
4. Select from the menu Data - Filter - Advanced Filter. (Click OK if a message appears.)
5. Ensure "Filter the list, in-place" is selected and then select "Unique records only" and click OK.

The list should now display the unique records only. Note the row numbers have gone blue. This indicates that the duplicate records are just hidden. You can copy and paste the visible selection back to your original worksheet as values (Paste Special)


Posted by: Jethro | 11:56 PM |

Increasing the number of Undo options in Excel

Not having the ability to Undo more than 16 times in Excel can be a huge pain sometimes.

The Good News
There is a way to change this to 100!

The Bad News
It involves editing the Windows Registry.

This Microsoft KB Article will give you the details. The trick only takes a couple of minutes to implement and worked perfectly for me. However, I've reprinted the Warning message for those who haven't edited the registry before:

WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

From AutomateExcel.com


Posted by: Jethro | 2:40 AM |

Thursday, December 09, 2004

Checking a file is not open by another user in Excel VBA

This is the code I use to identify whether or not a file is already opened by another user.
It checks the Random Access Read property of the Open method.

The currentfile variable is the variable you need to replace with a valid file name Eg "C:\exceltestfile.xls".

Sub check_file_available()
'Macro writen by www.jethromanagement.biz 28 April 2003
'check the file isnt already open
Dim checkavailable
Dim currentfile As String
filenumber = FreeFile
On Error GoTo accesserror
Open currentfile For Random Access Read Lock Read Write As #filenumber
GoTo noerror:
accesserror:
checkavailable = True
MsgBox "The " & currentfile & " file is currently being modified by another user. This process will terminate.", vbOKOnly + vbExclamation, "File open error"
noerror:
Close #filenumber
End Sub


Posted by: Jethro | 1:40 AM |

Tuesday, December 07, 2004

Adjusting Print Area In Page Break Preview Mode In Excel

I like it when I learn something new in Excel.
Yesterday I watched a client adjust a print area in a real easy way. He went into Page Break Preview Mode (From the View Menu) and then he simply clicked and dragged the blue border around the print area. In this way he was able to quickly and visually resize the print area. Much quicker than my old way of selecting the desired area and then using File - Print Area - Set Print Area as I used to use.
Thanks Mike!


Posted by: Jethro | 9:29 AM |

Sunday, December 05, 2004

Merging Cells in Excel

Don't do it!

OK now I'll explain why.
First of all you can merge cells if you want to, but I'll explain the consequences below. Basically merged cells should only be used in cosmetic non database spreadsheets.

Microsoft have changed the formatting option relating to centering text across cells a number of times in its evolution of Excel. Not all of these changes are backward compatabile either.
Before the Merge Cells feature was introduced (in Office 2000 I think) Excel used to have a center across cells button in its tool bar which replicated the menu function Edit Cells Format Alignment, Horizontal, Center across Selection.
This feature simply centered the text in a cell across all the cells that had been selected before choosing the option.
When they upgraded to Office 2000 they used the same button on the toolbar (a little a) and replaced its function with the new Merge Cells function. People who were used to centering text across cells kept using the button, only now a more insidious change was being made to the spreadsheet.
Merge Cells A1 and B1 and B1 will no longer exist. Merge C3 to C8 and cells C4 through C8 no longer exist. This makes it very awkward to work with rows and columns (records and fields) of data. I have repaired countless spreadsheet applications that were wrecked by this inadvertent use of the "old" toolbar button with a new function.
Office 2003 works with it a little smarter than 2000 and 2002 (XP) in that you can select columns that contain merged cells without the selection expanding to the entire range of the merged cells. However the database structure and integrity is gone.

In a nutshell, if you want to manipulate data in a database type format, and you must center across columns than use the Menu and select this option in the Format Cells Alignment tab.
If you must use the merge cells function then don't expect your file to be user friendly for formulas like SUMPRODUCT, SUMIF, AVERAGE, COUNT, VLOOKUP etc. in fact any formula that you might want to use that relies on a column or row of data.
Use it only on cosmetic pages, help, instruction and menu sheets etc.


Posted by: Jethro | 1:19 AM |

Thursday, December 02, 2004

Copy relative formulas without changing them in Excel

I just found the niftiest trick.
Copy a formula with relative row links down will always change the rows relative to the original formula.
If you don't want this to happen then simply select the cell below the one you want to copy to and press CTRL '. (Apostrophe)
The relative cell referene will not change!


Posted by: Jethro | 7:22 PM |

Wednesday, December 01, 2004

Broken Links in Excel

If you have ever opened a workbook that has broken links then you will know how frustrating it can be to clear them.

There are a number of ways you can do this.
If the missing workbook has had its name or directory structure changes then you can simply update the links by going to Edit Links and then for each work book listed. This will only work if it is exactly the same as the previous linked workbook.

If the links are to a file you no longer want to link to then you can remove them.
Using the Edit Links dialog box you can find the names of the files. These can be found using the Find function, and then can be deleted.

Sometimes these methods still don't work and you still have links left.
Two tips.
First of all make sure there are no hidden sheets. There may be links on hidden sheets.
Second look for named ranges that refer to another file. USe CTRL F3 to open the named ranges list and check for names that have another file name in the reference. Delete (or re-reference) these.


Posted by: Jethro | 6:59 PM |