Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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?

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    <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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •