Day() function problem (2000 sp-3/ 98SE/ VB6)

[AlanMiller]

I have cell entries formatted as dates, for instance appaering in the cell as 23-Sep and in the formula bar as 23/09/2005. In my VBA, I'm trying to use statements like:
cbxQuoteDateSentDay.ListIndex = CInt(Day(rng.Cells(1, i).Value)) - 1 or
cbxQuoteDateSentDay.ListIndex = CInt(Day(rng.Cells(1, i).Formula)) - 1

and I keep receiving a type mismatch. The same thing works fine using Month(), with or without the CInt casting. Further, running:
MsgBox Day("3/10/2005")
returns 3 as expected.

I've tried every variation I can think of, using #, ", CDate(), CInt() ... to no avail. OK, what's the secret?

Alan

[HansV]

Your code works fine in a quick test. When you get the error message, choose Debug and inspect rng.Cells(1, i) - is it the cell you intended, and is its value a valid date?

[AlanMiller]

It works much better with an EndIf to close up the IF block. <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

I got into the (IMO) bad habit of using single & multi-lined, unblocked IF constructs with VB (C++ didn't allow them).
This is an example of regretting abandoning stricter/ safer coding practices for a quicker way out!

Alan

[HansV]

<img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>

Even though single line If's are allowed, I always use the If ... End If construct. I have gotten into the habit of typing the End If immediately after typing Then and pressing Enter. Only then I'll start to type the code in between.

I also avoid combining multiple instructions on one line separated by :. It may have made sense in the '80s to make your code as compact as possible, but nowadays, the disadvantages far outweigh the advantages (if any).