Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 34 of 34

Thread: Solved: Call?

  1. #21
    Administrator
    Chat VP
    VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Got in late here...

    My three tips are:

    1) READ THE INSTRUCTION MANUAL

    2) READ THE INSTRUCTION MANUAL AGAIN

    3) READ THE BLOODY INSTRUCTION MANUAL!!

    1) Always use all the properties and methods that are documented in the VBA Help files except for those few that the Help files specifically caution you against using (e.g. While_Wend is one such that's best avoided). It may not always be obvious, but there's always a reason for them to be included.

    2) Always heed the advice and tips given in the VBA Help files (for example, you're advised to use Option Explicit). Except for the very few that, in the fullness of time, have shown to be incorrectly documented.

    3) The Set MyObject = Nothing statement only 'releases' the piddling amount of memory contained in the variable that refers to the object. (The set statement simply cannot release any 'object' that wasn't even created - and an object is only created when Set is used in conjunction with the New keyword). However, if you don't really understand all the preceding, using Set MyObject = Nothing creates very little overhead )

    Nothing wrong with using defaults, seriously, who on earth uses either Excel.ActiveWorkbook.ActiveSheet.Range("A1").Value = "Yes" or Application.ActiveWorkbook.ActiveSheet.Range("A1").Value = "Yes" or even
    Excel.Application.ActiveWorkbook.ActiveSheet.Range("A1").Value = "Yes" everything preceding "Range" is the default, as long as you're quite clear about that and always keep it in mind there's no problems.

    If default properties were not meant to be used, the creators of visual basic would not have taken the time to write the code to cater for them. Everyone would then be bound to ALWAYS explicitly write their code in a form similar to one of the above.

    But - a cautionary word here - if you are going to rely on default properties ALWAYS MAKE SURE YOU KNOW EXACTLY WHAT THEY ARE - when you're coding in VBA you're speaking visual basics' language, so learn to 'speak' the language properly - visual basic is quite literal and knows exactly what all the defaults are (they've been hard-coded into it) - so you have to ensure that you are always referring to the exact same thing that visual basic is.

    Comment your code as much or as little as is required to let its intent be known to all those unfamiliar with the method or technique used (if others can't be bothered reading your comments then that's their problem).

    Personally, I use Call simply for documentation purposes...

    I just LOVE being the devils advocate...
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  2. #22
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Excel.Application.ActiveWorkbook.ActiveSheet.Range("A1").Value = "Yes"
    [A1]="OK" every time!
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #23
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Actually, if we wanted to be REALLY pedantic about writing all the defaults we really should not omit the Let statement either, i.e.[vba]Let Excel.Application.ActiveWorkbook.ActiveSheet.Range("A1").Value = "Yes"[/vba]
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  4. #24
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi John,
    While you're about it, shouldn't you say "Please"!
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #25
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    I guess so [VBA]Sub BePolite()
    With MyPost.Rows(9)
    .Replace "3) READ", "3) PLEASE READ"
    End With
    End Sub[/VBA]
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  6. #26
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    I thought
    [VBA]
    Please Let Excel.Application.ActiveWorkbook.ActiveSheet.Range("A1").Value = "Yes"
    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  7. #27
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    [vba]Sub Yes()
    On Error Goto AskNicer
    Please Let Excel.Application.ActiveWorkbook.ActiveSheet.Range("A1").Value = "Yes"
    Exit Sub

    AskNicer:
    Pretty Please Let Excel.Application.ActiveWorkbook.ActiveSheet.Range("A1").Value = "Yes"
    End Sub[/vba]

    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  8. #28
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    I surrender!
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  9. #29
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by johnske
    ... visual basic is quite literal and knows exactly what all the defaults are (they've been hard-coded into it) ...
    Absolute tosh!

    Defaults are neither hard-coded, they have to be gotten from the type library as they are associated with objects in the object model, nor does visual basic know exactly what they are, you show me anywhere that the Range object is defined in visual basic.

  10. #30
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Bob, dunno exactly what you mean by this
    Quote Originally Posted by xld
    you show me anywhere that the Range object is defined in visual basic
    go to Help > Microsoft Visual Basic Help > Index, then type in "Range", click 'Search', and it's listed under "Range Object"

    but you're just being disingenuous with this bit...
    Quote Originally Posted by xld
    Defaults are neither hard-coded, they have to be gotten from the type library as they are associated with objects in the object model
    I find this quite interesting - so there is absolutely no code at all to create or define the object model? We slip in our CD, install Office, and everything just magically appears on our machine and Excel just "knows" how & where every object in the object model is, and how it's associated? Without any code being used? - Fascinating stuff, but I really don't think so, everything in the object model has been hard-coded onto the disc that we buy when we purchase our very own "Office" CD... However you want to word it "gotten from the type library" or whatever, they are essentially "default properties" that have been written into that lower-level code on the CD.

    Also (syntax and language aside)... by implication, you're saying that every VBA function that you write that has an "Optional" argument/property in it does NOT have any "default" of any kind to fall back on if the user omits to specify an argument/property (???? I thought that was the entire purpose of "Optional" ????). I'm sure you'll find that if you get into the underlying code that controls what we know as "VBA", that every visual basic function in the underlying code has similar very carefully thought out optional/default arguments to fall back on (although they may not all be documented as being the default for that function).

    However, whatever theory may say, it's as simple as this - if the underlying code for your visual basic functions have no defaults (i.e. optional properties to fall back on) then that function will simply FAIL if everything's not explicitly specified.

    For example: If Value is not the default property for the Range object then MsgBox Range("A1") really SHOULD crash and burn every time (perhaps with an error message from Excel - perhaps not) - however it doesn't fail - it simply returns the value contained in the cell A1, not the Text, nor the Formula, (you must explicitly specify those) - test it as often as you like, for a single cell it always returns the cells' Value. This default usage of value can only fail if a statement contains something doubtful or ambiguous that visual basic really can't resolve on its own.

    One example of ambiguous/non-ambiguous for visual basic is this: Range("E1:F1") = Range("A1") isn't ambiguous, we simply want the values in E1 and F1 to be the same as the single value in A1 on the RHS - no problems there either for me or for visual basic.

    However, Range("E1:F1") = Range("A1:B1") is doubtful/ambiguous (what if one cell on the RHS contains a formula and the other a value?)and so visual basic does nothing, but we can remove the doubt by specifying Range("E1:F1") = Range("A1:B1").Value (which coerces it to transfer the values to E1 and F1) or, Range("E1:F1") = Range("A1:B1").Formula (which coerces it to transfer both formulas and values to E1 and F1). Edit: In fact, if we should (somewhat erroneously) put Range("E1:F1").Value = Range("A1:B1").Formula, the 'Value' on the LHS is completely ignored because of the 'Formula' coercion being imposed by the RHS. It is then not difficult to see that putting either 'Value' or 'Formula' on the LHS is completely redundant - the RHS coercion means they're to be ignored anyway.


    Another default: it IS documented (in "Range Property (Application, Range, or Worksheet Object)") that when Range is used without an object qualifier, the Range property is a shortcut for ActiveSheet.Range i.e. whatever the name you choose to call it, 'shortcut', 'default', 'optional argument',..., whatever, ActiveSheet is the "default" object qualifier.

    I don't know if it's documented (and can't be bothered looking - but we both know - and it's quite easily tested) that ActiveWorkbook is the default object qualifier for ActiveSheet (or any other similar objects) and the same principle applies for Excel and/or Application.

    I always find it's much safer to operate on empirical evidence obtained from testing and not just on what theory tells me should be happening.
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  11. #31
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    I don't know about anyone else, but I'm anxiously waiting for a rebuttle from Bob...to me, this is where you learn serious core knowledge. Keep going, guys




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  12. #32
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    Is it safe to say the towel has been thrown in?




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  13. #33
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    No, but there is no point in trying to debate when things you didn't say are used as a counter-argument, and things that you did say are twisted to try and make another point. Life is too short to waste my time in this manner, just because you might get some sport out of it.

  14. #34
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    I see.

    Sport? No. Knowledge buddy Simply knowledge.




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

Posting Permissions

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