Spy Journal Excel Tips

Monday, August 29, 2005

Protecting Cells in Excel

Microsoft Excel has the ability to "protect" cells from accidental entry, editing or erasure. By default all cells in an Excel spreadsheet are locked. This option can be found by selecting a cell then clicking Format | Cells | Protection.
This has absolutely no affect unless the sheet is protected. You can protect a sheet by clicking Tools | Protection | Protect Sheet and then completing the dialog boxes to add a password to the sheet. Unlocking is done in reverse. Once a sheet is protected cells that were locked are no longer able to be edited or deleted. Cells that had the locked option removed can be.

Excel XP (2002) and Excel 2003 added additional features to the locking process. Some of these options include the ability to restrict users to specific ranges or types of changes to a cell, eg format changes but not formula changes. While these features are quite powerful I don't tend to use them either in spreadsheets or VBA code. The reason is that these are NOT completely backward compatabile. Syntax in VBA that relates to the new features will cause Debug errors when run in older versions of Excel. Some protection methods that reserve some functions do not perform as required under older versions.
When writing spreadsheet solutions for corporate environments where mutliple versions of Excel may exist, including workers home PC's remember not to use these options or alternatively error trap all possible issues with versions in your code.

There were numerous changes between Excel 2000 and Excel 2002 and then 2003 that weere not documented in the whitepapers produced by Microsoft. I have spent many hours on the phone to Microsoft Engineers trying to resolve issues relating to changes that were not documented either in help files or whitepapers. The good news was that these calls were free; the bad news is that the issues have not been resolved. I will discuss some more technical issues later.

Saturday, August 27, 2005

Sorting a list in Excel

There are several ways to sort a list in Excel.

On the tool bar there are some short cut buttons A to Z and Z to A.
These are context sensitive. That is they will sort the column of the selected cell(s) aplha numerically ascending and descending respectively. They will also by default sort the surrounding (connected) columns. If you have formulas in these cells that are relative to the row they are in this is not good.
Unless you are sorting simple lists this is probably not the best way to sort.

Clicking Data Sort on the Menu is also context sensitve. Excel attempts to guess what your sorting requirements are. If there are unique headers it will generally determine this is a header row and identify these values as the columns headers. Else it will provide columns labels; Column A, Column B etc.
This sort option gives you a number of additonal options.

You can choose to sort by columns or rows by clicking Options and choosing the Orientation you need.

You can select up to 3 sort options and choose ascending or descending for each.

You can turn on or off the header row(column) option.

It is always a good idea to follow some simple rules when using or planning to use the Sort function.

Ensure that there is a header row for the data to be sorted.

Ensure that there is a clear space around the area to be sorted, at least one row at top and bottom and at least one column left and right. Alternatively select the area to be sorted first.

Don't use cell relative formulas in adjacent columns as these will be rearranged and return incorrect results after sorting changes orders.

Happy sorting!

Tuesday, August 23, 2005

Dividing a number equally into rounded numbers

From Exceltip.com

Problem:
The price in cell B1 must be divided accurately into 5 similar payments, with up to two decimal places.
When simply dividing the price 48.04 by 5 and rounding the result to two decimal places, we get 9.61.
However, when multiplying the result back by 5, we get 48.05, which is not the exact number we originally divided.
How could we find 5 similar numbers that sum up to the accurate price?

Solution:
In order to find our 5 similar payments, we would have to calculate each payment by equally dividing the current price (original price(-)payments already calculated) by the number of remaining payments (those that were not yet calculated).
First, we must calculate the first payment, by dividing the price by 5 and rounding the result:
=ROUND(B1/5,2)
After finding the first payment, we can calculate all the remaining payments by using the following formula:
=ROUND(($B$1-SUM($B$2:B2))/(7-ROW()),2)

Total Price $48.04

Payment 1 $9.61
Payment 2 $9.61
Payment 3 $9.61
Payment 4 $9.61
Payment 5 $9.60

Thursday, August 18, 2005

Auto Fill Macro

doakra wrote

I would like to write a macro that would look at a value in a given cell, then begin at another given cell and autofill the cells going down the column with sequential numbers, beginnig at 1 and ending at the number found in the first cell. Is this possible? Thanks!

I replied:

Use Insert | Name | Define and Add to name the ranges the source cell should be called source - if you have more than 1 then source1, source2 etc.
Then name the target cell target - the starting cell you want 1 to go in.

place this code in a module and run it.

Sub autofiller()
'macro written by Jethro Aug 1005
Dim sourcedata As Variant
Dim x As Single
'get source data
Application.Goto reference:="source"
sourcedata = ActiveCell.Value
'go to target cell
Application.Goto reference:="target"
'enter data
For x = 1 To sourcedata
'make sure cell is not at end of sheet
If ActiveCell.Row = 65535 Then Exit Sub
ActiveCell.Value = x
ActiveCell.Offset(1, 0).Activate
Next x
End Sub

Tuesday, August 16, 2005

Date and Time Formats

Wolfgang asked

hi gents (and ladies)...

the next-door comedian (co-worker) just asked me if he could add hours to the following fancy format and no formulas please:

a1 = mon 08:00
b1 = 8.5
c1 = mon 16:30

he has a table with some 90 rows which he need to sum up and of course, all possible day combinations included, mon to sun...

i told him that nobody has time enough to code this...am i right?!

best,
wolfgang

I replied
Hi wolfgang

I'm not 100% sure what you want but I assume that your coworker wants to be able to allow users to enter the starting time and hours worked and calculate a closing time. This needs to be displayed in the format as you wrote it.

The easiest way to do this is to format the cells themselves to handle dates and hours. Go to Format Cells Number and select Custom to do this.
The first and last cells (A1 and C1) need to have a custom format of ddd hh:mm. Cell B1 needs a format of h:mm.
This will give you Mon 08:00 when you enter that date and time in - this is easily editable in the formula bar or can be entered as 15/7/05 09:00 for 9am on Friday 15th August.
The second cell B1 needs to have the date entered by typing 08:30 as in the hours and minutes required separated by a colon and needs the leading zero
The third cell C1 can then just have a simple formula =A1+B1 and it will calculate the ending time.

To force the users to enter the data in the correct format you can use Data Validation to require the format and limit the times that can be entered.
Select B1 and click Data Validation on the menu
Select Time in the Allow box
Choose between 0:00 and 23:59 or any other times required
Yoiu can use the Input Message and Error Alert tabs to modify what the user sees if they enter incorrectly.
Repeat for A1 and if necessary lock the spreadsheet only allowing cells A1 and B1 to be entered into. Do this by formatting the cells and going to the Protection Tab and turning off Locked. Then go to Tools Protection Protect Sheet to protect the sheet. Use this sparingly and only when you can't trust the users not to muck it up by typing over formulas.

You can prepare a whole sheet by selecting the whole column before editing the format
Hope this answers the question.

Monday, August 15, 2005

Deleting empty rows

Here's the answer I wrote to a question posted on the JMT forums.

Question

I have a lot of entries in a spread sheet.
each one is separated by an empty row (sometimes 2 rows are empty)
I was wanting to activate cell a4 and then run the macro to see
if that cell is empty. Runs until it hits a date ("08/10/06")
I've seen it before on an old excel help screen,
but cant remember it exctly. Thanks for your help.

Answer

1 Create this function to find the last row on the sheet by copying and pasting into a module in the Projefct Explorer (shortcut ALT F11)

Function LastCell(ws As Worksheet) As Range
Dim LastRow&, LastCol%
' Error-handling is here in case there is not any data in the worksheet
On Error Resume Next
With ws
' Find the last real row
LastRow& = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
' Find the last real column
LastCol% = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
End With
' Finally, initialize a Range object variable for the last populated row.
Set LastCell = ws.Cells(LastRow&, LastCol%)
End Function

2 Then create this procedure by copying and pasting into the module below the function.

Sub delrows()
lastrowno = LastCell(ActiveSheet).Row
Range("A1").Select
Do While ActiveCell.Row < lastrowno
If ActiveCell = "" Then
ActiveCell.EntireRow.Delete
lastrowno = lastrowno - 1
Else
If DateValue(ActiveCell) > DateValue("11/8/2005") Then Exit Sub 'enter date here
ActiveCell.Offset(1, 0).Activate
End If
Loop
End Sub

Friday, August 12, 2005

COUNTIF Criteria

Microsoft Excel Help has these syntax options for the COUNTIF function

=COUNTIF(A2:A5,"apples") Number of cells with apples in the first column above
=COUNTIF(B2:B5,">55") Number of cells with a value greater than 55 in the second column above

But what if you want to count the number items below or above to a certain value, but that value needed to be flexible and not pasted in the formula as in these two instances? The answer is that the formula can return the count using a criteria found in a cell if entered like this:

=COUNTIF(A1:A12,">"&B10) where B10 is the cell containing the criteria.
Note the quotes around the greater than sign.

Thanks Rembo for this one

Thursday, August 11, 2005

JMT Forums

An excellent forum location (almost as good as mine!) that has a good selection of ask and answer questions on it.
Feel free to check the questions out and answer them if you can!
Heres an answer I added to the question about checking if files are open in VBA.

Sub checkfileisopen()
filenumber = FreeFile
On Error GoTo accesserror
Open "Q:\filename.xls" For Random Access Read Lock Read Write As #filenumber
GoTo noerror:
accesserror:
MsgBox "The " & selectedfile & " file is currently being modified by another user. This process will terminate.", vbOKOnly + vbExclamation, "File open error"
noerror:
Close #filenumber
End Sub

This code will open a read write lock to the file in question Q:\filename.xls
if the file is already open it will return an error and the error handler will skip it with a message. I use this inside a loop checking all the files in a folder before operating on them

Wednesday, August 10, 2005

User Defined Function (UDF) Request

I found this request in a forum at DigDB.com

Dear Sir,

Could you please help me to find a solution for the following problem. I have been trying to find a solution for a long time and finaly I saw this website, I feel I am lucky.

I want convert or translate a mathematical figure in an excel shorksheet to english words. for example:

My bill total comes in cell A1, I want that total to be appear in words in next cell.

If Cell A1 = 100.55 ( This is my bill total )
Cell A2 = One Hundred & Five Five Only. ( This is my bill total in words )

I hope you have understand me and look forward to hearing from you soon.

Thank you

Have a go at solving it and post your responses either in the comments or by email to me.

Monday, August 08, 2005

Sheet Tabs Shortcuts

Sheets in Excel Workbooks are accessed with the mouse by clicking on the sheet tab at the bottom of the screen.
There are a number of short cuts that can be used instead.
CTRL PAGEUP and CTRL PAGEDOWN will cycle through the sheets with PAGEUP taking you left and PAGEDOWN right.
When you have more sheet tabs than can be displayed on the screen you can increase the amount of space by dragging the scroll bar slider right. However once you have run out of room here there are some buttons at the very left of the sheet tabs that are very useful.

These are similar to a CD player controls with the outer arrows taking you to the front and end of the sheet tabs. The center arrows selects the next sheet up and down from the current sheet.
Additionally right clicking on these arrows displays a list of all sheet tabs that can be accessed. If there are more sheet tabs than this can handle it displays a More Sheets option.

Thursday, August 04, 2005

Display duplicate values in a range

Often when working with a list of data one needs to know whether or not there are any duplicate values in the list.
Heres a conditional formatting way of doing it
I am assuming that there is a list of data in column A and that we want to check for duplicate values in it.

Steps

Select any cell in the range and then click Format | Conditional Fomatting.

In Condition 1 select Formula Is and type =COUNTIF($A:$A,A5)>1 where A5 is the selected cell. Note the use of relative and absolute references. Column A is selected absolutely ($A:$A) while the selected cell is selected relative to itself with no $ signs.

Now choose a format option for the duplicate cells by setting the Font, Border or Patterns as required.

Click OK.

Now copy the cell and then select the whole column and Paste Special As Formats.

All the duplicate cells should now display the chosen formatting.

Useful Excel Links

VBA and VB Programming Links:
http://www.vbexplorer.com Visual Basic Explorer provides downloads, tutorials, and code samples for the Visual Basic programmer.
http://www.bmsltd.co.uk/Excel/ Stephen Bullen's Excel page inlcudes some practical and interesting examples of Excel worksheets and VBA programs.
http://www.cpearson.com/excel.htm More Excel tips and sample programs from Pearson Software Consulting.
http://j-walk.com/ss/excel/index.htm Tips, downloads, newsgroups and other links from a very useful site for Excel users provided by John Walkenbach.
http://www.officevba.com/ Information and downloads for MS Office products. A subscription is required.
http://www.beyondtechnology.com/home.shtml More tips and tutorials for the Excel user and VBA programmer.
http://www.vbapro.com/ The VBA pro provides information, links, and resources for Microsoft Excel and Visual Basic for Applications (VBA).
http://visualbasic.miningco.com/cs/vba/ This site contains articles and tutorials for beginning and advance Visual Basic programmers.
http://www.vb-bookmark.com/vbaTutorial.html This site contains more articles, tutorials, and source code for the VBA programmer.
http://peach.ease.lsoft.com/archives/visbas-beginners.html Search L-Soft International's archives for help with VBA

Microsoft Links:
http://msdn.microsoft.com/vba/ Microsoft's VBA home page with useful links and information for programmers.
http://office.microsoft.com/Downloads/ Download updates, extras and add-ins for Excel from Microsoft.
http://communities.microsoft.com/home/default.asp Join a Microsoft newsgroup and communicate with others about Microsoft development products including VBA.

ActiveX Downloads:
http://www.vision-factory.com/activex.htm Download ActiveX controls to use with your VB and VBA programs from Vision Factory Multimedia.
http://abstractvb.com/downloads.asp abstractvb.com provides some excellent ActiveX controls for download as well as code samples and tutorials in Visual Basic.
http://www.a-softtech.com/Free/Controls.html A few more ActiveX controls from A-Soft Technologies.
http://axsoft.hypermart.net/products.htm More ActiveX controls from AXSoft.
http://sevillaonline.com/ActiveX/ More ActiveX controls from Alvaro Redondo

Links taken from the Microsoft Excel VBA Programming for the Absolute Beginner.

Copyright Notice. All documents and text contained in this web site www.spyjournal.biz and sub pages is copyright material of Jethro Management (c) 2004 unless noted as being copyright material of someone else. No public reproduction of the content on this site in any form is permitted without express written permission.

Unless otherwise expressly stated, all original material of whatever nature created by Tim Miller and included in this weblog and any related pages, including the weblog's archives, is licensed under a Creative Commons License.