PDA

View Full Version : Solved: select or activate fail when code is active?



TheAntiGates
11-20-2007, 08:08 PM
Anyone have a reason why range("a1").select fails during code execution? If I stop code, no problem.

I know when selecting is inadvisable and the non-optimized nature of the code below, and don't need help whether or not to even try this command. I just need to understand why the command is not allowed to run. As shown in comments below, there are errors that let you click debug/end. But if I make one change, I don't even get that (see sentence after code).

Perhaps it's a corrupt file. I rebuilt 90% of it but did copy buttons and a few things from a prior file rather than go 100.0% from scratch.sub foo
ActiveWorkbook.Names.Add Name:="Criteria", RefersToR1C1:="=MyCrit2" 'comment this?
Sheets("tempo").Activate
Application.Goto Reference:="tempo!R1C1"
'any of the following go boom
Range(Selection, Selection.End(xlToRight)).Select
' "method 'Range' of object '_Worksheet' failed"
Range("b2").Select
' "Select method of range class failed"
Range("a1").Select
' "Select method of range class failed"
End SubIf I comment the line that says "comment this?", then I get a full bomb on any attempt to select or activate, via our friend "Runtime Error 1004: You guess the problem. We don't care. We're the phone company." Execution completely stops - no yellow highlighted line in break mode is available.

TheAntiGates
11-20-2007, 08:23 PM
Okay, forget corruption. Start a new workbook, and stick this code on sheet1 and run it. Are you forbidden to navigate Sheet2 from sheet 1 code? Would calling a module1 routine from [button on] sheet1 get around this? I want to select A1:D1 on sheet2 from sheet1's button.Private Sub foo()
Sheets("Sheet2").Activate
Application.Goto Reference:="Sheet2!R1C1"
Range("A1").Select
End Sub

TheAntiGates
11-20-2007, 08:34 PM
Okay, I've moved on - I resolved it with
Application.Goto Reference:="MyRange"

How about this: what I REALLY want to do is hit a button on Sheet1 and do a filter landing on Sheet2, a la
Range("database").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("criteria"), Unique:=False, _
CopyToRange:=Range("CopyHeadings")

Where Database and Criteria are on Sheet1, and CopyHeadings is on sheet2.

Since this is not working, I'm trying to select the heading row on sheet2
Application.Goto Reference:="copyHeadings"
in light of
"XL: Error Message: Can Only Copy Filtered Data to Active Sheet"
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q117643

I'm stuck. Can someone please tweak the code so it will work?

mikerickson
11-20-2007, 08:39 PM
Each sheet of a workbook is a different VBProject Component. Routines that deal with more than one component work best if they are in a normal module rather than a specific component's code module. Since your code is in the module for sheet1, it gets "confused" once Sheet2 is activated.

Put that routine in a normal module. If you want to use it with an event routine (which must be in the sheet's code module), this technique is used.


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Call otherRoutine: Rem otherRoutine in normal module
End Sub

Did that help?

mikerickson
11-20-2007, 08:43 PM
We simul-posted.
Put your routine in a normal module and add the line

Sheets("Sheet2").Activatesomewhere before the Filter instruction.

If you use a Command Button from the Forms Menu, you can assign your macro to that button directly.
If you are using an ActiveX Button from the toolbox, use this.


CommandButton1_Click()
Call myOtherRoutine
End Sub

TheAntiGates
11-20-2007, 08:46 PM
Yes, sticking the code in Module1 turned my world to cherries. The filtering succeeds.

Actually I left off the word "call" though I don't know if it has any more effect than saving a couple of nanoseconds.

Thank you for the courteous assistance.

mikerickson
11-20-2007, 09:06 PM
It doesn't save any nano-seconds.

Using automatic conversion or default routines is not faster than explicitly declaring them

myString = "xyz" & (1+2)
and

myString = "xyz" & CStr(1+2)both take the same amount of time, since the compiler(?) adds the CStr when executing the first line.

TheAntiGates
11-20-2007, 09:12 PM
Welllll "call" actually returns the instruction pointer to the caller, and not calling doesn't, was my highly insignificant point :) - but good gosh you appear to be right!! Call is assumed! I never imagined that. Whoda thunkit! Crazy VBA!
sheet 1:
Sub foo()
bar
MsgBox "hi"
End Sub

MODULE:
Sub bar()
End Sub

TheAntiGates
11-20-2007, 09:34 PM
As to compiler vs. interpretive, this is mighty fuzzy. My bet would have been on interpreter instead of compiler handling that conversion. Although a compiler would know to do that, I just always thought of BASIC as only using compiling for syntax - since, heck, you can actually change the code at runtime.

However...

This gives identical results, supporting "compiler" as the correct answer to what you earlier posed. (I repeated the first test within the routine to fight memory cache influence)Sub foo()
Dim i As Long, tt As Date
tt = Now
For i = 1 To 10000000
myString = "xyz" & CStr(1 + 2)
Next i
Debug.Print Now - tt
tt = Now
For i = 1 To 10000000
myString = "xyz" & (1 + 2)
Next i
Debug.Print Now - tt
tt = Now
For i = 1 To 10000000
myString = "xyz" & CStr(1 + 2)
Next i
Debug.Print Now - tt
End Sub

rory
11-21-2007, 06:12 AM
If your routine is in a worksheet code module, then any unqualified reference to a range refers to a range on the sheet to which the module belongs, hence your original error. If the routine is in a normal module, then unqualified references refer to the active sheet.

TheAntiGates
11-21-2007, 02:35 PM
That makes perfect sense, and constitute a smart logical standard to live by. But having
Range(Selection, Selection.End(xlToRight)).Select
die (and in its ignominious and INCONSISTENT fashion as described atop) makes me question if things are really built that cleanly!