six superquick questions =]

[venomatic]

I thank you very much for taking the time to read, and apologize in advance for these ABCs (annoying, boring and possibly conceptual questions). Nonetheless, I'm very grateful for any thoughts on any one of them (many of which are actually observations). In any event:


1) For If-Then-Else statements, is the Else keyword effectively the same as Else: (i.e. with a colon)? (As far as I can tell they are, from the lack of errors when compiling)


2) Suppose that

Range("A1:A6").Select       '//you can use With-End With structure
Range("A1:A6").Copy

Is the latter line, Range().Copy, always the exact same thing as Selection.Copy (and possibly ActiveCell.Copy) in general?


3) Is it fair to say that for all user-made Functions, that they must have a (main) variable of the same name and that if not declared otherwise, they are treated as variants?


4) If we wanted to use If-Then for a bunch of conditions, I believe we can either use a series of strictly If-Then, or a series of If-Then-ElseIf (where End If seems to be always required, but usually not so for the former). For the latter, is it true we don't necessarily need to wrap the conditions up with the keyword "Else"? For example:

'This simply returns the price with appropriate discount
Dim Amount
Amount = InputBox("Enter Amount: ")
 
If Amount > 0 And Amount < 25 Then
Discount = 0.12
ElseIf Amount >= 25 And Amount < 60 Then
Discount = 0.2
ElseIf Amount >= 60  Then
Discount = 0.25
 
MsgBox "Your Price: " & Amount*(1-Discount)                       
End If           
End Sub

5) For a given cell or variable, is it fair to say that if they are Null, it means they have a value that has yet to be determined, while Empty means it is blank, undefined or "nothing"? (And if I'm not mistaken, the memory address of Null is fixed, and the memory address of Empty hasn't been determined). Or is it the other way around (in any language)? I'm getting confused since different sources from Google seem to be give me opposing information).


6) And this isn't really a question; I just found it interesting that the ActiveCell in this example (among others) doesn't change when being Offset (variable,fixed):

'this assigns random numbers to 25 cells
Sub FillintheRange()
Dim Counter As Long
For Counter = 0 To 24
ActiveCell.Offset(Counter, 0) = Rnd
Next Counter
End Sub

Compared with,

Sub MyRelativeMacro()​
'this fills some ranges one after another with a word
ActiveCell.FormulaR1C1 = "Lemons"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Pineapples"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Bananas"
ActiveCell.Offset(0, -2).Range("A1").Select​
End Sub

Again, any feedback welcomed!!!!!!!!!!!

 

[mikerickson]

1) The : acts as if it were the start of a new line

Else:
    A = 1

'is treated like
Else

A=1

2)

Range(someRange).Copy

is prefered to

Range(someRange).Select
Selection.Copy

They act the same, but Selection can cause errors, takes resources and is not needed.

3)You can declare the result of a UDF in the declaration line. The default is Variant, and unspecified UDF is marginaly slower than one that is declared a variant.

Function myRowFtn(Optional aRange As Range) As Double
    Rem this UDF emulates the worksheet function ROW

    If aRange Is Nothing Then Set aRange = Application.Caller
    myRowFtn = aRange.Row
End Function

4)In any If..ElseIf..Else..EndIf situation there are a variety of ways to branch. Have you looked at Select Case

Amount = InputBox("Enter Amount: ")

Select Case Val(Amount)
    Case Is <= 0
        Rem bad entry
    Case Is < 25
        Discount = 0.12
    Case Is < 60
        Discount = 0.2
    Case Else
        Discount = 0.25
End Select
 
MsgBox "Your Price: " & Amount*(1-Discount)

5) (I need more coffee)

6).Offset is a property of a range, not a method. It doesn't "do" anything, it is a "function" of the range. Again, don't use Select unless absolutely needed.

 

[CharlesChuckieCharles]

1//

If X=0 then
X=4
 Else 
X=25
endif
 
'the :(Colon) separates statements on the same line
X=0
X=IIF(X=0,4,25):Y=3:Z=X*Y 
'result X=4, Y=3 and Z=12

Best not to use : in an If statement because confusion
//1


2//


Range("A1").Copy doesn't require a select statement first in order to copy

But yes it performs the same as Selection and activecell

Range("A1").Select
selection.Copy

Range("A1").Activate
activecell.copy

Do the same thing
//2

3//
No undeclared Function returns are always a boolean
Function Mydouble(A as Range, B as range)

when used in Spreadsheet =MyDouble("a1","C1") woudl return a False, not useful but not required

You don't have to declare a variable in a sub or function if you don't have to
although it would be an odd function

//3


4//

IF Then Else EndIf

If the condition is on one line

if  X=3 then x=9

therefore you don't require an endif

But correct formating helps make your code easy to read, less likely than anyone else might miss the significance

The Approved form in VBA is

IF  Conditions   then
   statements
Endif
 
IF  Conditions   then
   statements and or  other condition tests
else
   other  and or  other condition tests
Endif
 
IF  X=1 then
   'statements and or  other condition tests
 
ElseIf X = 2 Then

  ' other  and or  other condition tests
ElseIf X = 3 Then

   
'other  and or  other condition tests
ElseIf X = 4 Then

  ' other  and or  other condition tests
ElseIf X = 5 Then

  ' other  and or  other condition tests
 
Else
  'acts as logic sink if all conditions fail
Endif
 
'If the IF Condition fails the each ElseIF is evaluated in turn.

if you have several ElseIF's in a statement you want to consider Select Case
//4

5//
The Null value indicates that the Variant contains no valid data. Null is not the same as Empty, which indicates that a variable has not yet been initialized. It is also not the same as a zero-length string (""), which is sometimes referred to as a null string

In vba you should use ISNULL() when evaluating for Null
//5

6// Offset doesn't move the active cell or the focus of the activecell, it references the value/properties of a from a cell at an offset from the activecell

//6
//5

 

[venomatic]

of course, I am grateful for your prompt, comprehensive input!!!!!

1) good point!
1) not too sure about IIF, but I'll try to review your code again; in any case, I'm going to avoid : where I can!

2) ah, so my code was inefficient, as well as the Selection.Copy variant
2)

Range("A1").Activate
Selection.Copy

and

Range("A1").Select
Activecell.Copy

Hmm... I wonder if these make sense too (I don't seem to get any compiling errors at least), not that I will ever use them, as there doesn't seem any reason not to use the simpler Range(someRange).Copy

3) *I'll need some time analyze this, i.e. use alot of F1*
3) good point!

4 both) I guess Select Case is much better in usual practice! (at least I may not have to remember all the End If conventions, even though they may be good to know)

5) haha, I don't blame you!
5) yes, it was the zero-length string that had confused me (that, and trying to make a distinction between cells and variables in the null/empty context) but I think I get it... Null variables are without value nor form (i.e. not even defined), compared with Empty, which we know what the type is but not the value at the moment... hmm, but looks like now I have to make a distinction between variant and variant null (and maybe variant empty? but I'll see what I can google up)

6) good point!
6) very true

thanks again!!!!!!!!

 

[xenou]

Note:
1) Personally I live without Nulls in Excel VBA quite well. They never enter my programming or my thinking (unlike, however, Access VBA programming) What I do keep track of is Empty, Nothing, and zero-length strings. In GUI Excel worksheets we have blank cells too, which are the "real" empty cells, i.e. being those that return true for ISBLANK().

VBA differs here from some other languages - VBA always initializes primitive variables to a default value (zero, "", or False). Variants initialize to Empty. And objects are Nothing. I suppose something in this list might be null but I don't know -- and I find I never need to know - that is probably just due to my habits of programming Excel VBA. We never have primitives in VBA that are undefined as you would get, say, in Java (if memory serves me correctly).

2)
There is a difference between If-ElseIf-ElseIf and If-ElseIf-Else
The former has three conditions and will only execute if one of them passes the conditional test. The latter has two conditions and will execute the Else statement if the two conditional tests fail.

 

[venomatic]

thanks for the feedback xenou!
1) empty/blank/nothing/null/zero-length/variant/etc. ... now I'm getting really confused
2) good point! I believe in my previous If-ElseIf-ElseIf example, the (discount) variable is just zero (i.e. initialized behind-the-scenes as so) if none of the conditions are satisfied

 

[venomatic]

Excel help gives
"To return a value from a function, assign the value to the function name. Any number of such assignments can appear anywhere within the procedure. If no value is assigned to name, the procedure returns a default value: a numeric function returns 0, a string function returns a zero-length string (""), and a Variant function returns Empty. A function that returns an object reference returns Nothing if no object reference is assigned to name (using Set) within the Function."

So I'll try to work from above and see where it takes me; I imagine its the same for variables. Hmmm.....

 

[mikerickson]

Re: "We never have primitives in VBA that are undefined as you would get, say, in Java (if memory serves me correctly)."

The only time I run into Null is with userform controls. ComboBox1.Value is Null if the user doesn't select anything and failing to test for that can cause problems, which is why I use the .Text property more than the .Value. (Similar for ListBoxes)

 

[xenou]

Typically, you want a function to return something:

Function TwoTimes(ByVal x As Long) As Long
   Foo = (2 * x)
End Function

Occasionally I will do something like this:

Function GetSomeRange() As Range
    On Error Resume Next
    GetSomeRange = BlahBlahBlah
End Function

The function above returns a Range object, assuming the BlahBlahBlah code succeeded. If it did not succeed, the line is skipped (as an error) and the function returns Nothing. It's a bad example - maybe I can think of a more realistic one.

@Mike - thanks. I've been getting used to that with Access comboboxes lately where its the same.

 

[venomatic]

thanks for the extra assistance gents!

(i) might be awhile before I can check out userforms
(ii) will definitely review everyone's code on functions
(iii) and from what I've culled from everyone's posts and Google on the topic of Null values, this is my current intepretation:

----------------------------------------
If you don't know someone's e-mail address, that is an unknown or "null" until you can determine what it is; null values doesn't take up any memory. But if you then know that particular someone doesn't have an e-mail account, you can re-label that field as "Nothing" (kinda) or an Empty (string); this takes up memory. I'm not too sure, but this seems to be related to a blank cell.

It seems that effectively, the latter agrees with Excel's definition that a Variant variable hasn't been initialized, since by default, it is also an Empty (string) (or zero for numeric data). However, both Empty and Null are Variant subtypes; why these are Variant (I mean, can't a Dim x As Long be unknown?)... I guess the two are special cases defined under Variant (at least according to Excel Help glossary).

And off-tangent, "Missing" refers to a [left-out] optional parameter of a procedure.
----------------------------------------