Bug in Excel

I created a two column csv file using Excel:

Norway,no
Denmark,dk
Sweden,se
Germany,de
Norway,no
Denmark,dk
Sweden,se
Germany,de
Norway,no
Denmark,dk
Sweden,se
Germany,de
Norway,no
Denmark,
Sweden,
Germany,
Norway
Denmark
Sweden
Germany

If you look carefully, you'll notice that there is a comma missing in the last four rows. This means that it does not have two columns and the file is useless.

The workaround is to swap the two columns:

no,Norway
dk,Denmark
se,Sweden
de,Germany
no,Norway
dk,Denmark
se,Sweden
de,Germany
no,Norway
dk,Denmark
se,Sweden
de,Germany
no,Norway
,Denmark
,Sweden
,Germany
,Norway
,Denmark
,Sweden
,Germany

The bug is described here.

Err and get over it

If you want to delete a table and say things like:

drop table x

you'll get an error message if the table x doesn't exist.

To check if it exists may require to run a query and see if you get any rows returned. Far easier is it to receive the error with open arms and move on:

Welcome to jansprograms

This will be a blog where I write things I find neat or interesting in my programming adventures.

I will not post every day. Just when I feel like it.

You are very welcome to post comments.

Exiting a website

To exit a program you click File - Exit. That is common practice.

Today I stumbled upon a site that saves the url of the site you came from and offers an Exit link at its menu so you can return to it, regardless of how many webpages you have been visiting on the site.

A normal Back button will not work of course since it sends you only page to the previous page.

How it works? Don't know, but maybe the url you are coming from is saved in a table at entry.

Here is the site.

(Via The Home for Wayward Statisticians)

Duel Calculator

Over at my blog think again! the duel of Bad Guy has been studied recently.

Below you may download the Duel Calculator written in MS Excel. You have to enable macros to run it. It has no virus. I know since I wrote it.

1 Attachment

Processing - yet another free language

'Processing is a programming language and environment for people who want to program images, animation, and sound. It is used by students, artists, designers, architects, researchers, and hobbyists for learning, prototyping, and production. It is created to teach fundamentals of computer programming within a visual context and to serve as a software sketchbook and professional production tool. Processing is developed by artists and designers as an open-source alternative to commercial software tools in the same domain.'

The beta software for Processing 1.0 was released 20 April 2005. More

(Via paulofierro.com)

Interactive SQL tutorial

I have tested myself with 'a gentle introduction to SQL'. Questions are asked and you have to come up with the SQL that gives the right answer. You SQL is run on a database somewhere in real time.

You can practice SQL Server, Oracle, MySQL, DB2, Mimer SQL, PostgreSQL and Access.

An example:

Question:

A "good value" album is one where the price per track is less than 50 pence. Find the good value album - show the title, the price and the number of tracks.

Answer: (MySQL)

select album.title, album.price, count(song)
from album
JOIN track ON (asin=album)
group by asin
having price/count(song) < .50

Highly recommended. Both to learn SQL and for its pedagogical approach. The theory is available to those who needs/wants it by clicking a link on the question page. No correct SQL's are given, but the right records and fields for each question may be displayed to help one to come on the right track.

(Via J-Walk)

Excel is good for some things

The elections in the Cayman Islands took place yesterday. I analysed the results today with Excel and made two discoveries.

1. A candidate who got more than 32.7% of the registered voters' vote was elected to the Legislative Assembly regardless of district.

2. A candidate who got more than 42% of the votes cast was elected to the Legislative Assembly regardless of district.

The big winner, in my mind, was Anthony Eden. 75% of those who voted in Bodden Town voted for him.

Look at the Excel file below for details. Maybe you discover other patterns.

1 Attachment

Testing my Access skills

Today I went to the second job interview for a post that requires query skills in Access.

I did not know what to expect, so I practised writing queries as sql statements, instead of using Access' query generator.

In FoxPro 2.6 Developer's Guide I found 24 query questions with solutions. Already the second question stumped my Access skills:

'Determine how many states have at least one customer residing in them.'

The FoxPro solution is:
SELECT Count(distinct state) FROM CUSTOMER,
but that does not flush with Access.

I didn't find a solution before the interview, but I should not have worried. I was given three queries to do and the last one gave me some problems as I haven't done too many crosstab queries in my life. I got the columns and rows right looking, but the numbers were not correct.

'We are looking at the way you approach problem solving,' was the interviewer's comment, 'not that you get everything right.'

Personally I would hire me, not for what I know, but for my hunger and ability to get to know what I don't know.

We'll see if this firm sees it the same way. The verdict falls in a week's time.

---

When I find an Access solution to the problem mentioned in the beginning, I will post it here as a comment. If you beat me to it, shame on me!

Copying files

One way to backup files is simply to copy them to a new folder. If the destination folder starts with the date and time, like 2005-05-16 09-34-56 AM, even better.

Yesterday, I wrote such a program in Access. Several copy jobs can be defined, edited, and deleted.



This is what it looks like afterwards in Windows Explorer:

Javascript fun

Today I had a real nice experience with javascript. I wanted to write some code, had half an idea on how it could be done, and found, thanks to, as always, Google, the other part.

In my blog think again! I wanted to show 19 questions and answers in the game Twenty Questions. It would spoil the fun to display all 19 questions at once, so I started to make them invisible:

1. It is classified as Mineral.

Everything in the div block "news1" will not be shown thanks to its style.

To display it at a click of a button, I need, suprise! surprise!, a button:

When the button is clicked, it calls the function jfx() with the value "true".

This is the function:

function jfx(status){
if (status)
{document.getElementById("news1").style.display = "block"};
}


To avoid using nineteen buttons, I used a variable:


var counter=1;

function jfx(status){
var b = "news"+counter;
if (status)
{document.getElementById(b).style.display = "block"};
if (counter == 19)
{document.getElementById("button").style.display = "none"};
counter++;
}


By putting the button itself in a div block, I can hide it when the 19th question have been shown.

To see the code in action, click here.

Greasemonkey

It is easy to be stupid. For instance by not thinking of the simple things.

I can create a web page with html code people can see. People can create a web page with html for me to see.

The simple thing I never thought of is that I can filter what people send me. I can write code that does something to the html page I receive before it is being displayed on my monitor.

J-Walk enlightened me today: 'Greasemonkey could be the best thing to ever happen to the Web.'

He gives some links for learning more. And, a few hours later he came up with a 'useless' script. :)

I liked this quote:

"Greasemonkey is the new Excel: an inefficient way for end users to do anything. That's a compliment, BTW." - Mark Pilgrim

Ah, by the way. Greasemonkey is for Firefox browsers only. Which shouldn't be a problem, as it is the one I use (read: the best browser out there).

Yes, you guessed it. My mind is warming up its cylinders to come up with a problem the Greasemonkey may be a solution to. The wrong way around, I know, but what can you do?

Eat your own dog food

One thing has puzzled me for some time. Why does Joel on Software have more than ten thousand subscribers in Bloglines?

Today I found the answer. From time to time he writes interesting article on programming. Here is an excerpt from something he wrote more than four years ago:

'Eating your own dog food is the quaint name that we in the computer industry give to the process of actually using your own product. I had forgotten how well it worked, until a month ago, I took home a build of CityDesk (thinking it was about 3 weeks from shipping) and tried to build a site with it.

Phew! There were a few bugs that literally made it impossible for me to proceed, so I had to fix those before I could even continue. All the testing we did, meticulously pulling down every menu and seeing if it worked right, didn't uncover the showstoppers that made it impossible to do what the product was intended to allow. Trying to use the product, as a customer would, found these showstoppers in a minute.'

To use your application as a customer is a good advice. I developed sharealink more than a year ago and use it every day. By using it I found some irritating features that have been ironed out, and a list of improvements I haven't had time for yet.

Who is Ruby?

There seems to be several nice, free programming languages out there that I should try. In the right column I will maintain a list of them. First one out is Ruby.

'Ruby is the interpreted scripting language for quick and easy object-oriented programming. It has many features to process text files and to do system management tasks (as in Perl). It is simple, straight-forward, extensible, and portable.

Oh, I need to mention, it's totally free, which means not only free of charge, but also freedom to use, copy, modify, and distribute it.'

Fighting hard coding

Hard coding is not a good thing. Sooner or later the name of the company, the number of tutor groups, the web address of the program's support page, etc, will change.

One solution is to put the information in an ini file. Many people scorn ini files, I don't.

Another solution is to put the information in a table. My table has two fields: cname and cvalue.



select cname, cvalue from (ctable) into array a_temp

CLOSE databases

for i = 1 to _tally
cdatatype = left(a_temp(i,1), 1) && c, l or n
do case
case cdatatype = 'c'
this.addproperty(alltrim(a_temp(i,1)), alltrim(a_temp(i,2)))
case cdatatype = 'l'
this.addproperty(alltrim(a_temp(i,1)), iif(ALLTRIM(lower(a_temp(i,2))) == '.t.', .t., .f.))
case cdatatype = 'n'
this.addproperty(alltrim(a_temp(i,1)), val(a_temp(i,2)))
case otherwise
MESSAGEBOX("Corrupt " + ctable + ". The program will end.", vbokonly + vbinformation, "Error")
RETURN .f.
ENDCASE

ENDFOR

The first character in cname stores the data type. The code is written in Visual FoxPro 8. Each piece of data is stored as a property in an object I have open as long as the program is running, making them behave like global variables.

I think this is a clever way of dealing with data of this kind. Even better, I think it is my own invention! :) At least, I can't remember I read about it somewhere.

Haskell - another language

I discovered Haskell the other day.

It has its admirers:

'WOW! I basically wrote this without testing just thinking about my program in terms of transformations between types. I wrote the test/example code and had almost no implementation errors in the code! The compiler/type-system is really really good at preventing you from making coding mistakes! I've never in my life had a block of code this big work on the first try. I am WAY impressed.'

I'll check it out when I find the time.

Adding time

Many businesses keep track on how many hours and minutes their employees work using Excel.

How much is 5h 49 m + 55m (h = hours, m = minutes)?

If you add 5.49 and 0.55 you get 6.04, which is wrong.

However, if you custom format the cells to [h]:mm and write 5:49 in one cell, and 0:55 in another, you get the correct answer 6:04. The [h] is used so the time is not added modulo 24. 20:50 + 10:04 = 06:54 if h:mm is used, but equals the correct 30:54 with [h]:mm.

If you are lazy, as I proudly am, you don't want to type 05:49 to enter the time. The 0 shouldn't be needed and the : neither. The answer is to use the custom format 0":"0#. Now, if you type 5, 0:05 appears. Likewise, 12 gives 0:12, 549 gives 05:49, 1545 gives 15:45.

Here comes the bad news. You can't give a cell two custom formats at the same time. In this case you have to pick between [h]:mm and 0":"0#. If you choose the first the times add up, but you can't be lazy. If you use the latter the times do not add up, but you can be as lazy as hell.

Allen Wyatt suggests a way out.

'... use another column to show the entered digits converted into a time. All you need to do is use a formula to do the conversions. For instance, if the time you entered was in cell A3, you could use the following formula in a different cell to do the conversion: =(INT(A3/100)/24)+((A3 - (INT(A3/100)*100))/1440)'

I would type the times lazily with the format 0":"0# and have it automatically copied to a cell formatted with [h]:mm format I would use for adding.

If I have hundreds of cells with times to be added, the solution is a bit laborious, so I looked for something else. This was my idea: Format all cells with the general format. When a time is entered lazily, like 549, let code convert it to 05:49 and format the cell to [h]:mm. This is my code:

Dim nrow As Integer
Dim ncol As Integer

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If nrow <> 0 And ncol <> 0 Then
 If InStr(Application.Cells(nrow, ncol), ":") = 0 Then
   Application.Cells(nrow, ncol) = ftime(Application.Cells(nrow, ncol))
   Selection.NumberFormat = "[h]:mm"
 End If
End If
nrow = Application.ActiveCell.Row
ncol = Application.ActiveCell.Column
End Sub

Private Function ftime(t As String) As String
' example: '5' -> '00:05'
' example: '12' -> '00:12'
' example: '540' -> '05:40'
' example: '1123' -> '11:23'
t = Right("000" & t, 4)
ftime = Left(t, 2) + ":" + Right(t, 2)

End FunctionIt works well as long as one stays inside the cells where time is added. To avoid problems for other cells one might test if the active cell is in the range of time cells or not.

I will post this problem on a user group and see what people have to say.

Subform in php

The last two weeks I have translated a small Access database for a music association to the Internet using php and mysql.

One new challenge was how to get a subform over. It looks like this in Access:



This is what I came up with in the browser:


Three queries was needed. One to display the fee records for each year, another to display the years in the combo for the chosen member, and a third to give the content of the fields for the chosen year.

I liked the solution.

Mail merge with MS Publisher

I never planned to ever mail merge with MS Publisher, but since the template for the students' report card was made in Publisher I more or less had to give it a try.

I put the data in MS Excel.

The only interesting problem was how to achieve these check marks:

I came up with this:

The formula checks if the rightmost character of the column titles (A4, A3, ...) equals the number in K2 (4, 3, 2, or 1). If it is, a ü is printed, which in font Wingdings is a check mark.

In MS Publisher I had the fields A4, A3, ..., and they were empty or not depending on the value of K2. So all I had to do for each student was to put a number from 4 to 1 in K2 for the class work effort. Homework effort was put in L2, etc.

Even more exciting

This year I have written some exciting php programs that manipulate a database. The programs are copied to my server in Hong Kong where my database sits as well.

Today, I did something even more exciting. I wrote a Visual Basic program that runs on my computer and accesses my database in Hong Kong. To achieve it all I had to do was to enable the database for external access in its control panel, download the MyODBC driver, and use this connect instruction in Visual Basic:

conn.Open "DSN=Hong Kong;server=simpler-solutions.net;uid=???;pwd=???;database=janbase"

I replaced the ??? with my username and password.

To improve my economy slightly my next project is to access my bank's database.

Javascript is my new hero

Javascripts run on the client's machine and not on the server. It means that it runs very fast. Therefore, if possible, when there is a choice between coding php that will have to be executed on the server, or javascript, the answer is javascript.

An example. In my program sharealink the link may have a note describing the link and one or more comments from people who has visited the link. Sometimes it is nice to turn the notes and comments off.

I knew hov to turn one div block on and off, but not a whole bunch of them. Paulo Fierro, came to my rescue. By using

document.getElementsByTagName("div")

he created a loop that turn off or on all divs that started with 'note' and 'comment'. To see the code click the sharealink link above and view the page source. To see the code in action, just click the links Toggle notes and Toggle comments

My Internet bank uses a lot of javascript to make visits to my account a more pleasant one. The nice thing is that the code is right there under my nose. I choose view source and learn a lot. :)

Can I flash?

The Norwegian chess championship finished yesterday. The 14 year old Grand Master Magnus Ø. Carlsen has to play an extra game against his teacher Simen Agdestein, another Grand Master, to decide who gets the title.

Some of the games can be played, move for move, thanks to a Flash application. I don't know for how long the link will work, so here is a picture, and here is a link to the company.



The question is, of course, why can't I write this kind of software. Why shouldn't I find time to flash a bit. I have even been told that Flash and databases are friends.

The hard way, because it is easier

'lo primero que tenes que pensar es en no usar esos beneficios porque lo único que te traen son dolores de cabeza'

I just read it in a Google Group, and I share the sentiment:

'the first thing you have to decide is not to use these advantages because the only thing they give you is a headache'

Should I use a dbcombo or a standard combo in Visual Basic? Should I use a wizard to create a form in Access or should I do it manually? Should I use Excel for my database or should I use a database program?

If you only want to make one step, the easy way is fine. But, if you want to take two the harder way is easier. The funny thing is that people who only thought they would take one step, suddenly realises they need two so they have to go back to square one, and maybe unlearn a few things on the way.

My first javascript program

A school yard blog presented an intriguing prisoner problem the other day I briefly mentioned in my think again! blog.

I found a solution, that later turned out to have a flaw, and wanted to illustrate the solution in a javascript program you can play with here.

The program has a few flaws on its own, but they will be ironed out in the next few days as I learn more.

To write the code I used the examples at w3school for inspiration. The coding took me a few hours. These syntactical details hampered me the most:

- I did not know how to write a for loop. I used comma instead of semi colon and got the order of the three elements in the beginning wrong.

- It took me some time to realise that an array likes [] better than ().

- I discovered that the elements of an array is not initialised to 0 when it is declared.

- I had some problems with lining up the { and } in a long if then else endif statement. The error message complained about an instruction far later than where the error was.

- I had some trouble remembering when to use == and when to use =.

- I tried to run an empty do while loop. It didn't like that. When I added nothing, i.e. { }, everybody was happy.

- To pick a random prisoner I found this at Google, Math.floor(Math.random() * prisoners).

- I still do not know how to keep the textbox and the button when clicking Start.

- I still don't know why the code doesn't stop to run.

- In Visual Basic loops often have the instruction doevents to allow the cpu doing other thing than the program so the machine won't freeze. This does not seem to exist in javascript. Some sites talked about settimeout, but I failed to understand it. The problem was that when I used a 'do while (solution not found)' the program made Mozilla freeze. I had to settle for a for loop.

- if ((beenthere[leader]) && (switchis == 'down')) seemed to behave better than if (beenthere[leader] && switchis == 'down')

Conclusion: great to learn a language by solving an intriguing puzzle and looking at examples and not descriptions for the right syntax.

Here is a sample run:

---

start of game

there are 5 prisoners, (prisoner# 0 to prisoner# 4)
switch is unknown

0 up
the leader was sent to the cell for the first time
count = 1 (added the leader, prisoner# 0)

4 down 3 down 2 down 1 down 4 down 1 down
count = 2 (added prisoner #4)

0 up 0 up 1 down 4 down
count = 3 (added prisoner #1)

0 up 0 up 2 down 1 down 1 down 1 down 1 down 3 down 2 down 3 down
count = 4 (added prisoner #2)

0 up 4 up 3 down
count = 5 (added prisoner #3)

release the prisoners!
25 moves to the cell

---

If you read the problem and comments at a school yeard blog you may understand what all this means. :)

Sensitive table

About ten years ago I wrote an Excel spreadsheet that today needed some maintenance. The request was simple, 'add space for ten more teachers.' The hard part was to remember how on earth I had created the spreadsheet.

One hour of research revealed that I had used sensitive tables, something I have not used since.

The first column contains the teachers' ids and the second column how many lessons they have been assigned during a week. There are eight periods per day and the other columns show if they are teaching (1) or not (blank).

The cell with # in it contains this formula:
=COUNTIF(TIMETABLE,DUMMY)
where TIMETABLE is a range on another sheet that contains amongst other things all the teacher ids for every period of the day during a week while DUMMY is the name of a cell in the first column below all the teacher ids.

The cell with 1 in it for Monday contains:
=COUNTIF(MON1,DUMMY)
where MON1 is a column on another sheet listing the teachers who teach first period on Monday.

Now we are ready for the exciting part! The range from the blank cell above AE down to the row with the last teacher and the column with period 8 on Friday I created to be a sensitive table by choosing Data - Table on the menu and A93 (the address of DUMMY) for Column Input Cell (Row Input Cell I left empty).

When I click on any of the cell the formula displayed is
{=TABLE(,A93)}.

I found this solution rather elegant, although it was hard to gather how I had done everything ten years ago. Excel's help on this is meager and I hadn't taken any notes. Which is why I jot this down in case I want to use sensitive tables again. Here is a web page that helped me a bit.

How stupid I am

Tab is the preferred key used to move from one object to the next in a form. However, some users may prefer to use the Enter key instead. To make that possible in a Visual Basic form I made this plan:

Private Sub Form_Load()

Me.KeyPreview = True ' so the form receives keystrokes before the objects

End Sub

Private Sub Form_KeyPress(KeyAscii As Integer)

If KeyAscii = 13 Then

  ' find out which object has the focus

  ' find the tab index to the object

  ' increase the tab index by 1

  ' find the object with that tab index

  ' give the object focus

  KeyAscii = 0       ' Ignore this key.

End If

End Sub

A quick Google Group search made me kick myself behind. The five commented lines above can be replaced by one single instruction:

SendKeys "{tab}"   ' Set focus to next control.

Translated: let the Enter key pretend it is a Tab key.

Learning by doing

I use apache server on my computer to test my php mysql files before I upload them to my Internet server.

Till today I had to put all my projects in the same folder as I didn't know how the server would find them otherwise. Obviously, it is easier to keep each project in its own folder, and on the usb stick and not on the hard disk in case it dies on me.

A question on a Google group taught me how to do it.

Here is my summary:

1. I opened the file C:\WINDOWS\system32\drivers\etc\hosts in a text editor and changed the only instruction from

127.0.0.1 localhost
to
127.0.0.1 localhost calendar

2. I opened the file ...\xampp\apache\conf\httpd.conf in a text editor and added these instructions.

NameVirtualHost *:80

DocumentRoot "E:\programming\php programs\dx"
ServerName calendar

3. I copied all the files in my dx project to the folder
E:\programming\php programs\dx.

4. I restarted apache.

5. I typed this url in my web browser: http://calendar/dx_login.php.
Vuaw! The file was found and did its job as when it was in its original folder. In short: it worked!

Fast data entry

Let's say you have to add codes like this all day:

8 - 5 k

8-5 d

5-cl p

12-10 s

8 - 5 k means that the employee worked from 8 to 5 in the kitchen, etc.

Probably you would find it tedious and mistakes are bound to happen.

Imagine you could type in 1, 2, 3, 4 instead. When you type 1, 8 - 5 k appears, when you type 2, 8-5 d, etc. That would save time and improve accurracy.

I created a worksheet called "tabla" with a range called tabla, item, valor, and max.

The range tabla has two columns. First columns is for the codes 1, 2, 3, ... and the second column is for the values 8 - 5 k, etc.

Item is a single cell with no formulae.

Valor is a single cell with the formulae =VLOOKUP(item;tabla;2;FALSE).

Max is a single cell with the formulae =MAX(A2:A16).

This is my code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name <> "tabla" Then
If Target.Value > 0 And Target.Value <= Range("max") Then
Range("Item") = Target.Value
Cells(Target.Row, Target.Column) = Range("valor")
End If
End If
End Sub

Every time you type 1, 2, 3, ... it is replaced by the corresponding code.

Note that the user does not have to change the code. All he has to do is to change the values in the range tabla.

I am sure this can be done in many other ways, but I found this one neat.

My major problem in writing the code was to realise that I had to test if the sheet name was "tabla".

Fast data entry - Part 2

One thing I really like is to revisit a program I have written in order to improve it. Improve it so it runs better or so it is easier to maintain.

In Excel if you want 'police' to appear when you type 'pl' all you need is a table that pairs the two up. In some languages, like php, you can have arrays A where A('pl') = 'police', but I don't think Excel allows it.

For the moment I will settle with numeric codes 1 to 100, so A(1) = 'police'.

Here is the code for my new version of 'Fast data entry' from the other day.

---

Option Explicit
Const Max As Integer = 100
Dim A(Max) As String

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim I As Integer
If Sh.Name <> "tabla" Then
For I = 1 To Max
A(I) = Worksheets("tabla").Cells(I, 1)
Next
End If
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name <> "tabla" Then
If IsNumeric(Target.Value) Then
If Target.Value > 0 And Target.Value <= Max Then
If A(Target.Value) <> "" Then
Cells(Target.Row, Target.Column) = A(Target.Value)
End If
End If
End If
End If
End Sub

---



This is the substitution or code table the user will edit. As soon as the user goes to a new sheet, the values are read into a public array.

Why is this version better than the previous one? It uses no range names the user may inadvertently erase. Apart from that I don't think it is much better than the previous one. Actually it is a bit worse in a sense since the previous version allowed for 'pl' as code for 'police' while this one requires a number.

Anyway, it gave me some practice using Excel.

One thing I learnt was that if you write 'isnumber' it is changed to 'IsNumber' making you believe that it is a part of the language. When you select it and click F1 you are told that Help is not installed. When you select 'IsNumeric' and click F1, Help opens. Definitely a bug. In Excel.

Multi column combo box in Visual Basic

In MS Access a combobox can have several columns. Typically two columns are used, one for the id (which is hidden), and one for a descriptive field (which is shown).

Example: Show the name of the employee, but hide his id.

Strangely enough, in MS Visual Basic a combo box can't have more than one column.

One workaround is to add the descriptive field and the id into one long string, padding the descriptive field so it is say 200 characters long, making the id out os sight, but easily available using a substr() function.

Another way is to create your own multi column combo box. Patrick van Olderen did so. I just downloaded his version 11 of the control from 2002. It comes with an example test program in .exe form and also as vb project so you can study how the control can be used with code.

To register the ocx was a swift operation. Windows + R followed by:

regsvr32 "E:\programming\visual basic\ocx\ComboBoxLB.ocx"

(By the way, E is my 40GB iPod that since yesterday has started to work for me as my data storage, slightly out competing my 1GB Usb stick that was full.)

As always, Google groups, helped me to find the ocx and learn how to register it. Now, of course, all tips and tricks have been pasted into my treepad file on Visual Basic.

Programming is the easy part

"To be a good programmer is difficult and noble. The hardest part of making real a collective vision of a software project is dealing with one's coworkers and customers. Writing computer programs is important and takes great intelligence and skill. But it is really child's play compared to everything else that a good programmer must do to make a software system that succeeds for both the customer and myriad colleagues for whom she is partially responsible. In this essay I attempt to summarize as concisely as possible those things that I wish someone had explained to me when I was twenty-one." - Robert L Read

The essay.

How to stay in a combo box after pressing the Enter key

I have a combo box called cmbrefnumber. The user will type a refnumber, or part of it, press Enter, and data for that refnumber will be displayed. Since the user most probably would like to enter another refnumber I would like the combo box to have focus after Enter is pressed.

This is how I achieved it:

Private Sub cmbrefnumber_AfterUpdate()
...
gbcmbrefnumberafterupdate = True
End Sub

Private Sub cmbrefnumber_Exit(Cancel As Integer)
If gbcmbrefnumberafterupdate Then
gbcmbrefnumberafterupdate = False
Cancel = True
cmbrefnumber.SelStart = 0 ' selects the refnumber
cmbrefnumber.SelLength = Len(cmbrefnumber.Text)
End If
End Sub

gbcmbrefnumberafterupdate is a global variable that remembers if the user has typed a new refnumber. If he has, the exit will be cancelled and gbcmbrefnumberafterupdate is set to false such that if no changes are made exit is permitted and the next control in the tab order gets the focus.

I found the answer here.

I don't know, so let me answer that question!

I have done some programming in Excel, but I have never used userforms. Actually, I thought they were dead.

Today, I got an email from a friend re userforms. His problem was how the content of one combo box can change when the user makes a pick in another combo box.

A few Google searches not only solved my friend's problem, but it also taught me quite a bit about userforms.

RodMcKuen once told his audience that he was about to publish a new book of poems. Shortly, he got many requests for the book. The problem? He hadn't written the book yet. 'So, every night I went home and wrote poems for the book.'

How to add code to a page that doesn't allow code

I live in West Bay which is thirty minutes from the tennis club in South Sound. How do I know if the courts are dry before I set out on my journey to the club? I could call, but that costs cell phone money and disrupts the club manager.

Another solution is to have a web page the manager updates displaying info on the courts, any details, and the time it was updated.

The control panel for the weather page is here. Only the club manager knows the password.

The weather page members can check out is here, or they can look in the upper-right corner of the club's website.



How does the box change from 'The courts are dry!' to 'The courts are wet!' when the blog host doesn't allow any programming?

The box is an image sitting on my server. I change the image when the club manager changes the weather report in the control panel. The control panel also sits on my server and allows, of course, programming.

Array formula saves the day

Three employees are sick (S) or have vacation (V) on various days in August and September.

I would like a formula to calculate the total number of sick days and vacation days for each employee for the range B4:C6.

I visited Mr Excel here and visited pages with countif in them. This page held the answer.

In B4 I typed the formula =SUM((people=$A4)*(DATA=B$3)) and pressed Ctrl+Shift+Enter as it is an array formula.

people is the range A7:A18 and DATA is B7:I18. I copied the formula to the other cells in B4:C6 and that was it!

Moral: you don't have to know a lot if you know a little and Google is not far away.