Solved: Call?

[mvidas]

Hi Everyone,

I'm looking to get some opinions on the use of Call. Does anyone use it?
I see 2 benefits to using it, neither of which are of much importance to me, but I'm thinking others might disagree. The two things I can think of:
-A way of reminding future code editors that you're referring to a coded procedure rather than inherent procedure
-A way of reminding future code editors that there is no return value of the procedure being called

Just curious
Matt

[Ken Puls]

Matt,

I ALWAYS use call. It makes it obvious that you're going to another procedure. The code becomes a little more self documenting.

I always make an effort to make sure that the code is documented so that others can maintain it in future. One day I won't be here, and to me it's something about being a professional.

Just my 2 cents. :)

[mvidas]

But as far as speed, theres no difference (even a ver y slight one)?

[Ken Puls]

Honestly, Matt, I've never tested it.

My understanding is that the call word is optional, as it is implied. Much like Activesheet is implied when you ask for a Range object. I still code Activesheet.Range, and also use call. It changes the syntax a bit, but that's it:

Call MyMacro(arg1)
'vs
MyMacro arg1

I'd be surprised to find any diference in execution speed worth talking about, to be honest.

[mvidas]

I feel the same way (and I doubt any difference would be noticed, even iterating thousands of times).

As I only qualify things when absolutely necessary (or are writing for someone who knows next to nothing), and rarely comment, I'll stick to not using Call.
I never said I was a good programmer :)

Thanks

[Ken Puls]

LOL!

I never said that I was a good programmer either, but I tend to work completely opposite to the way you do. I try to be very explicit in my coding (Option Explicit is more than just some keywords to me).

For example I (almost) always:
-Set a reference to Activesheet (and use it with If blocks)
-Set references to all workbooks if I'm using more than one
-Use Call statements
-Declare my variables with non-Variant types

I'm also trying to get into the habit of passing variants ByRef and ByVal to other functions.

:)

[Bob Phillips]

I tend to side with ken on these particular style aspects.

I also use Call all of the time, eveb for non-returning functions (in fact I often just use functions), mainly so that a control break out of this procedure is immediately identifiable, and also so that parameters are always conatined in parentheses.

My particular stylistics include:
- setting references to objects as much as I can
- extensive use of With ... End With
- NEVER use default properties
- use Call
- (mainly) use Function instead of Sub
- NEVER use shortcut notation
- keep procedurse short
- always decalre variables, even variant types
- keep comments short

and so on.

[mvidas]

I will frequently use With blocks to refer to an object (if I don't have a variable for it) like Activesheet in case that needs to be changed, but at what point do you stop?
Range("A1")
ActiveSheet.Range("A1")
ActiveWorkbook.ActiveSheet.Range("A1")
Application.ActiveWorkbook.ActiveSheet.Range("A1")

I do (almost) always declare my variables with non-variant types, generally unless it is notepad (for vbs) or unless it should be a variant, you'll rarely find me use one.
I got used to the byval/byref (at least in the procedure declaration, not while calling) a while ago when I learned the difference.

And I still don't have the option set to put "option explicit" everywhere. I usually hand-type it in, but not even always.

I guess since I don't get paid to program, I think it is ok to sometimes have poor programming practices as long as it doesnt crash something and it works fast. :)

[Bob Phillips]

I will frequently use With blocks to refer to an object (if I don't have a variable for it) like Activesheet in case that needs to be changed, but at what point do you stop?

Use object variables and it is much simpler.


Range("A1")
ActiveSheet.Range("A1")
ActiveWorkbook.ActiveSheet.Range("A1")
Application.ActiveWorkbook.ActiveSheet.Range("A1")

4 examples of one of my absolute no-nos!: pray2:

[mvidas]

Perhaps I should un-solve this thread, I'm liking these responses. And maybe change the title to "programming practices"


I tend to side with ken on these particular style aspects.

I also use Call all of the time, eveb for non-returning functions (in fact I often just use functions), mainly so that a control break out of this procedure is immediately identifiable, and also so that parameters are always conatined in parentheses.

My particular stylistics include:
- setting references to objects as much as I can
- extensive use of With ... End With
- NEVER use default properties
- use Call
- (mainly) use Function instead of Sub
- NEVER use shortcut notation
- keep procedurse short
- always decalre variables, even variant types
- keep comments short
I almost always use functions as well (at least while coding for myself) and only use Subs for entry points (less choices of what to choose..).
I do occasionally use default properties, though thinking about it now I think the only one I use is a cell's .value property (maybe 50/50 chance of me using it)

I try not to use shortcut notation (I'm assuming you're referring to the evaluate shortcut?), I can probably count on one hand the number of times ive used it in the past year.

I can't keep comments short when I use them, I'm waaay too verbose for that. Probably why I don't comment much (I know I should)

I guess just a confirmation question regarding creating objects and with blocks.. is method 2 any better (memory-wise) than method 1 here? just a simple example:
'1
With CreateObject("wscript.shell")
.Popup "hi", 1
End With

'2
Dim WSHell As Object
Set WSHell = CreateObject("wscript.shell")
WSHell.Popup "hi", 1
Set WSHell = NothingDoes the memory get freed when End With is reached?


and so on.Keep em coming!

[Ken Puls]

...at what point do you stop?
Range("A1")
ActiveSheet.Range("A1")
ActiveWorkbook.ActiveSheet.Range("A1")
Application.ActiveWorkbook.ActiveSheet.Range("A1") If all I'm doing is working on the Activsheet and will not change it, I'll go with ActiveSheet.Range("A1"). Any deeper than that, or if I will need to change the sheet at any point, I set a reference to an object.


And I still don't have the option set to put "option explicit" everywhere. I usually hand-type it in, but not even always. :doh:


I guess since I don't get paid to program, I think it is ok to sometimes have poor programming practices as long as it doesnt crash something and it works fast. :)
I completely disagree. While you are not being paid to program, per se, you are being paid by your employer, and you are programming. I personally try to make all my stuff stand up for long after I'm gone, as I don't want a bad reputation to follow me anywhere.

[mvidas]

4 examples of one of my absolute no-nos!: pray2:
I've noticed myself having variables for a lot more things since I made my first com add-in, but for setting a cell, would you always have a worksheet variable in something like:set cll = ws.range("A1")? What if it is for a function to always interact with the activesheet, do you create a variable for the activesheet?

Say for example this, which is one of the few procedures I have assigned to a keyboard shortcut:Sub insRow()
Selection.EntireRow.Insert
End SubIt never fails me, but as far as good programming practice goes, how would you code that instead?

One of my friends on EE at one point told me the last 4 letters of my username stood for "I Despise Active Sheet", since I almost never use that keyword.

[mvidas]

I completely disagree. While you are not being paid to program, per se, you are being paid by your employer, and you are programming. I personally try to make all my stuff stand up for long after I'm gone, as I don't want a bad reputation to follow me anywhere.True, but most of the programming I do here is not work related, more for increasing knowledge (while helping on the forums). The few things I have here that are for other people are well made and documented; I really only make that kind of thing on request. I am trying to convert my procedures that I know inside-and-out to a form that a future coder would understand, but as my job in no way calls for anyone who even knows what a programming language is, it isn't entirely necessary. I'm really only doing it now so that when I'm done with it (after extensive testing) I can tell my boss "I'm leaving, but heres a program to do my work for me" :)

[Bob Phillips]

I try not to use shortcut notation (I'm assuming you're referring to the evaluate shortcut?)
Yeah, that is what I mean.


I can't keep comments short when I use them, I'm waaay too verbose for that. Probably why I don't comment much (I know I should)
The problem with verbose comments is two-fold:
- if they are tool long, it probably also means they are probably not succinct or clear, so people will get bored and ignore them
- if they are too long, it probably means the code is not clear enough, therefore is bad code


I guess just a confirmation question regarding creating objects and with blocks.. is method 2 any better (memory-wise) than method 1 here? just a simple example:
'1
With CreateObject("wscript.shell")
.Popup "hi", 1
End With

'2
Dim WSHell As Object
Set WSHell = CreateObject("wscript.shell")
WSHell.Popup "hi", 1
Set WSHell = NothingDoes the memory get freed when End With is reached?
I wouldn't use method 1 personally, if it doesn't refer to at least two objects, it doesn't become more readable (less so IMO), and the overhead isn't warranted.

As to memory, With creates an implicit object variable, but one that you cannot explicitly release. It doesn't get released at the End With, buts should at the end of the sub. Howevere, if you don't trust VBA's garbage collection handler, then you might think you have a problem. I have heard some claim that they cannot kill instances of Excel because of these implied objects, never come across it myself.

[Bob Phillips]

I've noticed myself having variables for a lot more things since I made my first com add-in, but for setting a cell, would you always have a worksheet variable in something like:set cll = ws.range("A1")? What if it is for a function to always interact with the activesheet, do you create a variable for the activesheet?

That wasn't my no-no. I meant



Range("A1")


instead of


Range("A1").Value


Personally, I rarely set a range object, unless I will use it later in the code, or it is a variable cell.


Say for example this, which is one of the few procedures I have assigned to a keyboard shortcut:Sub insRow()
Selection.EntireRow.Insert
End SubIt never fails me, but as far as good programming practice goes, how would you code that instead?[/vba]

I wouldn't!

There are 6 commands in the toolbar commands for inserting/deleting cells/rows/columns,, so I have a custom toolbar with tjose 6 on (besides others).

[Bob Phillips]

I'm really only doing it now so that when I'm done with it (after extensive testing) I can tell my boss "I'm leaving, but heres a program to do my work for me" :)

You wa nt tgo be careful that he doesn't find out, and decide that your program canj do your work NOW.

[mvidas]

There are 6 commands in the toolbar commands for inserting/deleting cells/rows/columns,, so I have a custom toolbar with tjose 6 on (besides others).
I did often use the right-click key (I don't know the real name for it), and then 'I' or 'I-R' or 'I-C', the above just makes it easier for me for one of my sheets (I try to not use the mouse when possible). The only other macro with a keyboard shortcut I have (the only other excel related macro I use more than once a month) is one to rename the active sheet (easier than alt-o-h-r for me)


You wa nt tgo be careful that he doesn't find out, and decide that your program canj do your work NOW.Actually my bosses know I use scripts to get my stuff done, but they do like that. The person who did my job before me did everything manually, and a months worth of work for her is a few hours for me (with everything extra that they've given me after people left it is at about a week now). They know I spend a lot of my time 'practicing' and fine-tuning what I do. I'm about a month or two from getting most of my job automated now, unfortunately the programmer working my request is slow.
Ever used powerbuilder? want a job? :)

[mvidas]

As to memory, With creates an implicit object variable, but one that you cannot explicitly release. It doesn't get released at the End With, buts should at the end of the sub. Howevere, if you don't trust VBA's garbage collection handler, then you might think you have a problem. I have heard some claim that they cannot kill instances of Excel because of these implied objects, never come across it myself.
Interesting.. I didn't think the memory would be freed until after the sub was finished, but I thought it was possible it was set to nothing after the End With. I don't think I've ever done it like that ('think' being the key word), and now I won't :)

[Bob Phillips]

Ever used powerbuilder? want a job? :)

Used PowerBuilder, but that was some years ago, just before theyt brought out their web-enabled version.

[malik641]

Sub insRow()
Selection.EntireRow.Insert
End SubIt never fails me, but as far as good programming practice goes, how would you code that instead? I would do one of two things different with this (both for the same reason)...

Sub insRow()
On Error Resume Next
Selection.EntireRow.Insert
End Sub-Or-Sub insRow()
If TypeName(Selection) <> "Range" Then Exit Sub
Selection.EntireRow.Insert
End Sub Only because if you have an OLE object on your worksheet, and it's selected...then that object becomes "Selection", and you get an error ;)

As far as using call, I always use call. And I, too, have been recently using ByRef and ByVal and getting into a habbit with that.

I try to make each procedure as short as possible, and (more recently) break up my code into Functions/Subs that are called on to do something common that I think I'll have the chance of calling in a future procedure (although I should have been doing this a while ago...) :)...I've learned recently how much nicer everthing looks with less code in a main sub procedure, especially when everything's commented properly (using Bob's standards).

[johnske]

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... :devil2:

[mdmackillop]

Excel.Application.ActiveWorkbook.ActiveSheet.Range("A1").Value = "Yes"
[A1]="OK" every time!:duel:

[johnske]

Actually, if we wanted to be REALLY pedantic about writing all the defaults we really should not omit the Let statement either, i.e.Let Excel.Application.ActiveWorkbook.ActiveSheet.Range("A1").Value = "Yes":devil2:

[mdmackillop]

Hi John,
While you're about it, shouldn't you say "Please"!

[johnske]

I guess so :) Sub BePolite()
With MyPost.Rows(9)
.Replace "3) READ", "3) PLEASE READ"
End With
End Sub

[mdmackillop]

I thought

Please Let Excel.Application.ActiveWorkbook.ActiveSheet.Range("A1").Value = "Yes"

[Ken Puls]

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

:)

[mdmackillop]

I surrender!:whyme:

[Bob Phillips]

... 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.

[johnske]

Bob, dunno exactly what you mean by this
you show me anywhere that the Range object is defined in visual basic:dunno go to Help > Microsoft Visual Basic Help > Index, then type in "Range", click 'Search', and it's listed under "Range Object" :dunno

but you're just being disingenuous with this bit...
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. :)

[malik641]

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 :thumb

[malik641]

Is it safe to say the towel has been thrown in?

[Bob Phillips]

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.

[malik641]

I see.

Sport? No. Knowledge buddy :) Simply knowledge.