+ Reply to Thread
Results 1 to 6 of 6

Variable doesn't work based on type

  1. #1
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    682

    Variable doesn't work based on type

    I have a problem with the variable NewNum
    NewNum is a number

    with
    Dim NewNum As VARIANT the MsgBox returns Zero for the count
    but the StrPtr(NewNum) & Len(NewNum) work correctly

    with
    Dim NewNum As LONG the MsgBox returns the count correctly
    but the StrPtr(NewNum) & Len(NewNum) fail

    with
    eliminating the defining of the variable
    MsgBox returns Zero for the count then pops up two more messages boxes containing the number 4 (there are 4 blank cells in my data - coincidence?)
    again, the StrPtr(NewNum) & Len(NewNum) work correctly


    Please Login or Register  to view this content.
    Last edited by carsto; 07-10-2007 at 05:52 PM. Reason: put the attachment on the wrong post

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    Try this

    Please Login or Register  to view this content.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,206
    I think it's all in the what data type VBA recognizes for newnum.

    According to VBA help file, the InputBox function returns a string. So when newnum is a variant, VBA recognizes it as a string. As we so often see with Excel, the string "2" is not equal to the number 2. VBA tries to be "smart" about these comparisons, but doesn't always succeed. My advice would be to use type conversion functions (Val, Str, etc.) or other to coerce comparisons to the correct data type (I believe royUK's code does this).

  4. #4
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    682
    Roy,
    What I'm eventually doing is deleting all the lines that don't contain the NewNum in Column A.

    So I've attached a sample spreadsheet with all my code in one module & yours in another.
    The NewNum is 9999

    My code works fine unless they fail to put an entry into the input box.
    Your code still doesn't recognize the input number and count them up.

    & Thanks for the tip on using rcell not cell

    MrShorty,

    I see what you mean about the InputBox returning a string, however the solution containing "type conversion functions" is beyond me.
    Last edited by carsto; 07-11-2007 at 09:33 AM.

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,206
    The type conversion functions simply allow you to convert from one data type to another. So for example:
    Please Login or Register  to view this content.
    puts the string returned from inputbox into a string variable. You can then test this variable to decide if a valid input has been entered. If nothing was entered, newstr will contain an empty string ("").
    then:
    Please Login or Register  to view this content.
    You can get specifics about type conversion functions by putting "type conversion functions", "Val function", "Str function" into VBA help's search feature.

  6. #6
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    682
    Thanks MrShorty,

    Works like a charm & I like is better than the If StrPtr & Len I always use.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1