+ Reply to Thread
Page 5 of 5 FirstFirst ... 3 4 5
Results 61 to 67 of 67

Range Dimensioning, Range and Value Referencing and Referring to Arrays

  1. #61
    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 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??
    … just googled words to the effect that Left could return an empty ( so a variant type ) whereas Left$ returns a string only and will error for an empty..


    left$() will return a string or generate an error if passed NULL
    left() will return a string or NULL if passed NULL


    Sounds in my Naïve ‘ead a bit of a contradiction.. Left is a function of the Strings Object.. which works with and returns strings…
    Last edited by Doc.AElstein; 05-26-2015 at 11:08 AM.
    '_- 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 )

  2. #62
    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 Norie View Post
    .....

    PS Fixing the references usually just involves unchecking any that are marked as MISSING when you goto Tools>References...s
    Thanks for the reply.
    . I think somewhere along the way I tried that one once, and it was an alternative solution... to get that code to work in XL 2007, but in a real file I had those referrences were needed again running in XL 2010, so it was a pain switching on and off as it were...
    The
    VBA.
    seemeed at the time for me a better working solution, but as I said the proper solution is probably a bit much for me, I probably end up deleting something important and crashing my excel..... or worse..

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

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

    References that are marked as MISSING are very unlikely to be the standard ones that are set by default.

    When you've had the problem what other references were being used in the workbook(s) apart from the standard ones?

    PS By the standard ones I mean Visual Basic for Applications, Microsoft X.0 Object Library, OLE Automation and Microsoft Office X.0 Object Library.

    In X.0 the X stands for the version, for example for Excel 2010 I believe it's 14.0, and you shouldn't change which version of the libraries a workbook
    is using.
    If posting code please use code tags, see here.

  4. #64
    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 Norie View Post
    References that are marked as MISSING are very unlikely to be the standard ones that are set by default.

    When you've had the problem what other references were being used in the workbook(s) apart from the standard ones?

    PS By the standard ones I mean Visual Basic for Applications, Microsoft X.0 Object Library, OLE Automation and Microsoft Office X.0 Object Library.

    In X.0 the X stands for the version, for example for Excel 2010 I believe it's 14.0, and you shouldn't change which version of the libraries a workbook
    is using.



    . Your explanation sort of ties up, I think..… The File I downloaded in Post # 56 originally came from a German Excel Learn DVD which was aimed at all versions up to XL2013. I just looked and the “missing libraries” it reference when I look in my XL 2007 are Microsoft Outlook 14.0 Object Library and Microsoft Word 14.0 Object Library. I just quickly experimented… I have to uncheck both to get that test code to work without the
    VBA.
    Bit..

    . But I do not have a real problem here… I just brought it up and referenced my earlier problem as kyle asked me to in passing in post # 55.

    . But thanks very much for the replies..you explained very nice and clearly -- it is interesting now to get an idea what the problem was back then.. In the meantime I gave up learning with the DVD’s anyway and got further with my learning participating here and over at MrExcel. So I do not have the problem any more..



    Alan

  5. #65
    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

    Hi again,
    . One remaining point which got sunk in the Library references diversion.. I have got very confused trying my best to post a clearing up / closing Post..Any help Please

    . Quick recap……
    Post #58
    Quote Originally Posted by romperstomper View Post
    …....since you're using strings:
    Please Login or Register  to view this content.
    Post #59 and Post #61
    Quote Originally Posted by Doc.AElstein View Post
    .......
    .. 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??......

    … just googled words to the effect that Left could return an empty ( so a variant type ) whereas Left$ returns a string only and will error for an empty..for example

    left$() will return a string or generate an error if passed NULL
    left() will return a string or NULL if passed NULL


    Sounds in my Naïve ‘ead a bit of a contradiction.. Left is a function of the Strings Object.. which works with and returns strings…
    ………………………………………………………………………………
    .. Starting again simplified

    Please Login or Register  to view this content.
    … I have experimented some time now with endless variations similar to this:

    Please Login or Register  to view this content.

    . I have not ( yet ) got the code to error.. ( Other than with a line such as line 22, but then both Left and Left$ error, as expected *** )

    . My conclusions (were ******* )

    . 1 ) The reference I googled is wrong!!

    . 2 ) Actually Left$ is not of any significance here: ( as is probably the case of any Method from the Strings Object.. )….
    …. Because:

    . 3 ) We got out of the discussions that Left is a method of the Strings Object. In English this means it only ……looks at, plays with, has anything to do with, work through looking for, working with ….etc .etc…. Strings / character sequences. As part of it’s “working” it takes Anything (variable “Something” in my code ) and, if it can *** converts that to a string , as it takes a string only as a first argument. I will therefore only ever have an error or a string applied to the Left. It will never occur that a blank for example is passed,, so Left$ will never have any different effect..*******


    . It is a very niggley point, as always here but some good basic points and understandings are coming through. So if anyone can comment on this last point I would be very grateful. And please insult and correct me if appropriate – I expect my conclusions wrong….as I HAVE seen Left$ somewhere ( I think - I cannot remember where!! )

    Thanks in advance!!
    Alan

    P.s. I had a last thought. Maybe in some other libraries the Left Function performs differently . I repeated my experiments in that File that would not work for Left ( The one that needed VBA.Left or VBA.Strings.Left to work ). But my conclusions remained the same

    ……………………………………………………..

    EDIT
    .4 ) -- ******* I just found a way to get Line 40 to error and at the same time Line 30 ( and line 31 ) not to error…

    Please Login or Register  to view this content.

    This is telling me I can somehow “force” / “coerce” the Left Function to work with and return a non String. So this contradicts the definition of the Strings Object? Also contradicts my idea that Something in the first argument would be converted to a string if it can be *** ? As I think I can convert a Null to a “” String ??

    Very confusing..
    … This would now suggest another conclusion.
    .. generally a Strings object is one which works through, ……. looking for, working with ….etc .etc……. Strings / character sequences. . However we can “Trick it into taking and returning something else, sometimes, as with the case of making something in it’s first argument a Null….. ( and BTW we have a clear contradiction here that the VBA Left Function takes a string as second argument!!! )

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

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

    So this contradicts the definition of the Strings Object
    Not really, Strings is just used as a generic namespace for all things String related - I'm not aware of a definition that specifies thats String functions must return strings, indeed Filter, Split, Len to name a few do not return strings.

    RE: passing Null, I believe that Null is just a special type of Variant, so it's the same as passing Something in your code. That's why it errors in Left$, I believe that Left simply coerces the first parameter into
    Variant, Left$ does not - that's why Left$ is preferable over Left, it's theoretically more efficient.

  7. #67
    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
    Not really, Strings is just used as a generic namespace for all things String related - I'm not aware of a definition that specifies thats String functions must return strings, indeed Filter, Split, Len to name a few do not return strings…….
    OK, thanks that is helpful. My “Definition was made up from googling everywhere and making an average of everything I read, and if you are not sure of a strict definition then there probably is not one, or whoever once knew has forgot, died, and never bothered to write it down, So I am near as probably possible to understanding that one ! ? Possibly my definition… ……looks at, plays with, has anything to do with, work through looking for, working with ….etc .etc…. …. Could be valid for a String Object that has the $ added ?? F1 gave me something near to my definition for the Strings Object , but some Experienced regulars tell me that the info there is often not too good.


    Quote Originally Posted by Kyle123 View Post
    ………………

    RE: passing Null, I believe that Null is just a special type of Variant, so it's the same as passing Something in your code. That's why it errors in Left$, I believe that Left simply coerces the first …
    So maybe the Left does its best to get at the “Left” of whatever is there, having various processes it checks through to achieve that..,.. one could be my idea of trying to convert it to a string, another could be to seeing if the argument can have a “sensible” result some other way… and/ or maybe it just has a list, and for a Null it says give a Null as left of nothing is nothing, sort of

    Quote Originally Posted by Kyle123 View Post
    ………………
    … why it errors in Left$, I………Left$ does not…..
    - that's why Left$ is preferable over Left, it's theoretically more efficient.

    . That could tie up with my idea, or something similar… with $ it just assume the argument is a string , does not do the other stuff and so is faster… The normal default error handler ( or one of the other two error handlers if we choose to enable one of them ) is then left to take over and handle the situation when something gets confused or does not work..
    )

    Thanks Kyle,

    P.s. I put the links in you asked for in Post #55 in Post # 56 , in case it got lost in the Library references diversion

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

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