Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
passing a range from another worksheet
Hi all,
Apologies for this - this is probably quite a basic question, but I'm afraid I can't seem to find the answer, and all the bookshops I'd go to for something to help me out are closed at this time of night. In my defence, I started working with VBA about four hours ago. I've got a subroutine (lifted from another thread in this group) which is adding two ranges. This is great when the ranges are on the same sheet, but VBA doesn't seem to like attemts to pass ranges from other pages. So when I try AddRanges Range("F6:J10"), Range (Sheet2.Range("C4:G8")) calling a subroutine Private Sub AddRanges(RangeOne As Range, RangeTwo As Range) I get a run-time error 1004 - Method "Range" of object '_Worksheet' failed. I've tried playing with the quotes and brackets, but haven't had any success. The Sheet2 reference works fine for a straightforward copy, so is it something about passing this sort of range as a parameter? Thanks for any help, Mike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
passing a range from another worksheet
Without seeing any code it it hard to tell... but here is a little theory.
The range object is a member of the sheet. So one sheet can have multiple ranges, but a range can only span one sheet. As a guess the code tries to do something with both ranges as if they were one big range which they can not possibly be. -- HTH... Jim Thomlinson "Mike O" wrote: Hi all, Apologies for this - this is probably quite a basic question, but I'm afraid I can't seem to find the answer, and all the bookshops I'd go to for something to help me out are closed at this time of night. In my defence, I started working with VBA about four hours ago. I've got a subroutine (lifted from another thread in this group) which is adding two ranges. This is great when the ranges are on the same sheet, but VBA doesn't seem to like attemts to pass ranges from other pages. So when I try AddRanges Range("F6:J10"), Range (Sheet2.Range("C4:G8")) calling a subroutine Private Sub AddRanges(RangeOne As Range, RangeTwo As Range) I get a run-time error 1004 - Method "Range" of object '_Worksheet' failed. I've tried playing with the quotes and brackets, but haven't had any success. The Sheet2 reference works fine for a straightforward copy, so is it something about passing this sort of range as a parameter? Thanks for any help, Mike |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
passing a range from another worksheet
I think you are looking for something like this:
Note: the MsgBox is just there to show you how it works...) Sub AddRanges 'AddRanges Range("F6:J10"), Range (Sheet2.Range("C4:G8")) Dim tot as double tot = worksheetfunction.sum(Sheet1.Range("F6:J10"),Sheet 2.Range("C4:G8")) msgbox tot end sub to make this more generic - you would need to define rng1 as range & rng2 as range than set rng1 =Sheet1.Range("F6:J10") set rng2 =Sheet2.Range("C4:G8") tot = worksheetfunction.sum(rng1,rng2) -- steveB Remove "AYN" from email to respond "Mike O" wrote in message ... Hi all, Apologies for this - this is probably quite a basic question, but I'm afraid I can't seem to find the answer, and all the bookshops I'd go to for something to help me out are closed at this time of night. In my defence, I started working with VBA about four hours ago. I've got a subroutine (lifted from another thread in this group) which is adding two ranges. This is great when the ranges are on the same sheet, but VBA doesn't seem to like attemts to pass ranges from other pages. So when I try AddRanges Range("F6:J10"), Range (Sheet2.Range("C4:G8")) calling a subroutine Private Sub AddRanges(RangeOne As Range, RangeTwo As Range) I get a run-time error 1004 - Method "Range" of object '_Worksheet' failed. I've tried playing with the quotes and brackets, but haven't had any success. The Sheet2 reference works fine for a straightforward copy, so is it something about passing this sort of range as a parameter? Thanks for any help, Mike |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
passing a range from another worksheet
Thanks for that Steve, setting the ranges in range variables did the trick
nicely. One question though - I know VBA doesn't require the declaration of variables, in theory. That said, if I do a set rng = Sheet1.Range("C4:G8") and attempt to pass this across to a Subroutine which is expecting a Range, I get a type mismatch. What type is VBA assigning to rng in this case? As you pointed out, declaring rng as a Range before setting it clears up this problem, but I'm curious to know what it's being created as, if not as a Range. Thanks for the help, Mike -- -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
passing a range from another worksheet
Mike,
rng is being set as a Range object, but only in the module it is defined in. It will not pass to another routine. Excel works best when all variables are properly defined. Improves speed and functioning. Option Explicit at the top of each module forces Excel to only accept defined variables. This helps catch typos and other errors in your code. (it also drives you to distraction when you are first learning how to use VBE) When you use "set" you are defining an object. Excel likes this... But when you use variables between modules you run into minor issues. When you use Dim variable in a module - it won't pass to another module When you use Dim variable outside a macro - it should pass to other macros in the same module. To pass variables to all macros in all modules - use Public variable outside any macro. (I usually do this by creating a module called "MyVariables" and just fill it with Public variables and add notes as to what they are. But be careful - if you create a Public variable, don't dim this variable inside of any module - it can cause Excel to crash. keep on Exceling... -- steveB Remove "AYN" from email to respond "Mike O" wrote in message ... Thanks for that Steve, setting the ranges in range variables did the trick nicely. One question though - I know VBA doesn't require the declaration of variables, in theory. That said, if I do a set rng = Sheet1.Range("C4:G8") and attempt to pass this across to a Subroutine which is expecting a Range, I get a type mismatch. What type is VBA assigning to rng in this case? As you pointed out, declaring rng as a Range before setting it clears up this problem, but I'm curious to know what it's being created as, if not as a Range. Thanks for the help, Mike -- -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing a Range | Excel Programming | |||
Passing a range into a VBA function | Excel Programming | |||
passing range to c# | Excel Programming | |||
Passing range as argument | Excel Programming | |||
Passing range to subprocedure - maybe? | Excel Programming |