Subroutine Arguments

  • Thread starter Thread starter Ray Batig
  • Start date Start date
R

Ray Batig

I am obviously missing something since I can't get this to work. I am
building a subroutine that will work on several worksheets. The Sub would be
initiated by clicking a button on the worksheet. To make it universal, I
need to pass the Worksheet name, the Range to be manipulated, a Range on
another worksheet which holds some data, and a constant. Here is what I have
tried.

On the Worksheet:

Private Sub CommandButton2_Click()
Dim WkSht As String, ANRange As Range, XlSlots As Range, RCol As Long
WkShts = "SheetName"
ANRange = Range("RangeName1")
XlSlots = Range("RangeName2")
RCol = 5
Call BuildReport( WkSht, ANRange, XlSlots, RCol)
End Sub

In Module:
Sub BuildReport( WkSht As String, ANRange As Range, XlSlots As Range, RCol
As Long)

Code
End Sub

Running this code produces a Run-time '1004' Application-defined or
object-defined error.

I have tried several other versions without success. What can I do to fix
this? Thanks in advance for your help!

Ray
 
Hi
some remarks:
- first you should add the line 'Option explicit' to your module as
there is a typo in the line
Long WkShts = "SheetName"
should read
Long WkSht = "SheetName"

For your error: Is RangeName1 a defined name?. If not it won't work.
Also you have to use Set to assign a range object. So in total change
your code to
Private Sub CommandButton2_Click()
Dim WkSht As String, ANRange As Range, XlSlots As Range, RCol As
Long
WkSht = "SheetName"
Set ANRange = Range("RangeName1")
Set XlSlots = Range("RangeName2")
RCol = 5
Call BuildReport( WkSht, ANRange, XlSlots, RCol)
End Sub
 
H Frank,

Thanks for your reply. I always have Option Explicit Set, however, the code
is running on another machine so I retyped it. My 2 thumbs and their 8
friends messed up. Both ranges exist before calling the macro, and there is
data in them. I added in the Set commands and stepped through the macro.
When I try to execute the Set ANRange = Range("RangeName1") code I get the
same error.

Any more suggestions? Thanks again!

Ray
 
Hi Ray
I just tested it again and if I have a Rangename1 as defined name in my
active workbook/sheet it works. Could you please post again the code
you tried after the corrections :-)
 
Assume RangeName1 (and RangeName2) is a defined name. If it does not refer
to the sheet containing the code, then you need to qualifiy it with the
worksheet name. Normally this is not required, but when used in a sheet
module, it is.

assume
Name: RangeName1
Refersto: =Sheet3!$A$1:$A$100

and code is in Sheet2 code module. Then

Set ANRange = Worksheets("Sheet3").Range("RangeName1")
 
Thanks to all who commented. The addition of the Worksheets("Sheet3").
specifier did it.

Ray
 
Back
Top