Advanced: Excel order of evaluation and side effects 2

[JohnCHolmes]

I'm grateful for the Selection.Resize solution to the thread "Help with Excel macro recording and absolute addresses" (BTW, has anybody ever gotten Tek-Tips search to work?!).

I have just successfully tried reducing the current selection by 1 row, using

Selection.Resize(Selection.Rows.Count - 1, Selection.Columns.Count).Select

But being a REAL non OOP C programmer, I should be concerned with side effects. That means, is the order of evaluation guaranteed here, so that the "pre-resized" counts are used, not the "pre-resized" ones?

Moreover, does anyone know enough about Excel internals to comment definitively on side effects and order of evaluation?

How about on IF statements - are both conditions evaluated if the first is true? This would be valuable to know for optimization (any dinosaurs remember that term? Well, it still matters at times). I sometimes have huge hairy mother IF conditions like
IF( ISNA(LOOOONG statement w/ slow

http://www VLOOKUP),"",LOOOONG

statement w/ slow

http://www VLOOKUP)

TIA

 

[SkipVought]

John,

I can't really comment on the optimization or order of evaluation question.

But I can comment on the use of Select. There's a bunch of stuff that you can do WITHOUT Select-ing a range. In fact, to assign a value from/to a cell on a sheet, you do NOT have to have that sheet active, unlike Select-ing. And Activating sheets and Selecting ranges eats up time!

Hope this helps Skip,
SkipAndMary1017@mindspring.com

 

[ratman]

In reference to Skip's good point concerning the option of avoiding activation / selection.....Personally I avoid selection because this automation is real slow and not necessary. Here's just one small example of avoiding the prementioned; others would be in during Search, Find or returning value loops where I opt for For Next or For Each loops.

Consider this:

If IsEmpty(Range("Sheet1!A2&quot.Value) Then
lastrow = 1
Else
lastrow = [Sheet1!A1].End(xlDown).Row
End If
Range("A" & lastrow + 1).Value = yourvariable

Thank you,
Dave Rattigan

 

[xlbo]

Good answers from both Skip and Ratman - on the IF statement point, NO, if the 1st condition is met, the 2nd is ignored. Therefore, for optimisation, always make the more likely of the 2 your 1st statement. However, this will more than likely not help too much with VLOOKUPS - they are one of the slowest calculating formulae because, by necessity, they have to perform an internal loop through the records to find the match. If you are sure of the integrity of your data, you may be able to get away without the 4th statement of VLOOKUP (FALSE) - this searches for an exact match and seems to increase the calc time considerably - without it, however, you must sort your lookup data
If you are not averse to trying things out, have a look at the INDEX and MATCH functions - you can combine them to create a formula that works the same as VLOOKUP but it's quicker ('cos of the index) and it can look left as well (VLOOKUP only loks right)

HTH
~Geoff~

 

[rmikesmith]

I don't claim to have definitive knowledge of VBA internals, etc. but based on my experiences:

You can rely on .Rows.Count and .columns.Count to have the values that exist prior to the statement executing in which you are referencing (and changing) them. That is also true in analogous constructs.

Geoff - Not sure which particular version of Excel/VBA you are using but at least up through Excel 97 (VBA v. 5) all conditions are evaluated even though previous conditionals evaluate to true. I have seen this documented for Excel 5 and if you run the following test procedure under Excel 97 it fails with a divide by zero error:

Sub TestConditionalEvaluation()
Dim v As Integer
Dim flag As Boolean

flag = True
v = 0

If flag Or (20 / v > 1) Then
  ...
End If

End Sub

Regards,
M. Smith

 

[segmentationfault]

I cannot agree! Short circuit evaluation in VB does not function in the same fashion as it does in C. For example,

Sub ShortCircuitEvalTest()
    Dim Ints(10) As Integer
    For i = 0 To 9
        Ints(i) = i
' Uncomment either if block
'        If (Ints(i) = 0) Or (Ints(100) = "a string") Then
'            MsgBox "Short Circuit Works"
'        End If

'        If (Ints(100) = "a string") Or (Ints(i) = 0) Then
'            MsgBox "Short Circuit Works"
'        End If
    Next i
End Sub

Both If blocks result in a subscript out of range, indicating that the second (or first) boolean expression is NOT ignored. In C, you can check your index and use it in the array in the same line without worry - if the index exceeds the array boundary, the evaluation stops at that point. In VB however, you cannot use this syntax.

With regards to passing objects to themselves, which I believe is the crux of the original post, I have never seen a situation where passing parameters by value has unusual side effects. Passing the selection to itself should have the same effect as using the assignment operator in statements such as i = i + 1, which replaces C's i++;

 

[segmentationfault]

Huh, must have been nearly simultaneous posts. But I must comment, your logic is astounding M. Smith!

 

[rmikesmith]

segmentationfault,

I love the wealth of experience and knowledge people bring to Tek-Tips forums!

The incidence of nearly simultaneous postings is almost as frequent as Microsoft releasing service packs

Regards,
Mike

 

[xlbo]

Erm...chaps - very nice answers but I was talking about worksheet formulae not VBA - I know this is a VBA forum but I think that the question was related to worksheet formulae (in terms of the IF statements). I bow to your knowledge of VBA functionality but I'm pretty sure that worksheet IF statements work as I said

However, I now know more about VBA IF statements than I did so you can both have a star
HTH
~Geoff~

 

[rmikesmith]

Thanks Geoff!

Revisiting John's initial post, I think you may be correct regarding Excel Formula vs. VBA. In that case, I think we have covered all the bases

Mike

 

[JohnCHolmes]

Yes, I sort of slipped in two bases, and thanks for responding to both. Let me capitalize on this hybridization!

As to VLOOKUP, below is a crude replacement. It could be outfitted with MATCH, but is that really any faster than VLOOKUP? It's just a offset and dereference away from VLOOKUP, who toils while doing the (identical, right?) comparison part. A.F.A. INDEX goes, I'm confused and clueless with it, but I think I can say that it's not like the performance technique of database indexing.

As to the code below it is slug-paced VBA looping for comparison, so it's not any solution (though it does allow negative column offset). What it really needs is .Find, but .Find won't work in a function, only a sub (and function calling sub doesn't fool it).

vlookup bites whether ,TRUE or ,FALSE. I'd be grateful
if xlbo or any of you can come up with something better.
----------------------------------------------------
Option Explicit 'Code needing help!
Function MyStringVlookup(sStr As String, rangeWhere As Range, iColOffset As Integer)'or int or double version
Dim rangeWhere2 As Range, c As Range

'NOTE! For performance, *ONLY* supply 1 column to the function - e.g. ("X",A1:A9,3), NOT ("X",A1:E9,3)

'Remember that the last arg (iColOffset) is 1-based, like idiotic vlookup

'Set rangeWhere2 = rangeWhere.Columns(1)
For Each c In rangeWhere 'rangewhere2 doesn't work! JEEZ!
If c.Value = sStr Then
'Debug.Print c.Offset(0, iColOffset - 1).Value
MyVlookupAlpha = c.Offset(0, iColOffset - 1).Value
Exit Function
End If
Next c
MyVlookupAlpha = 0 'consider "" in another variation, etc.
End Function

 

[JohnCHolmes]

Skip I like your point. What I am doing is taking from A3 to the end of the sheet, selecting it, reducing the selection by one row (see my top post here), copying, going to another sheet, and pasting to cell A3, then sorting that new (i.e., the pasted) selection (for a later slow

http://www VLOOKUP

- GRRR).

I realize that it would be an insult to ask you to respond, considering the hardcoded A3 (and I promise, I don't use AOL either!), but this would be a great place to show off those techniques.

Thanks to you and all of the cool tipsters on this thread.

 

[SkipVought]

John,
Why are "we" duplicating data (copy to another sheet, etc...)?

Can't the sorting and lookup be done on the ORIGINAL sheet?

Personally, I rarely use V & H Lookup. I might use MATCH instead of VLOOKUP, along with INDEX to access other data in a row. But you can do alot of that with range references. So if tbData defines the data in a table, then...

   tbData(row, col)

defines a particular cell in the table, where...

   row = Application.Match(LookupVal,ColumnRange,0)

Hope this helps
Skip,
SkipAndMary1017@mindspring.com

 

[JohnCHolmes]

[Apologies as this has drifted from the subject, but has yielded some tasty ideas]

Skip,
It seems that far too often in Newsgroups, and sometimes here at Tek-Tips, rather than getting answers to very CarefullyAndExplicitlyDevelopedQuestions, there are DistractingAndOffThePointAttackOfMETHODS. I can't count how many technical threads have swelled to inefficiency, or even derailed from that. [ Man, I feel like had I not spent my limited time to jump off the track to explain "A3" above, someone would have devoted a post to "why hardcode?" and I'm just waiting for a "why aren't you using defined name ranges?" ]

Just the same, I'll take the wisdom of your advice in a constructive spirit, and hope that it benefits others too. Duh - duplicity of data has disadvantages - duh. I never espoused it - I just decided to skip spending another 10 minutes and more typing to explain the design requirement - and the very nifty ADVANTAGES of that duplication - but just trust me on this one

Like the first sentence of my last post, I'd like to pay homage to your original idea of not using selection. I have the live example chomping for exactly that. I don't know that range references and Application.Match solve it but I'll mess around with them. Thanks for the inspiration. Meanwhile, here's the code that's replete with selections, if you see a chance to tighten them with those clever substitutions.


'This'll fit ur screen fine after pasting into VBA
'Clear the destination on Sorted
Sheets("SortedByLOB&quot.Select
Range("A3&quot.Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
Selection.Clear ' better because existing references to it are not invalidated!

'Grab unsorted's cell A3 to the end, copy to "SortedByLOB"
Sheets("RawData&quot.Select
'Cells.Select 'would select whole sheet
Application.Goto Reference:="R3C1" 'cell a3
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy 'Mark and copy everything from a3 to the end of the w/s
Sheets("SortedByLOB&quot.Select
Range("A3&quot.Select
'paste
ActiveSheet.Paste ' and the following resizing of all but last row applies to paste area
lR = Selection.Rows.Count: lC = Selection.Columns.Count
Selection.Resize(lR - 1, lC).Select
'Finally sort by column J then K ... company, then LOB
Selection.Sort Key1:=Range("J3&quot, Order1:=xlAscending _
, Key2:=Range("K3&quot, Order2:=xlAscending _
, Header:=xlNo, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom
'And this _is_ some really nasty hardcoding ... try making
'cell J3 one range and K3 the other

 

[xlbo]

Hi John - here's something you might wanna change:
Sheets("RawData&quot.Select
'Cells.Select 'would select whole sheet
Application.Goto Reference:="R3C1" 'cell a3
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy 'Mark and copy everything from a3 to the end of the w/s
Sheets("SortedByLOB&quot.Select
Range("A3&quot.Select
'paste
ActiveSheet.Paste ' and the following resizing of all but last row applies to paste area

This can be replaced by:
lRow = Sheets("RAWData&quot.Range("A3&quot.SpecialCells(xlLastCell).Row
lCol = Sheets("RAWData&quot.Range("A3&quot.SpecialCells(xlLastCell).Column
Sheets("RAWData&quot.Range(Cells(3, 1), Cells(lRow, lCol)).Copy Destination:=Sheets("SortedByLOB&quot.Range("A3&quot

watch the line wrap - there are only 3 lines here

This should speed up the copy no end - no selection is involved at all. The only criteria is that "RAWData" needs to be the activesheet

HTH
~Geoff~

 

[SkipVought]

John,
Here's some fairly tight code that does the trick...

    For i = 2 To 1 Step -1
        With Sheets(i)
            Set rng1 = Range(.Cells(4, 1), .Cells(4, 1).End(xlToRight))
            Select Case i
                Case 1
                    .Range(rng1, rng1.End(xlDown)).Copy _
                        Destination:=Sheets(2).Cells(4, 1)
                Case 2
                    Range(rng1, rng1.End(xlDown)).Clear
            End Select
        End With
    Next
[\code]
hope this helps  :-) Skip,
SkipAndMary1017@mindspring.com