Results 1 to 11 of 11
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    ReDim to change Variable Type (Excel VBA)

    Hi,

    I am aware that the ReDim statement is generally used to redimensionalize array variables. BUT, can it be used for this purpose too???

    Say I don
    Regards,
    Rudi

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ReDim to change Variable Type (Excel VBA)

    I don't think you can. Did you try?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ReDim to change Variable Type (Excel VBA)

    Yes, it failed multiple times. So I was wondering if I was missing something?
    Regards,
    Rudi

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

    Re: ReDim to change Variable Type (Excel VBA)

    If you look up ReDim in the online help, you'll see that it is meant specifically for dynamic arrays, not for "single" variables.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ReDim to change Variable Type (Excel VBA)

    Actually I did read up about it in the help files. Before this question I was not too sure why you needed ReDim in the first place. I think I was just trying to be smart here.
    Tx for the answer...I will scratch this from my ideas list then!
    Cheers
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ReDim to change Variable Type (Excel VBA)

    You can't change a variable type once it has been declared, hence the use of the variant to hold a variety of datatypes. When you need to use methods and properties of a particular type, you will need to declare a variable of that time as well and assign the value specifically to that variable.
    Charlotte

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ReDim to change Variable Type (Excel VBA)

    Thanx Charlotte!

    I guess my question does not speak volumes for my integrity, honour and faithfulness to VBA. Think of it: When a person Dims a variable, in laymans terms you are saying, " Hey VBA, I promise that I will NEVER use any other type in this variable other than what I specify here!!!" No I go and change things and VBA does not like it because I'm compromising my promise to it!!!

    Good for you VBA....uphold your righteousness and honour!!! As for me...forgive me, <img src=/S/please.gif border=0 alt=please width=31 height=23>!!!
    Regards,
    Rudi

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

    Re: ReDim to change Variable Type (Excel VBA)

    If, as Charlotte suggested, you need to pass a specific type to a function say, you can (if appropriate) coerce a copy of that variable into the type required by the function. e.g.
    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black><font color=blue>Dim</font color=blue> oVal
    oVal = ActiveCell.Value
    <font color=blue>If</font color=blue> TypeName(oVal) = "Date" <font color=blue>Then</font color=blue> x = MyFunction(CDate(oVal))
    </font color=black></code></div hiblock>Have a look in Help under "Type Conversion Functions" for further info.

    Alan

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ReDim to change Variable Type (Excel VBA)

    OK...that is clear. I can see the difference here, and it makes sense to be able to do that.
    Tx for the extra info!
    Regards,
    Rudi

  10. #10
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: ReDim to change Variable Type (Excel VBA)

    This is probably not what you're looking to do, but here's an added wrinkle for future reference. If you initially declare a variable as a simple variant -- i.e., Dim varX rather than Dim varX() -- you can ReDim it later as an array of a particular type. And then later, if you want, you can ReDim it again as an array of yet another type. Sample sequence:
    <pre> Dim varX
    ReDim varX(1 To 3) As Long
    Debug.Print TypeName(varX(2))
    varX(1) = 1234
    Debug.Print varX(1)
    ReDim varX(1 To 3) As String
    Debug.Print TypeName(varX(2))
    varX(1) = "Microsoft"
    Debug.Print varX(1)</pre>

    I'm not a hardcore programmer-type and am proceeding to wade in over my head here, but I believe a simple variant that's been retyped as an array of a particular type is actually not technically the same (in terms of, I believe, the memory reserved for it, for at least one thing) as a variable that is initially declared as an array of that type, but depending on your purposes, it may effectively work the same for you.

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ReDim to change Variable Type (Excel VBA)

    Thx for the input...all advice and guidance is welcome!
    Sorry for late reply! (Not been around on the boards lately!)
    Cheers
    Regards,
    Rudi

Posting Permissions

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