Converting Numbers to Words Part IV

See Converting Numbers to Words Part III

On to the thousands:

Sub TEST_Thousands()
   
    Debug.Assert NumbersToWords(1000) = "one thousand"
    Debug.Assert NumbersToWords(1001) = "one thousand one"
    Debug.Assert NumbersToWords(1099) = "one thousand ninety-nine"
    Debug.Assert NumbersToWords(1200) = "one thousand two hundred"
    Debug.Assert NumbersToWords(1310) = "one thousand three hundred ten"
    Debug.Assert NumbersToWords(1999) = "one thousand nine hundred ninety-nine"
   
End Sub

Every triplet of numbers follow the same rules, and I’ve already tested 0-999. I need to take all that relevant code and put it in a callable function.

Function ProcessTriplet(ByVal dNumber As Double, Optional ByVal sSuffix As String) As String
       
    Dim sReturn As String
    Dim vaSingles As Variant, vaTens As Variant
   
    vaSingles = Split("zero,one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen", ",")
    vaTens = Split("zero,zero,twenty,thirty,forty,fifty,sixty,seventy,eighty,ninety", ",")
   
    If dNumber >= 100 Then
        sReturn = sReturn & vaSingles(dNumber \ 100) & " hundred "
        dNumber = dNumber - (dNumber \ 100) * 100
    End If
   
    If dNumber > 19 Then
        sReturn = sReturn & vaTens(dNumber \ 10)
        dNumber = dNumber - (dNumber \ 10) * 10
    End If
   
    If dNumber > 0 Then
        If Right(sReturn, 1) = "y" Then
            sReturn = sReturn & "-"
        End If
   
        sReturn = sReturn & vaSingles(dNumber)
    End If
   
    sReturn = sReturn & Space(1) & sSuffix
   
    ProcessTriplet = Trim(sReturn)
   
End Function

That’s the same old code, just put into a function. The triplet of numbers is passed in and the suffix (currently only “thousand”) is passed in. Now I just need to divide my number into triplets, process them, and concatenate the answers.

Function NumbersToWords(ByVal dNumbers As Double) As String
   
    Dim sReturn As String
    Dim dRemainder As Double
   
    If dNumbers = 0 Then
        sReturn = "zero"
    Else
       
        dRemainder = dNumbers
       
        If dRemainder >= 1000 Then
            sReturn = ProcessTriplet(dRemainder \ 1000, "thousand")
            dRemainder = dRemainder - ((dRemainder \ 1000) * 1000)
        End If
       
        If dRemainder > 0 Then
            sReturn = sReturn & Space(1) & ProcessTriplet(dRemainder)
        End If
       
    End If
   
    NumbersToWords = Trim$(sReturn)
   
End Function

If my numbers is 1,000 or more, I process the thousands triplet and figure the remainder. Then I process the remainder as its own triplet. I guess processing triplets in this way makes my next set of tests already pass.

Sub TEST_TenThousands()

    Debug.Assert NumbersToWords(10000) = "ten thousand"
    Debug.Assert NumbersToWords(10001) = "ten thousand one"
    Debug.Assert NumbersToWords(20099) = "twenty thousand ninety-nine"
    Debug.Assert NumbersToWords(30200) = "thirty thousand two hundred"
    Debug.Assert NumbersToWords(42310) = "forty-two thousand three hundred ten"
    Debug.Assert NumbersToWords(99999) = "ninety-nine thousand nine hundred ninety-nine"

End Sub

Yep, already passing. On deck, the millions.

Converting Numbers to Words Part III

See Converting Numbers to Words Part II

My tests work from 0-99. The next test will test numbers between 100-199.

Sub TEST_OneHundred()

    Debug.Assert NumbersToWords(100) = "one hundred"
    Debug.Assert NumbersToWords(110) = "one hundred ten"
    Debug.Assert NumbersToWords(119) = "one hundred nineteen"
    Debug.Assert NumbersToWords(120) = "one hundred twenty"
    Debug.Assert NumbersToWords(121) = "one hundred twenty-one"
    Debug.Assert NumbersToWords(150) = "one hundred fifty"
    Debug.Assert NumbersToWords(188) = "one hundred eighty-eight"
    Debug.Assert NumbersToWords(199) = "one hundred ninety-nine"

End Sub

A haphazard selection of numbers including the edge cases.

Function NumbersToWords(ByVal dNumbers As Double) As String
   
    Dim vaSingles As Variant
    Dim vaTens As Variant
    Dim sReturn As String
   
    vaSingles = Split("zero,one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen", ",")
    vaTens = Split("NA,NA,twenty,thirty,forty,fifty,sixty,seventy,eighty,ninety", ",")
       
    If dNumbers >= 100 Then
        sReturn = "one hundred"
        If dNumbers Mod 100 <> 0 Then
            If dNumbers - 100 > 19 Then
                sReturn = sReturn & Space(1) & vaTens((dNumbers - 100) \ 10)
                If (dNumbers - 100) Mod 10 <> 0 Then
                    sReturn = sReturn & "-" & vaSingles((dNumbers - 100) - (((dNumbers - 100) \ 10) * 10))
                End If
            Else
                sReturn = sReturn & Space(1) & vaSingles(dNumbers - 100)
            End If
        End If
       
    ElseIf dNumbers > 19 Then
        sReturn = vaTens(dNumbers \ 10)
        If dNumbers Mod 10 <> 0 Then
            sReturn = sReturn & "-" & vaSingles(dNumbers - ((dNumbers \ 10) * 10))
        End If
    Else
        sReturn = vaSingles(dNumbers)
    End If
   
   
    NumbersToWords = Trim$(sReturn)
   
End Function

And all tests pass. Back in the first post of this series I said that I hoped it would be obvious when I need to refactor. Well if this isn’t a frying pan to the face, I don’t know what is. Way too much repetition, for one. I need to introduce a “remainder” variable, so that once I process the hundred part, I can send the remainder to process the tens, and the remainder from that to the less than 19 part.

Function NumbersToWords(ByVal dNumbers As Double) As String

    Dim vaSingles As Variant
    Dim vaTens As Variant
    Dim sReturn As String
    Dim dRemainder As Double

    vaSingles = Split("zero,one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen", ",")
    vaTens = Split("zero,zero,twenty,thirty,forty,fifty,sixty,seventy,eighty,ninety", ",")

    dRemainder = dNumbers

    If dRemainder >= 100 Then
        sReturn = "one hundred" & Space(1)
        dRemainder = dRemainder - (dRemainder \ 100) * 100
    End If

    If dRemainder > 19 Then
        sReturn = sReturn & vaTens(dRemainder \ 10)
        dRemainder = dRemainder - (dRemainder \ 10) * 10
    End If

    If dRemainder > 0 Then
        If Right(sReturn, 1) = "y" Then
            sReturn = sReturn & "-"
        End If

        sReturn = sReturn & vaSingles(dRemainder)
    End If

    NumbersToWords = Trim$(sReturn)

End Function

That looks much better, but it doesn’t pass the zero test. I don’t like special cases, but zero might just be one, so I’m going to force it. My conditional on whether to include a hyphen checks to see if the answer so far ends in “y”. That seems a little hokey, but it works. I could test for mod10 and set a Boolean variable in the If block above, but I’m not sure what I gain, so there it stays.

Refactoring in this way also makes the next bit of testing code painfully obvious. I’m hardcoding “one hundred”, but with vaSingles sitting right there, I don’t know why I can’t go above 199 pretty easily. So I’ll write that next test.

Sub TEST_Hundreds()

    Debug.Assert NumbersToWords(200) = "two hundred"
    Debug.Assert NumbersToWords(310) = "three hundred ten"
    Debug.Assert NumbersToWords(419) = "four hundred nineteen"
    Debug.Assert NumbersToWords(520) = "five hundred twenty"
    Debug.Assert NumbersToWords(621) = "six hundred twenty-one"
    Debug.Assert NumbersToWords(750) = "seven hundred fifty"
    Debug.Assert NumbersToWords(888) = "eight hundred eighty-eight"
    Debug.Assert NumbersToWords(999) = "nine hundred ninety-nine"

End Sub

Instead of hardcoding “one hundred”, I’ll pull the property number from vaSingles. This also shows my brute force zero fix.

Function NumbersToWords(ByVal dNumbers As Double) As String

    Dim vaSingles As Variant
    Dim vaTens As Variant
    Dim sReturn As String
    Dim dRemainder As Double

    vaSingles = Split("zero,one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen", ",")
    vaTens = Split("zero,zero,twenty,thirty,forty,fifty,sixty,seventy,eighty,ninety", ",")

    If dNumbers = 0 Then
        sReturn = "zero"
    Else

        dRemainder = dNumbers
   
        If dRemainder >= 100 Then
            sReturn = sReturn & vaSingles(dRemainder \ 100) & " hundred "
            dRemainder = dRemainder - (dRemainder \ 100) * 100
        End If
   
        If dRemainder > 19 Then
            sReturn = sReturn & vaTens(dRemainder \ 10)
            dRemainder = dRemainder - (dRemainder \ 10) * 10
        End If
   
        If dRemainder > 0 Then
            If Right(sReturn, 1) = "y" Then
                sReturn = sReturn & "-"
            End If
   
            sReturn = sReturn & vaSingles(dRemainder)
        End If
    End If

    NumbersToWords = Trim$(sReturn)

End Function

All tests pass. And the code doesn’t look too bad. Only infinity numbers left to test. Here’s what my main testing procedure looks like now, as if you couldn’t guess.

Sub TEST_All()

    TEST_Singles
    TEST_Tens
    TEST_OneHundred
    TEST_Hundreds

    Debug.Print "tests passed"

End Sub

Converting Numbers To Words Part II

See Converting Numbers To Words Part I.

The next test will test 20-99. I think in real TDD, you’re supposed to write tests that test just one thing. But I’m not doing real TDD, so I’m testing in groups.

Sub TEST_Tens()

    Debug.Assert NumbersToWords(20) = "twenty"
    Debug.Assert NumbersToWords(21) = "twenty-one"
    Debug.Assert NumbersToWords(30) = "thirty"
    Debug.Assert NumbersToWords(77) = "seventy-seven"
    Debug.Assert NumbersToWords(99) = "ninety-nine"

End Sub

Again, I’m testing the edges and few in between. Now that I have two test procedures, I’ll need to create a procedure to run them both.

Sub TEST_All()

    TEST_Singles
    TEST_Tens

    Debug.Print "tests passed"

End Sub

Now I can run TEST_All and make sure I don’t break any previous tests with the changes I make. Of course, TEST_Tens fails so it’s time to write some code to make it pass. I tried to write the simplest code possible, but it didn’t work out for me.

Function NumbersToWords(ByVal dNumbers As Double) As String
   
    Dim vaSingles As Variant
    Dim vaTens As Variant
    Dim sReturn As String
   
    vaSingles = Split("zero,one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen", ",")
    vaTens = Split("NA,NA,twenty,thirty,forty,fifty,sixty,seventy,eighty,ninety", ",")
   
    If dNumbers > 19 Then
        sReturn = vaTens(dNumbers \ 10) & "-" & vaSingles(dNumbers - ((dNumbers \ 10) * 10))
    Else
        sReturn = vaSingles(dNumbers)
    End If
   
    NumbersToWords = Trim$(sReturn)
   
End Function

That fails because NumbersToWords(20) returns twenty-zero. So there’s a special case that needs to be handled.

Function NumbersToWords(ByVal dNumbers As Double) As String
   
    Dim vaSingles As Variant
    Dim vaTens As Variant
    Dim sReturn As String
   
    vaSingles = Split("zero,one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen", ",")
    vaTens = Split("NA,NA,twenty,thirty,forty,fifty,sixty,seventy,eighty,ninety", ",")
   
    If dNumbers > 19 Then
        sReturn = vaTens(dNumbers \ 10)
        If dNumbers Mod 10 <> 0 Then
            sReturn = sReturn & "-" & vaSingles(dNumbers - ((dNumbers \ 10) * 10))
        End If
    Else
        sReturn = vaSingles(dNumbers)
    End If
   
   
    NumbersToWords = Trim$(sReturn)
   
End Function

That works. But I can see this special case handling becoming a problem. Maybe. We’ll see what happens when we test in the hundreds.

Converting Numbers To Words Part I

I overheard two people talking, one of whom was showing his intellectual prowess using that puzzle whose answer is always ‘four’. I don’t remember the specifics of the game, but you count the letters of an integer, do some basic math, and you always end up with ‘four’ because the word ‘four’ has that many letters in it. I get how it works, but I thought there’s surely another number with that property. Off to VBA to find out.

There’s not. But let me back up a bit. I searched the vast DDoE archives to find my Numbers-to-Words code. I’ve already written this code right? Apparently not. A broader search showed plenty of hits and, I’m sure, code, but I didn’t click any of the links. I’ve been hankering to write some test-first code and this seemed like a good candidate. I didn’t want to read anybody else’s code so it wouldn’t influence my test-first experience.

I’m going to document my test-first methodology in a series of posts. This is not test driven development (TDD), but borrows some of the principles from that. Basically, I write a test, watch it fail, then write enough code to make it pass. I refactor the code as necessary. If you are interested in TDD in Excel and VBA, search for xlUnit on Mike Woodhouse’s blog.

On to test 1! The idea is to write a function that returns a string that represents the English words of the argument. The procedure header should look like this:

Function NumbersToWords(ByVal dNumbers As Double) As String

First, write a test.

Sub TEST_Singles()
   
    Debug.Assert NumbersToWords(0) = "zero"
    Debug.Assert NumbersToWords(1) = "one"
    Debug.Assert NumbersToWords(7) = "seven"
    Debug.Assert NumbersToWords(19) = "nineteen"
   
End Sub

I know that once I hit 20 a pattern will start emerging, so I’m going to start by testing up to 19. Now I have to write enough code to make this pass. That’s seems pretty easy.

Function NumbersToWords(ByVal dNumbers As Double) As String
   
    Dim vaSingles As Variant
   
    vaSingles = Split("zero,one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen", ",")
   
    NumbersToWords = vaSingles(dNumbers)
   
End Function

When I run that test, it passes. I put all the words for zero to 19 in an array, then read the array. What could be easier? For my tests, I choose the two edge cases (0 and 19) and a couple in the middle. It’s not very rigorous, but what the heck. One of the benefits of a test-first approach is that you’re biting off a bigger problem in smaller chunks. No single code revision is particularly difficult, but when put together can be complex. The refactoring part will be the interesting part. I assume it will be apparent when it’s time to refactor (as it is in examples I’ve seen) but I’ve never done it before, so we’ll see.

Next time, I’ll test some ‘tens’.

Playing Peekaboo with the Used Range

I was trying to reset the used range in a spreadsheet template I inherited the other day, that had a much bigger used range than it should have had. Damned if I could get it to reset. Tried deleting rows and columns below and to the right of the actual used range. Tried this:

Sub ResetUsedRange()
Dim sht As Worksheet
Dim lng As Long

For Each sht In ActiveWorkbook.Worksheets
    lng = sht.UsedRange.Rows.Count
Next

End Sub

Still no joy.

Then after much playing around, I discovered the problem. Several problems, really:

  • If you hide a row – or adjust it’s width, then the UsedRange is extended down to that row. But only for rows. And deleting all the rows below your ‘actual’ used range soon fixes this problem.
  • Hiding columns or adjusting their width does nothing to the used range. Unless you hide a column, and then unhide it by manually dragging on the boundary of the hidden column in order to resize it. Then you just screwed up the UsedRange bigtime: the used range now extends to that column, and it is devilishly hard to reset it again. Deleting all columns to the right of your ‘actual’ used range does NOT fix the problem.

Try it. You’ll find that the extended used range this gives is much more persistent than the Spanish Football Team.

After much experimentation, I found that you can only reset the used range back to what it should be if you:

  1. Hide that pesky column again.
  2. Unhide that pesky column again, using the Right Click > Unhide method.
  3. Delete that pesky column.
  4. Delete the perfectly innocent column that took it’s place.

Then and only then could I get the used range to go back to it’s cage. Weird. But might explain why you’ve had used ranges in the past that you simply could not get to behave.

(UPDATE:As demonstrated by snb’s code, the weird result you get by resizing a hidden column disappears if you close and then reopen the workbook.)

In the course of all this, I noticed that if you hide a row, then if you put the cursor over the hidden row and double-click when this icon comes up:
Autofit Row
…then the row is unhidden.

But if you hide a column, and then double click when this comes up:
Autofit Column
…absolutely nothing happens. What’s with that?

In both cases, the Macro Recorder spits out Rows(“25:25″).EntireRow.AutoFit or Columns(“M:M”).EntireColumn.AutoFit as the case may be. But in the case of hidden columns, it just doesn’t seem to do anything.

Weird again.

T Accounts

I’ve made an Excel template for filling out T accounts. For you non-accounting folks, a T account is a method to trace accounting transactions through the accounts on the general ledger. Hey, wake up! This post isn’t finished yet!

I’ve made a T account template at least three times in my Excel career. They never work out. They never make it easier than pencil-to-paper or marker-to-whiteboard. It doesn’t seem to stop me from trying, though, and now I’m making my latest attempt available to you. First, let’s look at this huge image (click to embiggen).

The top left section (C2:F11) is where you write a brief description for up to 10 transactions. Column C is the transaction number and can’t be edited. Column D is where you type the brief description. Column E is overflow for column D because I hate merged cells. Column F is the general ledger period. You can put them all in the same GL Period if it’s not applicable to your transactions. You don’t have to use this section. That is, you can leave off the descriptions and the periods, but good luck trying to figure out what you were thinking.

In this example, the transactions are listed in the order they will happen over time. This series of transactions represent a sale where we get a kickback from the vendor and we’re passing that savings on to the customer. First, we buy the inventory from the vendor. Next, we ship it to the our customer. At that point we need to make our costs correct by accruing the rebate we’re entitled to from the vendor. We pay the vendor, get paid by the customer, and finally get our rebate from the Vendor.

The main section of the workbook is a 3×3 grid of T accounts. This is where the magic happens. The top left cell of each T account is a financials statement classification number. It’s a data validation dropdown containing the numbers 1-7.

  1. Assets
  2. Liabilities
  3. Equity
  4. Sales
  5. Cost of Goods Sold
  6. Expenses
  7. Other Income and Expenses

That financial statement classification number is replicated down the hidden column B (and H and N) for formula purposes.

To the right of the financial statement classification number is the (merged) cell where you type the account name. The account name is only for your reference and has no bearing on any formulas in the model.

The lighter green section down the left side of the T account is a series of data validation dropdowns that allow you to select the transaction number. The main white area of the T account that’s divided vertically is the area where you record your debits and credits. At the bottom of the T account, the debits and credits are summed up and the net debit or credit is displayed.

There are two other areas to the right of the T accounts grid. The top area shows the effects on the major sections of your balance sheet and income statement. It expands to the right for however many periods you have. The financial statement classification number at the top left of the T account determines where your transaction ends up in this area. And, obviously, the period you identify in the transactions section determine the column.

The bottom area is a list of the 10 transactions and the net debit or credit by transaction. If any of these are not zero, you’re missing a piece of the transaction.

Please leave your thoughts and suggestions in the comments.

You can download TAccounts.zip

FastExcel V3 Released – At Last!

As some of you may know I have been working on developing Version 3 of my FastExcel product for more years than I care to remember.
First there was the VB6 blind alley, then 64-bit and multi-threaded calculation and that ribbon thing, then I detoured into learning C++ and XLLs, then there was the inevitable scope creep, (and my consulting customers kept asking me to do things, and then there was the sailing) … you get the picture.

Anyway I finally pushed the release button on June 4 2014. Yippee!!!

The initial reaction has been great (no doubt helped by the introductory offer of 50% off).

So what is FastExcel V3?

There are 3 major products in the FastExcel V3 family, targeted at different types of use scenarios.

FastExcelV3

FastExcel V3 Profiler

The Profiler gives you a comprehensive set of tools focussed on finding and prioritising calculation bottlenecks. If your spreadsheet takes more than a few seconds to calculate you need FastExcel profiler to find out and prioritize the reasons for the slow calculation

  • Profiling Drill-Down Wizard
  • Profile Workbook
  • Profile Worksheet
  • Profile Formulas
  • Map Cross-References

FastExcel V3 Manager

FastExcel Manager contains tools to help you build, debug and maintain Excel workbooks.

  • Name Manager Pro – an invaluable tool for managing Defined Names and Tables
  • Formula Viewer/Editer – a better way of editing and debugging more complex formulas.
  • Sheet Manager – Easily manage and manipulate worksheets.
  • Workbook – Cleaner – Trim down any excess bloat in your workbooks
  • Where-Used Maps – See where your Defined Names, Number Formats and Styles are being used

FastExcel SpeedTools

SpeedTools provides you with a state-of-the-art tool-kit to help you speed up your Excel Calculations

  • Calculation timing tools for workbooks, worksheets, and ranges
  • Additional calculations modes to enhance control of calculation so that you only calculate what needs to be calculated.
  • 90 superfast multi-threaded functions
  • Faster and more powerful Lookups and List comparisons
  • Multi-condition filtering and Distinct formulas to eliminate many slow SUMPRODUCT and Array formulas
  • Enhanced functions for Array handling, text, mathematics, sorting, information and logic

If you want to find out more just go to my website

or Download the 15-day full-featured trial of FastExcel V3

Tiered Commissions and Counting Zeros

I was writing some formulas for a tiered commission calculation recently that I thought I should post. But beyond just what the formulas do, it reminded me that I’ve never shared my ‘counting zeros’ opinion, so I’m wrapping that in with this post too.

You have a commission structure where you pay your salesmen 5% for every sale. If the sale is a particularly large one, you pay them a bonus commission – 8% for the portion of the sale that’s over $20,000. But you don’t want your salespeople getting so rich that they have enough money to quit. Nor do you want them to get an unfairly huge commission on an unusually large sale. So you have a third tier that reduces their commission to 1% for the portion of the sale that’s over $100,000.

Let’s look at the formulas for column H.

    H4        =MAX(MIN(2*10^4,H2)*0.05,0)
    H5        =MAX(MIN(8*10^4,H2-2*10^4)*0.08,0)
    H6        =MAX(0,H2-10^5)*0.01
  • In H4, Take the smaller of $20,000 and whatever is in H2 and multiply it by 5%. Then take that larger of that result or 0, just in case there’s a negative sale in there. (5% of $20,000 = $1,000)
  • In H5, Take the smaller of $80,000 ($100k less $20k) and the amount that H2 exceeds $20,000 and multiply by 8%. Then take the larger of that result or zero. (8% of $80,000 = $6,400)
  • In H6, Take the larger of 0 and the amount less $100,000 and multiply by 1%. (1% of $1,000 = $10)

The MIN part of the formulas in H4 and H5 make sure you don’t pay more commission on that tier than you should. The MAX part returns zero when the calculation goes negative.

About counting zeros. You may have noticed that I use terms like 2*10^4 to represent $20,000. I’m a big fan of commas, but I can’t use them in formulas (they’re kind of important for separating arguments). I picked up using scientific notation in formulas from a scientist I know and I love it. No more do I have count the zeros in

=IF(A1=25000000,600000,8000000)

to know if it’s 25 million, 2.5 million, or 250 million. Instead I write

=IF(A1=25*10^6, 6*10^5, 8*10^6)

An even better answer is to put those values in cells and refer to the cells. When they’re in cells, I can format them and use commas to count the zeros. But let’s face it, sometimes we hardcode numbers in formulas. And when I do, I’ve been using this method for larger numbers and, after a small adjustment period, it’s been great.

AutoHotkey in the VBE

I downloaded AutoHotkey recently for something not Excel related. Well, it was kind of Excel related so I guess I’ll tell the story. I’ve been using the Save As feature of my Kwik Open add-in and really enjoying the lack of folder navigation. But it has sewn the seeds of discontent. When I’m doing a Save As in other programs, like a PDF viewer, I don’t get the keyboard love. I got AHK so I could quickly jump to some of my favorite folders without leaving the keyboard.

But then I got to thinking about other ways I could use this tool. One of my pet peeves about the Visual Basic Editor (and there are many) is that typing “endif” will auto-expand to “End If”, but typing “endwith” just sits there like an insolent child. I thought maybe I could fix that with AHK. And I could. But that wasn’t enough. Check out these two AHK scripts I wrote.

#IfWinActive ahk_class wndclass_desked_gsk
::with::With{Enter}End With{Enter}{up 2}{end}
:*:then`n::Then{Enter}{Enter}End If{Enter}{up 2}`t

The first line tells it to only work in the VBE window and it comes with a windows spy utility to find out the ahk_class of whatever window you like. The second line monitors for when you type the word “with”. When you do that, it replaces it with With and End With – even better than expanding endwith. Then it moves the cursor back up to the With line so you can continue coding. If you type “with” inside a comment, it’s trouble. So that’s something to work on.

The third line looks for when you type “then” and press Enter. It replaces it with an If block and puts the cursor in the middle, ready for more code.

Watch the video to see it in action. I’m not sure why the video is blurry at the start nor do I know how to fix it.

As you might guess, I’m going to love this.

Excel Function Bible released

Hi all

Together with Norman Harker I add this page to my website :  Excel Function Bible

The Excel Function Bible is aimed at providing meaningful and practical help in finding and implementing the 468 Excel functions. It replaces and adds significantly to the help users can call for whilst building or using Excel workbooks. To make it easier to navigate through all the Excel functions example workbooks(using a nice Ribbon interface) It will also be available as a add-in soon. But for now we (Norman Harker and me) have upload 468 Example workbooks to my site. There is one example file for each worksheet function. This is an evolving project. We would appreciate users providing us with additional examples of typical or extremely useful applications of the functions especially in conjunction with other functions. All examples provided will be appropriately and prominently acknowledged. Similarly, we don’t pretend to be perfect and would appreciate reports of any bugs or errors that may have crept in.

You can download separate function files in the classification pages or download all the 468 workbooks in one time on the index page.

Regards Ron de Bruin

http://www.rondebruin.nl/index.htm