May 31, 2005

Topsy Turvy

Sometimes I want to reverse a list. Here's one of many ways.

Upside Down
Enter this in the first row and drag down. (Add numbers to ROW() as in ROW() + 2 if you want to start in say Row 3)

=OFFSET($D$1,COUNTA(D:D)-ROW(),0)

(Original list shown in Column D)



Right to Left
Enter this in the first column and drag right. (Add numbers to COLUMN as in COLUMN() + 2 if you want to start in say Column C)

=OFFSET($A$1,0,COUNTA(1:1)-COLUMN())

Note both formulas will automatically adjust if your list does not start in the first row (first formula) or column (second formula) accordingly.

Randomize a List
Remember this? Here's a way to randomize a list. First enter RAND in a suitable column and drag down.

=RAND()

Then this formula in another column.

=OFFSET($D$1,RANK(E1,$E$1:$E$26)-1,0)

(Original list shown in Column D)



Same for columns. Enter RAND in a suitable row, then this formula in another. (Change your references to suit)

=OFFSET($A$1,0,RANK(B2,$A$2:$L$2)-1)

Blank cells in a list will show as zeroes. Get rid of them like this.  

Posted by andrewe at 22:30

May 27, 2005

Add Items to Multiple Menus

The other day there was a question on the JMT forum about adding the same items to multiple menus. It was good timing for me as I was wondering about the same thing myself.

When learning to add an item to a right click menu, I was satisfied at first to add items to only the Cell menu (to see how much right click menus differ, try right clicking cells, row or column headers, autoshapes or sheet tabs). But after a while I started to think it would be good add the same items to multiple menus, in this case the Cell, Row and Column menus. Here's what my new code looks like. (A couple of sub menu items are thrown in also)

Sub AddMenuItems()
    On Error Resume Next
    ResetMenu    ' Delete menu items if they already exist
    Dim cBar As CommandBar
    For Each cBar In Application.CommandBars
        Select Case cBar.Controls.Parent.Name
        Case "Cell", "Row", "Column": GoTo AddMenu
        Case Else: GoTo NoMenu
        End Select
AddMenu:
        With cBar.Controls.Add(Type:=msoControlPopup)
            .Caption = "My Menu"
            .BeginGroup = True
            With .Controls.Add(Type:=msoControlButton)
                .Caption = "Run Macro 1"
                .FaceId = 71    'Optional
                .OnAction = "MyMacro1"
            End With
            With .Controls.Add(Type:=msoControlButton)
                .Caption = "Run Macro 2"
                .FaceId = 72    'Optional
                .OnAction = "MyMacro2"
            End With
        End With
NoMenu:
    Next cBar
End Sub

Sub ResetMenu()
    On Error Resume Next
    Dim cBar As CommandBar
    For Each cBar In Application.CommandBars
        cBar.Controls("My Menu").Delete
    Next cBar
End Sub

Sub MyMacro1()
    MsgBox "Howdy", , "My Macro 1"
End Sub

Sub MyMacro2()
    MsgBox "Doody", , "My Macro 2"
End Sub

Study, study on the weekend... ;-)  
Posted by andrewe at 15:11

May 22, 2005

Simple Graphs

I don't have much call to make graphs but I like to experiment at times. Here's a couple of ways to make some very simple graphs that don't use Excel's regular graph features.

With a Formula
As you can see in the picture below, I've used the REPT function here with some relative referencing to cell A1 and dragged down. (The font used was Wingdings to make "n" a square shape)



The formula for the horizontal bars is

=REPT("n",A1)

The vertical bars were done the same way, but I merged cells first to get some more space, then used Format, Alignment then Orientation of 90 degrees.



With Conditional Formatting
Actually I've seen the above method before so I decided to try something a little different with Conditional Formatting. As you can see the formula used is quite easy.



And in this case, I've added numbers 1 to 10 in white font to match the background color. Conditional Formatting is then used to change the font to black and also to add borders (lines) if the following condition is True.



Of course I don't imagine that units from 1 to 10 will be used that often but it shouldn't be too hard to adjust your formulas to suit.

Update: Jon Peltier kindly sent me a file demonstrating 2 conditional formats to get something that looks more like a regular column chart, (borders at the sides only except for the top, figures also shown only at the top).



I've uploaded his file so you can refer to it here.

Thanks very much Jon!  
Posted by andrewe at 20:58

May 19, 2005

Calculation Settings

The following is some rather well known code that you can use to speed up your macros. It works by setting Calculation to Manual, running your code, then resetting Calculation to it's original status after the code has run. (Note ScreenUpdating being set to False, this keeps your screen "frozen" until the code is finished, this is a time saver too)

Sub MyMacro()
Dim CalSet as Long
    CalSet = Application.Calculation
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

'Place your code here

    Application.Calculation = CalSet
    Application.ScreenUpdating = True
End Sub

You can also change your Calculation settings by going to Tools, Options, Calculation. Switching to Manual Calculation can make working with large files a easier, but there are some very important things to keep in mind first.

The Good
As mentioned above, Manual Calculation can make things run a lot faster when inputting data, working with filters, even your macros depending on the code used and your original Calculation setting. (How your files are set up will have a big influence on how much time is saved)

The Bad
Formulas don't work (well, that's not surprising), neither does Conditional Formatting (spotty at best), but you can tap the F9 key to calculate whenever you want to update (or Shift + F9 just to calculate the active worksheet).

The Ugly
I've looked in the Help files and this part doesn't seem to be mentioned. If you switch between Automatic and Manual, all open workbooks are "set" to that mode and will be saved accordingly. (You will be asked if you want to save changes when closing, but this might be overlooked if you lose track of which mode you are using)

Also be aware that the first workbook opened sets the Calculation mode for all subsequent workbooks. This means if the first workbook is set to Manual Calculation, all other workbooks opened later on will set to Manual by default. If you are careful, this may not be a problem, but consider the following scenarios. (Please correct me if I am incorrect or missing something)

1. You unwittingly save a Workbook in Manual Calculation. Several other workbooks might also be saved in this mode without you realizing what has happened and/or not knowing which workbooks were saved in this setting. (Don't forget you can always check by going to Tools, Options, Calculation if necessary)

2. A co-worker opens a workbook set in Manual Calculation or you email a workbook in that was saved Manual Calculation . This can cause problems because the user may fall into the same trap as above. Depending on their experience and /or whether they notice (assuming they actually know what Manual Calculation is), this might end up being a very big problem.

Use with due caution.

P.S. I use message boxes in some files with Workbook Open and Before Close events to verify Calculation settings and offer an option to change to either Automatic or Manual. But adding this to each and every file is not very practical :-(  
Posted by andrewe at 02:20

May 15, 2005

Excel Heaven

This really makes my day...

Dick Kusleika from Daily Dose of Excel wrote about it in Daily Dose Headlines.

And John Walkenbach (aka J-Walk) made this very convenient link to show how it's done.

Mark Wielgus of AutomateExcel fame has also written about it and shows a nice way to make your own.

Meanwhile, Colo has added the JMT Forum feed to make his own version.

This just keeps getting better and better... Thanks guys!  
Posted by andrewe at 19:49

May 09, 2005

Ordinal Numbers

I haven't written a formula for a while so here's a nice "easy" one for ordinal numbers (as in 1st, 2nd, 3rd etc) I'm kind of surprised it works with single digits, maybe it's a "good" bug.

=A1&IF(OR(RIGHT(A1,2)="11",RIGHT(A1,2)="12",RIGHT(A1,2)="13",MOD(A1,10)=0,MOD(A1,10)>=4),"th",CHOOSE(RIGHT(A1,1),"st","nd","rd"))



Has this been done before? Probably. But, you know me, I prefer to try my own way first, then look at what others have done later. That way your chances of learning something new increases even more ;-)  
Posted by andrewe at 22:13