Results 1 to 10 of 10

Thread: [RESOLVED] Error: Value of type 'Date' cannot be converted to 'Long'

  1. #1

    Thread Starter
    Banned
    Join Date
    Oct 2015
    Location
    Earth
    Posts
    175

    Resolved [RESOLVED] Error: Value of type 'Date' cannot be converted to 'Long'

    Hi experts,
    I am trying to filter excel cells after 09/01/2015 date.
    I am gettting following error.

    http://prnt.sc/8x8khn

    Please note that the code you see in the link works very well in VBA.

    Any idea will be appreciated!

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Error: Value of type 'Date' cannot be converted to 'Long'

    If you're trying to format the date's string then call the ToString method along with the format:
    Code:
    s.ToString("MMMM dd yyyy")
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3

    Thread Starter
    Banned
    Join Date
    Oct 2015
    Location
    Earth
    Posts
    175

    Re: Error: Value of type 'Date' cannot be converted to 'Long'

    Hi dday9,
    Your advice doesnt work...

    Because dates in the column three are in the date format...
    Any other idea?

  4. #4
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Error: Value of type 'Date' cannot be converted to 'Long'

    What is it that you're trying to do? Because CLng is a conversion function which means that it tries to convert some object to a Long data type.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  5. #5

    Thread Starter
    Banned
    Join Date
    Oct 2015
    Location
    Earth
    Posts
    175

    Re: Error: Value of type 'Date' cannot be converted to 'Long'

    Following VBA code works perfectly in VBA-Excel.
    Code:
    Sub Macro1()
    ActiveSheet.Cells.AutoFilter Field:=3, Criteria1:=">" & CLng(CDate("09/01/2015"))
    End Sub
    Why following vb.net code doesnt work?
    Code:
    With ExcelApp.Workbooks(1).Worksheets(1)
         .Cells.AutoFilter(Field:=3, Criteria1:=">" & CLng(CDate("09/01/2015")))
    End With
    See the vb.net error;
    http://prnt.sc/8xa69w

    P.S.
    The logic for changing Date format to Long format here is Excel-AutoFilter for after date needs number format...
    Last edited by Herry Markowitz; Oct 30th, 2015 at 07:45 PM.

  6. #6
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,578

    Re: Error: Value of type 'Date' cannot be converted to 'Long'

    It doesn't work because VBA and VB .NET have completely different philosophies about how values should be converted from one type to another. I'm starting to think that knowing VBA makes it harder to learn VB .NET in quite the same way that knowing WPF made Silverlight a little harder to grasp.

    In VBA, when you ask the language to convert a string to a Long, it tries a lot of things. First, it asks if the string is just a number. In this case, it's not. So then it asks if the string is a Date, and in this case it is. Dates can be represented by Longs in many ways, so it converts the date to that format via some rules. Interesting question: how does it decide if your date is January 9 or September 1? You have to memorize that to use VBA "well". If it wasn't a date, there's probably a few other things VBA does, including and not limited to just giving up and returning 0 or some other default value.

    VBA did this because they wanted to make it easy for people who weren't programmers to figure out how to do some pretty complicated things. Seasoned programmers know that this kind of "easy" can often translate into "big fat messes" when you make some innocent mistake somewhere but instead of crashing, the language propagates garbage so far down the line you can't figure out where things went wrong. In terms of teachers, it's kind of like Bob Ross: it doesn't believe in mistakes, just happy little accidents.

    You can call VB .NET Professor Tightpants in comparison. Even in its most weakly-typed configurations, it will insist that some types just can't be converted unless you go about them a very special way. Instead of happy accidents, it says, "There is no "try", there's my way and there's FAILURE." This sounds very constricting. But for novice to journeyman programmers, it also forces you to slow down and think a little harder about what your variables are and what you're trying to do with them. When you get really used to it, you start wishing it'd get the heck out of your way, but it's only because you're already not making the mistakes that VBA allows but VB .NET doesn't.

    So. What's going on here?

    VB .NET says a String is a String. You can cast it with CType() to a Char(), because a string is made of many characters and that's what a Char() is.

    It has a little bit of niceness. If you CDbl("10.0") you'll indeed get a Double. That actually surprised me and I've been using VB .NET for 12 years. In general, VB .NET says the "right" way to go from a String to a Something Else is to see if Something Else has a Parse() method. In fact, Double.Parse() gives you a lot more options than CDbl() will, including the ability to make it fail on numbers CDbl() might accept. Sometimes you want that. It's nice when things outright fail instead of limping along.

    But VB .NET draws the line at Dates. Dates are extremely complicated things to parse, and as I pointed out above, it's not even clear if "01/09/2015" is in January or September. So VB .NET insists you use DateTime.Parse() or DateTime.ParseExact() to convert Strings to DateTimes. Even worse, you're asking it to jump through SEVERAL hoops, converting a String to a DateTime to a Long all in one go. Well, you can't convert DateTime to Long directly. Run this and you don't get a happy accident, you get a compilation error:
    Code:
    Dim t = DateTime.Now
    Dim l = CLng(t)
    That's VB .NET's way of rolling its eyes and saying, "Oh, sorry, did I ask you to THINK while you were busy programming?" It's a real jerk sometimes. But then VBA's prone to "Tee hee, you probably didn't realize it but I misinterpreted your date so the number's not even right. Have fun figuring out why September sorts before August!" That's just a different kind of jerk.

    Now, DateTime DOES have a Ticks property that is a Long, and is more or less what VBA would use. But you have to use it yourself if you want to use it. VB .NET is perfectly happy with you doing whatever you want so long as you are explicit.

    Code:
    Function DateToLong(ByVal rawValue As String) As Long
      Dim timestamp As DateTime = DateTime.ParseExact(rawValue, "MM/dd/yyyy", Nothing)
      Return timestamp.Ticks
    End Function
    I'm not sure if that will even work for Excel, but it does a lot of goofy things with data formats, so it could.
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  7. #7
    Frenzied Member
    Join Date
    Jul 2011
    Location
    UK
    Posts
    1,335

    Re: Error: Value of type 'Date' cannot be converted to 'Long'

    Wrong Thread....

    EDIT: Actually right thread. I just cross posted against SS and thought I'd wandered into the wrong thread.

    I agree with SS regarding confusing dates. CDate("9.1.2015") leaves the conversion to the mercy of the user's Regional settings. .NET has a date literal specifier, so use it: #9/1/2015#. The month always comes first, so you know that this date is 1st September.

    The DateTime Structure also has the ToOADate and FromOADate methods for converting to and from OLE Automation dates. ToOADate returns a Double, so you may still need the CLng:
    Code:
    CLng((#9/1/2015#).ToOADate)
    Last edited by Inferrd; Oct 30th, 2015 at 09:03 PM. Reason: Gaaahh!

  8. #8

    Thread Starter
    Banned
    Join Date
    Oct 2015
    Location
    Earth
    Posts
    175

    Re: Error: Value of type 'Date' cannot be converted to 'Long'

    Hi Sitten,
    Thanks for detailed explanation.
    But I am still getting same error here;
    http://prnt.sc/8xatfj


    Quote Originally Posted by Sitten Spynne View Post
    But VB .NET draws the line at Dates. Dates are extremely complicated things to parse, and as I pointed out above, it's not even clear if "01/09/2015" is in January or September.
    By the way it is January.
    Because my system and my country date setting is Day/Month/Year
    Quote Originally Posted by Herry Markowitz View Post
    I am trying to filter excel cells after 09/01/2015 date.
    Last edited by Herry Markowitz; Oct 30th, 2015 at 09:07 PM.

  9. #9
    Frenzied Member
    Join Date
    Jul 2011
    Location
    UK
    Posts
    1,335

    Re: Error: Value of type 'Date' cannot be converted to 'Long'

    I'm having one of those days....

    See post#7

  10. #10

    Thread Starter
    Banned
    Join Date
    Oct 2015
    Location
    Earth
    Posts
    175

    Re: Error: Value of type 'Date' cannot be converted to 'Long'

    Quote Originally Posted by Inferrd View Post
    Code:
    CLng((#9/1/2015#).ToOADate)
    This code solves my problem.
    Thanks Inferrd...

    Thanks Sitten.

    Solved.

Posting Permissions

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



Click Here to Expand Forum to Full Width