February 28, 2005

Class Modules / VBA Tips by Colo

Colo (Masaru Kaji) of Colo's Junk Room is working on tutorial to explain Class Modules. (As mentioned before, I used a Class Module for the first time at Colo's suggestion for my Cell Spotter add-in so you can assume that I'm very interested in learning more about them)



It's still a work in progress. At this time, two chapters have been uploaded and a third is on it's way with more to come at a later date.

As with all his tips, Colo doesn't just show the code but explains in detail how things actually work. Understanding the theory itself makes it a lot easier when it comes to developing your own stuff.



I'm happy to include Colo's tips as a permanent addition to my side bar. There's always something new to see, something new to learn. Please become a regular visitor like me ;-)

P.S. Colo's VBA Tips also has a RSS and XML feed so you can see when new tips become available using his EaZy RSS reader add-in, available on his Downloads page.

You can use it while still using Excel but don't forget to set it to "Ninja Mode" if you are using it at work!  

Posted by andrewe at 21:24

February 23, 2005

Cell Spotter

A little something I've been working on... :-)

As you know, I've been experimenting with Events and taking note of advantages and disadvantages with each. The very best add-in I've seen to date for highlighting cells is Chip Pearson's Row Liner. It's been in the back of my mind for a while so I thought I would have a shot at making something similar.

Cell Spotter


After thinking it through, I decided to base my Cell Spotter on a BeforeDoubleClick event. Used only when you need it, you can still use Undo in between clicks and leaving the right click menu enabled has definite advantages as you will see.

I would also like to thank my friend, Masaru Kaji (aka Colo) for his suggestion regarding using a Class Module (he says it's time I learned about them) so I am quite happy to give him 50% credit for his part in our "joint project".

Okay, here's how to use it.

Things you can do
Double click any cell to make lines appear on all four sides of the cell. (The color and line thickness is set but you can rewrite the code if you know how to, the code can be seen by pushing Alt and F11, then navigating to the Cell_Spotter.xla file)

The lines themselves can be moved, edited or even deleted within the workbook. If you want to make one or more of the lines "permanent", use the Name Box in the top right corner (see pic below) to change the line name(s), then push Enter, so it(they) won't be deleted next time you double click.



An alternative way to delete all four lines is to select Cell Spotter, Delete Lines from the right click menu (this code was copied from Colo's site too).



Note the lines are set not to show up when printing. (You can reverse this setting by double clicking the lines and ticking the check box on the Property tab)

Believe me, this add-in makes working with Excel a lot easier. You can download Cell Spotter here.  
Posted by andrewe at 20:30

February 20, 2005

Change Events 2

Rob van Gelder kindly gave me some code for selecting the entire row with the active cell remaining in place.

I changed it a little as below to work with the BeforeRightClick event with Cancel = True at the end to prevent the Right Click menu from appearing.

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    With Target: Range(.Address & "," & .EntireRow.Address).Select: End With
    Cancel = True
End Sub



This seems the best solution so far. Thanks to Rob's help, the active cell is highlighted without actually changing the color or anything else. Another thing is you can still do things like copy and paste (left click first) and even use Undo which was not possible by using SelectionChange.

On the downside, the Right Click menu is disabled. You can still use it by removing Cancel is True and put up with it, or you could try using BeforeDoubleClick instead of BeforeRightClick. The only problem is that don't place the cursor properly within the cell, the selection tends to fly up or to the left, increasing row width might help in this case. With BeforeRightClick you can also select multiple rows.

Note that you can limit the event to a singe sheet by using Visual Basic Editor to select the appropriate sheet from the Project Explorer, then using "Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)" instead of "Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)".  
Posted by andrewe at 20:59

February 16, 2005

Change Events

I was experimenting with Events at work today. It's always a good way to try out new stuff.

This is some code I wrote to make selected cells stand out a bit more. If you want to try it, insert the code into "This Workbook" of a preferably blank file (formatting changes can be reversed if you don't save) using the Visual Basic Editor.

Code 1 - Select the Entire Row
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
        ByVal Target As Range)
    Selection.EntireRow.Select
End Sub



Observations: Does not change formatting but practically useless if you want to select cells that are not in Column A.

Code 2 - Change the Row Height
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
        ByVal Target As Range)
    Cells.RowHeight = 14.25
    Selection.EntireRow.RowHeight = 50
End Sub



Observations: Getting better. Again, does not change formatting and you can select any cell you want to.

Code 3 - Change the Cell's Color
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
        ByVal Target As Range)
        Cells.Interior.ColorIndex = xlColorIndexNone
    Union(Selection, Selection.EntireRow, _
    Selection.EntireColumn).Interior.ColorIndex = 6
End Sub



Observations: Easiest to see but does change formatting so must be used with caution. Won't work with Conditional Formatting. Once again, you can select any cell you want to.

A couple of points.

1. I have seen better code written by more experienced coders, but this is a good way I've found to expand my own knowledge. At the same time, I think looking at other's code is also a great to learn. Sometimes I tend to use Google to look for similar code to what I want, it's better than just asking unless you are really stuck. But then again, that's what forums and friends are for right?

2. The nature of this Event means that it is used each time you change your selection. This means that you cannot use Undo if you make a mistake. It might still be useful depending on how and why you may use it though.

In the meantime, I'll keep on experimenting ;-)  
Posted by andrewe at 21:30

February 13, 2005

Showing the Print Area

There are times when it's useful to show the Print Area. I like to use lines around the Print Area that don't actually show up when printing.

Here's a picture of the Print Area shown in blue.



I select cells just outside the Print Area and use Format Cells, Border from the right-click menu to add lines on all four sides around the outside.



Heavier lines on two adjoining sides gives a nice 3-D effect.



If you've done things right the lines won't show up when printing as mentioned at the top.  
Posted by andrewe at 19:10

February 09, 2005

Excel and Email

It had to happen sooner or later. I've just been to Mark Wielgus's most EXCELlent blog, AutomateExcel.com and saw that he recently posted about using email with Excel.

By funny coincidence I was working on this today and came up with the following code.

Email the Active Sheet
Sub MailSheet()
On Error GoTo Terminator
    Application.ScreenUpdating = False
    Dim shtName As String
    shtName = ActiveSheet.Name
    ActiveSheet.Copy
        ActiveWorkbook.SaveAs     Filename:=Application.GetSaveAsFilename("Copy of " & _
    shtName, "Microsoft Excel File, *.xls")
        Application.DisplayAlerts = False
    Application.Dialogs(xlDialogSendMail).Show
        With ActiveWorkbook
    .ChangeFileAccess xlReadOnly
    Kill .FullName
    .Close False
    End With
Terminator:
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Email the Active Book
Sub MailBook()
    Application.Dialogs(xlDialogSendMail).Show
End Sub

Both of these versions send Excel as an attached file so you can add a message if you want to. I've also just added to with my utilities so please download them if you think they might be useful.  
Posted by andrewe at 20:34

February 07, 2005

Random Numbers 5

I've been meaning to write about this for a while. The other day a friend here in Japan showed me a neat trick to get unique random numbers.

1. Enter =RAND() into a range as below.



2. In the next column, use RANK like this.



Now, the odds of getting the same number more than once are very remote, but here's a way to make sure. Change the formula like this,



And then,



Finished! I thought it was pretty good ;-)  
Posted by andrewe at 21:16