Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts

    Question about a Dim statement

    I was just tidying up some old VBA, putting Dim statements at the beginning of a module, and I came across an odd problem.

    I've a variable, NewPrice, which first appears in a line of VBA: NewPrice = Fund.Offset(0, -1).Value where Fund is Dimensioned as Range (in which each cell one column to the left contains a number in currency format).

    On execution this line throws up a Type mismatch however I Dimension NewPrice - although it runs perfectly well if I don't Dimension Newprice at all (or Dimension it as Variant, which is the same thing) !

    I've tried Currency, Single and Double so far - what am I missing, or how could I work out what VBA is expecting it to be ?

    Thanks
    Last edited by MartinM; 2013-06-14 at 11:19.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    The plot thickens.

    I've written a bit of code to tell me what Type the VB environment thinks a variable is.

    Without the Dim statement, before the offending line of VB it reports that NewPrice's Type is Empty and Fund.Offset(0, -1).Value is Double

    After NewPrice = Fund.Offset(0, -1).Value it reports that NewPrice's Type is Double and Fund.Offset(0, -1).Value is Double

    That is exactly what I expected.

    If I now insert Dim NewPrice As Double at the beginning of the Module it reports that NewPrice's Type is Double and Fund.Offset(0, -1).Value is Double - but then I get the Type mismatch error.

    Can't figure out why.

    PS Fund itself is Dimensioned as Range. The code works if it is Dimensioned as Object, but I still get the Type mismatch error as above.
    Last edited by MartinM; 2013-06-14 at 14:03.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Sorted !

    Fund was a range of cells I was cycling through.

    One of the cells in the column to the left of Fund had a corrupted value, not numeric (or anything else as far as I could see).

    All corrected now - and thanks for reading this thread if you got this far !

Posting Permissions

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