THE CELL MASTERS
Chapter 07   The Cell Master.  Andy Pope  from London, England
  AJP Excel Information : www.andypope.info/

interview date:7.Feb.2003
Please introduce your self. You may be as thorough as you wish. Feel free to include or omit any detail about yourself.
''My name is Andy Pope and I'm an Excel addict!''

There I said it, any chance of a quick group hug?

No seriously, I am a 39 year old I.T. guy working for a data processing company in London, England. I have been with the company since 1986, dealing with all the computer related tasks.

I left school with a fistful of qualifications, most of which where mine, and started a engineering apprenticeship. Four years later I had an HNC in Mechanical Engineering. At the end of my apprenticeship I chose to work in the department with the most computers. There I was exposed to Fortran and got the coding bug, pun intended you should have seen some of my earlier efforts!

So, 2 years of hard graft later and I'm still being treated as the tea-boy, time to move on I thought. Well the bright lights of the big city beckoned and I started working at Digitab as a Junior programmer.

Been here ever since. The challenges, like moving everybody from mainframe to PC's ; or learning new coding languages, keeps me busy and therefore happy.

My spare time is spent relaxing, doing chores around the house and playing my son's PS2.

When do you remember using Excel for the very first time? Can you remember any specific details from that first time?
I can't really remember the first time. It certainly wasn't a defining moment. I probably just installed it, tried it and thought great does what it says it would.

When do you remember writing your first formula or VBA code for Excel?
The first major piece of code, in Excel 95, was a complex reporting system written for an external client.

It took their data and allow them to create ad-hoc reports. They could specify the exact content of in terms of data periods and categories. They had control over charts, tables of figures and the crowning glory a set of textual statements derived from the charts and tables.

It was a pretty impressive system, even if I say so myself.

On average, how many hours per day do you spend working with Excel formulas and/or VBA code?
I spend between 1 and 6 hours using Excel during a working day, but it is very dependent on the task(s) I am currently working on.

At home my usage is creeping up. I spend a couple of hours each night working on ideas to add to my site.

Which do you find most rewarding to work with: Formulas or VBA in Excel? Please tell us why?
For me VBA code is more interesting. I enjoying taking Excel into areas no one else would think of going. That said I get great satisfaction from solving a colleagues 'impossible' problem with a few well chosen formulas.

If you were going to give a novice, just starting out with Excel, some advice, what would it be?
Use the help system.

Don't be afraid to ask.

Get into the habit of saving your work regularly. Especially before you run that newly typed bit of experimentally VBA code that trashes your PC. You only do a couple of times before you see the light!

Backup your data.

If you must use passwords don't forget them!

Please provide a sample of your first work (either as a formula or vba code) in Excel and tell us about it.
This is not my very first piece of code. But it is my first reply to a newsgroup question, spelling mistakes included. Its code to center text within the msgbox function.

Option Explicit


'######################################################
'----------------------------------
Sub test_message()

    Dim a

    a = myMsg("This first line is a lot bigger than", "this the second", _
        "But not nearly as big as this line which I'll call the thrid line", _
        "line 4 is small")
End Sub

'----------------------------------
Function myMsg(ParamArray PromptTxt())
'
' Centre prompt on Excel message box
'
' Written by Andy Pope @ Digitab    19-March-1998
'
'
    Dim tmpText As TextBox          ' temporary object used to get true Font sizing
    Dim RealTextWidth As Single     ' size of widest string
    Dim ChopTrailers As Integer     ' start of trailing spaces
    Dim PromptBuf As String         ' new msgbox prompt
    Dim i As Integer

'-
' Create a textbox of worksheet in order to get true length of text
'-
    Set tmpText = Worksheets(1).TextBoxes.Add(1, 1, 1, 1)
'-
' set properties of textbox to mimic normal messagebox appearence
'-
    With tmpText
        .Font.Name = "MS Sans Serif"
        .Font.Size = 8.5
        .VerticalAlignment = xlTop
        .HorizontalAlignment = xlLeft
        .Orientation = xlHorizontal
        .AutoSize = True
    End With
'-
' determine the actual longest string
'-
    For i = LBound(PromptTxt) To UBound(PromptTxt)
        tmpText.Text = CStr(PromptTxt(i))
        If tmpText.Width > RealTextWidth Then RealTextWidth = tmpText.Width
    Next i
'-
' pad out lines
' the period is used to fool the textbox to take trailing spaces
' Chop keeps a record of where the trailing space start
'-
    For i = LBound(PromptTxt) To UBound(PromptTxt)
        ChopTrailers = Len(PromptTxt(i))
        tmpText.Text = CStr(PromptTxt(i)) & "."
        Do While tmpText.Width < RealTextWidth
            tmpText.Text = " " & Left(tmpText.Text, Len(tmpText.Text) - 1) & " ."
            ChopTrailers = ChopTrailers + 1
        Loop
'-
' Build new prompt and append linefeeds
'-
        PromptBuf = PromptBuf & Left(tmpText.Text, ChopTrailers) & Chr(10)
    Next i
'-
' remove trailing line feed from prompt
'-
    PromptBuf = Left(PromptBuf, Len(PromptBuf) - 1)
    MsgBox PromptBuf

'-
' remove textbox from worksheet
'-
    tmpText.Delete

End Function '-----------------------------------
'################################################



What is your mental attitude when you are preparing to write formulae or VBA code? And what is your working environment?
The key when it comes to solving a task, whether with formula or code, is to really understand the problem. I have lost count of the number of times I have been given a project specification written on the back of beer mat. You have to get the user to think about the problem they are trying to lumber you with. It's a painful task but it saves so much time and wasted effort.

My working environment is peaceful but not quite, as I share my office with the companies 3 servers. At home I wait until everyone's in bed before I start tapping away at the keyboard.

If there special preparations that must be in place before you can begin, what are they?
As to project preparation, I always begin with a cup of tea. This gives me time to think the problem through in my head. I then transfer the ideas to paper and begin coding small chunks before combining them into a complete program.

Finally, please give us something to think about - a reminder of your words here; a phrase that has helped you; a link to your own website. Anything that you think is important for the readers to remember.
Just because YOU can't do it, doesn't mean its impossible.

Oh, I did I mention BACKUP those data files.

You can see examples of my efforts on my website,

www.andypope.info

And finally, thank you for taking the time to read through my attempts at humour and wisdom.

Andy Pope

Thank you very much for answering the questions.

This Black belt is yours...

| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
ABOUT
WORKS
THE CELL MASTERS
CONTACT
LINKS
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo