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

 

[xenou]

Mainly just associate Empty with Variant variables, Nothing with Object variables, and 0/True/"" with primitive numbers/booleans/strings. You'll get along fine. I find that I just get used to these types of things after a while (though it is worthwhile to stop and think about it).

VBA has variants which are variables that can be anything. Once you assign an actual something to a variant, it takes on the right type of subtype. If you assign an object to the variant, it becomes Variant/Object; if you assign a string, it becomes Variant/String; if you assign an array it becomes Variant/Array. And so on.

Normally, you want your variables to have values, so they'll be "something" more often than "nothing", and "full" more often than "empty". You need these things mostly for testing if variables have values or not -- i.e., are they in an uninitialized state or not.

I don't believe I've ever actually set a variable equal to Null in Excel VBA, but I'm sure there's occasions for it.

ξ

 

[venomatic]

much obliged for the concrete insight!

 

[venomatic]

4) ah, I never knew about the Val(string) function. I don't know if that's needed here, but I wished I knew that conversion technique for some of my earlier stuff. Thanks (and also for introducing Rem)!


3) Mike, if you have the time, may you give a little more insight on your function? Maybe I can understand it better as I go deeper into my manual and F1 all the keywords (which I've been doing) , but this seems a bit tricky to tie it all together. If not, thanks anyway!

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

 

[mikerickson]

Function myRowFtn(Optional aRange As Range) As Double

This declaration line gives the name of the function, myRowFtn. It accepts one optional argument, which is a range and that it returns a value that is data type Double.

Rem this UDF emulates the worksheet function ROW

This is a comment. (Rem is old style).

If aRange Is Nothing Then Set aRange = Application.Caller

If the argument passed to the function is Nothing, i.e. if the optional argument is omitted, set aRange to Application.Caller. When called by a function on the Worksheet, Application.Caller is the cell holding the formula using that function.

myRowFtn = aRange.Row

This line sets the function's value to the row number of the upper-left cell of aRange. A purist would have included the CDbl function instead of relying on Excel to do automatic type conversion.* (The .Row property of a range is Long, the function returns Double.)

End Function

Thus, the function returns either the row number of the upper-left cell of the argument or the row number of the cell holding the function if the optional argument is omitted. It is, as stated, an emulator for Excel's ROW function.

*-Any value in a cell is either data type Double, String, Boolean or Error. Hence the function is declared type Double. I could have omitted a type declaration for the function, which would have the same effect as declaring it a Variant. Or I could have declared it as type Long, trusting Excel to convert it to Double when putting the value in a cell.

 

[venomatic]

thanks for the very extensive walk-thru Mike! Just what I needed ^^
3) I don't know if my Sub can be improved, but I guess its a suitable way to call the function (aside from the worksheet; suggestions welcomed of course)

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
'--------------------------------------------------------------
Sub RowEmulator()
Dim x As Range :Rem x must be declared as range
Dim Result 
Set x = ActiveSheet.Range("F5") :Rem x needs Set for objects
Result = myRowFtn(x) :Rem forget about using the (Call) myRowFtn(x) line
MsgBox Result
End Sub

4) and hopefully this is my last question for awhile, but I was curious if Val("&HFFFF) is common for conversions, unless theres an alternative


anyhow, no need to reply and thank you everyone!

 

[mikerickson]

I like using Val since it accepts all strings without error.

 

[venomatic]

thanks ^^