+ Reply to Thread
Page 4 of 5 FirstFirst ... 2 3 4 5 LastLast
Results 46 to 60 of 67

Range Dimensioning, Range and Value Referencing and Referring to Arrays

  1. #46
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,101

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    P.s. I think your Dim temp As Range in your Public Function is doing nothing?
    Yep, missed that
    I am almost afraid to ask… Is Property Set rather than Property Let used in your last example, as this is simply the syntax for returning an Object rather than a Property
    A property can be an Object, so yes, since we're passing in an Object, it's set

  2. #47
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Thanks Kyle





    .....

    Quote Originally Posted by Kyle123 View Post
    Flog a dead horse
    OK. I'll go for that
    Last edited by Doc.AElstein; 03-09-2015 at 02:26 PM.
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  3. #48
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Dimensioning of variable for Array returned by VBA Filter method.


    Hi,
    . In preparing a code for answering another Thread a minor Dimensioning problem gave a problem. I found the solution, but I would like to understand what is going on..

    . In brief:
    . If arrFiltValues is my variable in which an Array comes using the VBA.Filter applied to another Array, then…
    This works
    Dim arrFiltValues As Variant
    This don’t
    Dim arrFiltValues() As Variant ( I get Error 13 ( Type incompatible))

    . That bugs my code ( and me ) especially after the following I did to try and see what was going on:

    . To elaborate:
    . consider a Simple spreadsheet of silly names, listed Horizontally and vertically in the first row and first column respectively

    Using Excel 2007
    -
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    Silly Name F-Tang F-Tang OLE Wigy Wam Biscuit Barrel Du Wolly-Wop
    2
    F-Tang
    3
    F-Tang
    4
    OLE
    5
    Wigy
    6
    Wam
    7
    Biscuit
    8
    Barrel
    9
    Du
    10
    Wolly-Wop
    ASheetToKeep


    . In the first section .1) ( Lines up to 50 ) in the following code we “Capture” these ranges in two almost identical ways into 4 Arrays. This “Capture” method was discussed in detail in this Thread. These Arrays become “2 Dimensional Arrays of 1 column or 1 row , discussed for example here
    http://www.excelforum.com/excel-new-...1-2-3-4-a.html
    . I know that There are good reasons to Dimension all these Arrays as variant - this also was discussed in those Threads.
    . . By setting a watch in the Watch windows of all variables and stepping through the code with F8 one sees that the Arrays are of similar form , differing only if they are “vertical” or “Horizontal”
    This is regardless of if we do this
    Dim Array() as Variant
    . Or this
    Dim Array as Variant

    Code:

    Please Login or Register  to view this content.
    .


    . In section 2) , lines 60 to 90 ( excluding 70 ) we use various methods to change these Arrays to the “Psuedo horizontal” 1 dimensional form (1) ( 2) (3) (4) etc. which is Syntaxly required for the first argument of the VBA Filter Function. (Line 70 does not work supporting my debatable theory that the VBA 1 dimensional Array is “pseudo” “Horizontal” ?!?! )

    . Later in the program I use the VBA Filter Function to remove a Silly Name. Using again the “Step through with F8 while looking in Watch window” technique I see that this returns a very similar Array to its “Psuedo horizontal” 1 dimensional first Argument, simply reduced in ”length” by a Silly name

    (. As an intermediate Step, section 3) , lines 100 to 140, I can assign a newArray to the existing “Psuedo horizontal” 1 dimensional Arrays. Again the returned format is similar again and I note I can dimension my new Array as

    Dim NewArray As Variant
    Or
    Dim NewArray() As Variant

    . The results again are to produce a “Psuedo horizontal” 1 dimensional Array. )


    . So far so good. In section 4a) lines 150 t0 180 the Filtered Array is successfully obtained ( here I have dimensioned the New Filtered Array as
    Dim arrFiltValues1 As Variant )

    . What is troubling me is that section 4b) lines 200 to 220 errors as I have initially dimensioned the New Filtered Array as
    Dim arrFiltValues2() As Variant
    . taking into account all my experiments above, I cannot come up with an explanation for this.

    . Can anyone else

    . It is a niggly point again but , I think , a good insight and help in understanding how VBA is working.

    Thanks
    Alan

  4. #49
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,101

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Filter returns an Array of Strings, not an Array of Variants:

    Please Login or Register  to view this content.

  5. #50
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Quote Originally Posted by Kyle123 View Post
    Filter returns an Array of Strings, not an Array of Variants:

    Please Login or Register  to view this content.
    Great, Thanks, .. that explains it perfectly ( almost###)… this works!


    Please Login or Register  to view this content.
    Thanks kyle.

    .. I would never have thought of that as I see no logic to it.. or maybe I do….is it because the second argument is a string and so it follows it can only compare it with an Array of string types … ( obvious I suppose, I just went off in a weird direction thinking of how the Dimensioning had to fit seeing the Filter Method )
    . But### … Why did lines 160 to 180 work then.. ????

  6. #51
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,101

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Because lines 160 to 180 are using a Variant Not an Array of Variants - they aren't the same thing, a Variant can be anything (including an array of strings), a Variant Array is an Array of Variants.

    The logic is simple, filter is a function that compares a string to an array of strings, therefore returns an array of string matches. You may not always pass an array of strings in, but you'll always get an array of strings out.

    Since you like to go all in with referencing, it isn't VBA.Filter, it's VBA.Strings.Filter - does it make more sense in that context?a

  7. #52
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Quote Originally Posted by Kyle123 View Post
    Because lines 160 to 180 are using a Variant Not an Array of Variants - they aren't the same thing, a Variant can be anything (including an array of strings), a Variant Array is an Array of Variants.

    The logic is simple, filter is a function that compares a string to an array of strings, therefore returns an array of string matches. You may not always pass an array of strings in, but you'll always get an array of strings out.....
    . great that clears that up perfectly.. was along my last thought / suggestion. . Thanks a lot

    Quote Originally Posted by Kyle123 View Post
    .........

    Since you like to go all in with referencing, it isn't VBA.Filter, it's VBA.Strings.Filter- does it make more sense in that context?[.
    ... It probably does........ I need to go away and think about what the .Strings is ( Object , Method, etc. )

    Thanks again.

    . It probably sounds very tedious to you, but it does help getting these things explicit sometimes I think, and not just relying / accepting ther "implicit default "

    Alan

  8. #53
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,101

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Shouldn't you also be declaring Worksheet as Excel.Worksheet then?

    It's an Object

  9. #54
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Quote Originally Posted by Kyle123 View Post
    Shouldn't you also be declaring Worksheet as Excel.Worksheet then?

    It's an Object
    Yep, I always did,
    Or rather I did
    Application.Excel.Worksheet etc
    but it upset so many people i dropped it

    . it is reasonable to drop that bit , as I am in Excel so it is a good assumption that I am in using the excel library as default…

    BUT it can be very important to include VBA.

    There have been threads started when things like .Left .Right etc did not work, due to VBA different versions mixing up their library references. Using VBA.Left instead of .Left usually solved these problems..

    Alan
    Last edited by Doc.AElstein; 05-26-2015 at 09:01 AM.

  10. #55
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,101

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Could you please point me in the direction of some?

  11. #56
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Here are a few…
    http://www.mrexcel.com/forum/excel-q...eft-etc-2.html
    http://www.mrexcel.com/forum/excel-q...r-version.html
    http://www.mrexcel.com/forum/excel-q...ng-2013-a.html

    . I had exactly this problem running some XL 2010 Files in XL2007. My Right Left and Mid functions “did not work”, that is to say any code with lines using them Functions errored. Then adding the VBA. bit did the trick. There is a better way to overcome the problem I guess, if you know your way around referencing the different Libraries etc, but that is a bit above me.

    . I did this File to help an OP with the problem,…


    .. in the attached File for example I have this test code…( the File was made and saved in XL2010 )

    Please Login or Register  to view this content.
    .
    The code runs normally in XL2010


    If you open this file and run it in my XL2007 the code does not work., it crashes saying that the project or Library was not found.

    If you modify the code thus

    Please Login or Register  to view this content.
    … it does work.

    . I am sure my solution is very crude and you probably have a much better one. I guess it would be to be careful check exactly what Libraries are used / available etc.. etc… It could be that the original code will work if I disable some libraries… but then of course if it was a real File I may have need those etc.. etc.. I am sure you understand that better than me. (Please do not moan at me if my “VBA.” Solution is a bit naff!!)

    Alan
    Attached Files Attached Files
    Last edited by Doc.AElstein; 05-26-2015 at 10:39 AM.

  12. #57
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Quote Originally Posted by Doc.AElstein View Post
    ........... I need to go away and think about what the .Strings is ( Object , Method, etc. )........
    Edit.. I just googled a bit..

    The Strings is a Object full of Functions that typically work through looking for / working with Strings / character sequences .......so I a had better modify that last working code quick or Kyle will moan at me again

    Please Login or Register  to view this content.
    ........................................

    And for completeness the last few code lines again from my original Code from Post #48, with Kyle’s explanations in the ‘green comments

    Please Login or Register  to view this content.
    Last edited by Doc.AElstein; 05-26-2015 at 10:05 AM.

  13. #58
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    You should fix the missing references really otherwise something else is still going to fail.

    Anyway, since you're using strings:
    Please Login or Register  to view this content.
    Remember what the dormouse said
    Feed your head

  14. #59
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Quote Originally Posted by romperstomper View Post
    You should fix the missing references really otherwise something else is still going to fail.......
    . Sounds like putting "newer" libraries in older XL versions. Sounds a bit much for me.. I'll pass on that for now..


    Quote Originally Posted by romperstomper View Post
    ......
    Anyway, since you're using strings:
    Please Login or Register  to view this content.

    .. I have seen that $ bit before. I am not quite sure what it does... I will go off and "google it just now... do you have a simple explanation??

  15. #60
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,002

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Fixing the missing references is probably not going to involve putting new versions of the libraries in old XL versions.

    I'm pretty sure functionslike Left, Right etc are available in all versions of VBA.

    The references you need to 'fix' are more likely to be non-Excel libraries.

    For example if there was code in a workbook that was automating another application, eg Word, that could cause version problems.

    Mind you you could avoid the need for setting the references by using late-binding.

    PS Fixing the references usually just involves unchecking any that are marked as MISSING when you goto Tools>References...s
    If posting code please use code tags, see here.

+ Reply to Thread
Page 4 of 5 FirstFirst ... 2 3 4 5 LastLast

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