Using a Range variable to call a procedure with a ByVal statement...

[R Tanner]

Hi,

I am trying to call a procedure and I can't figure out how to do it
correctly. This is my call statement.

WorkBook_Open
(filename:=Range("O2").Value,cutrange:=range("O5").address)

The following is the procedure I am trying to call:

Private Sub WorkBook_Open(ByVal FileName As String, ByVal CutRange As
Range)

 

[JP]

The Workbook_Open event doesn't take any arguments. It is placed in
the ThisWorkbook module for a workbook and runs when that workbook is
opened. Where did you come up with that code?

--JP

 

[R Tanner]

The Workbook_Open event doesn't take any arguments. It is placed in
the ThisWorkbook module for a workbook and runs when that workbook is
opened. Where did you come up with that code?

--JP





- Show quoted text -

Okay I changed the name to just Book_Open. It still doesn't work. I
want to pass two variables to this Book_Open procedure....what am I
doing wrong?

 

[Bob Phillips]

It isn't called Book_Open, it is called Workbook_Open, but it doesn't have
any arguments, it is an event procedure that runs automatically when you
open the workbook. To open a workbook, you just run

Workbooks.Open Filename:="some file.xls"

--
__________________________________
HTH

Bob

The Workbook_Open event doesn't take any arguments. It is placed in
the ThisWorkbook module for a workbook and runs when that workbook is
opened. Where did you come up with that code?

--JP





- Show quoted text -

Okay I changed the name to just Book_Open. It still doesn't work. I
want to pass two variables to this Book_Open procedure....what am I
doing wrong?

 

[JLGWhiz]

If you are trying to ope a workbook then:

Workbooks.Open filename:=Range("O2").Value

I assume the Range("O2") has the path and file name. I don't know what the
argument cutrange means. It is not in the standard list of arguments for
workbooks.open

 

[R Tanner]

It isn't called Book_Open, it is called Workbook_Open, but it doesn't have
any arguments, it is an event procedure that runs automatically when you
open the workbook. To open a workbook, you just run

    Workbooks.Open Filename:="some file.xls"

--
__________________________________
HTH

Bob





Okay I changed the name to just Book_Open.  It still doesn't work.  I
want to pass two variables to this Book_Open procedure....what am I
doing wrong?- Hide quoted text -

- Show quoted text -

Okay sorry...I should have given more details...


I have a separate procedure that is creating a filesystemobject and
then running through every file in a folder and checking to see if
there is a file with a specific name. If there is not, it creates it,
copies data into it, and then closes it. If there is, then it opens
it, appends data to the end of the file, and closes it, saving
changes. I have two arguments I want to pass to this procedure. One
is the file name to check for. The second argument I want to pass to
it is the RangeToCopy...Here is my code...

Sorry, I didn't mean to make you think I was trying to use the
Workbook_Open Procedure...I have never used that so I didn't recognize
the fact that it is already designated for something else...I am
simply trying to modify a custom procedure that opens/creates a
specific workbook based on two variables I pass to it...


Private Sub Sheet_Open(ByVal FileName As String, ByVal CutRange As
Range)


Dim RTA As Range
Dim RTC As Range
Dim Directory As String
Dim D As String
Dim FolderPath As String
Dim ObjFSO As Object
Dim ObjectFolder As Object
Dim ColFiles As Object
Dim ObjFile As Object
Dim T As Integer

Application.DisplayAlerts = False

D = Date
D = Application.WorksheetFunction.Substitute(D, "/", ".")
T = 0

FolderPath = "Q:\Operations\Spread Data\"

Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Set objfolder = ObjFSO.getfolder(FolderPath)
Set ColFiles = objfolder.Files
FileName = FileName & " " & D & ".xlsx"

For Each ObjFile In ColFiles
Select Case ObjFile.Name
Case Is = FileName
Workbooks.Open FileName:=FolderPath & FileName
Set RangeToAppend =
Workbooks(FileName).Sheets(1).Range("B2").End(xlDown).Offset(1, 0)
Workbooks("DDE-Sample.xlsm").Activate
Set RangeToCopy = Range(CutRange, CutRange.End(xlDown))
Set RangeToCopy = Range(RangeToCopy, RangeToCopy.Offset(0,
4))
RangeToCopy.Cut
Workbooks(FileName).Activate
RangeToAppend.Select
ActiveSheet.Paste
Workbooks(FileName).Close savechanges:=True,
FileName:=FolderPath & FileName
T = 1

End Select
Next

Select Case T
Case Is = 0
Workbooks.Add
ActiveWorkbook.SaveAs FileName:=FolderPath & FileName
Set RangeToAppend = Workbooks(FileName).Sheets(1).Range("B2")
Workbooks("DDE-Sample.xlsm").Activate
Set RangeToCopy = Range(CutRange, CutRange.End(xlDown))
Set RangeToCopy = Range(RangeToCopy, RangeToCopy.Offset(0, 4))
RangeToCopy.Cut
Workbooks(FileName).Activate
RangeToAppend.Select
ActiveSheet.Paste
Workbooks(FileName).Sheets(1).Columns("B:F").Select

Workbooks(FileName).Sheets(1).Columns("B:F").EntireColumn.AutoFit
Workbooks(FileName).Sheets(1).Cells.Font.Size = 10
Workbooks(FileName).Close savechanges:=True,
FileName:=FolderPath & FileName
End Select

Application.DisplayAlerts = True


End Sub

 

[JLGWhiz]

The Workbook_Open method has to be in the ThisWorkbook code module. Press
Alt + F11 to open the VB editor, in the top left panel you will see
ThisWorkbook. Right click on that name and click View Code from the drop
down menu. Then, in the small Declarations window at the top right of the
code pane, select open, and the first and last lines of the Workbook_Open
code will appear in the code window. You can then insert your ByVal or ByRef
variables into the parentheses and fill in the rest of the code between those
two lines of code that VBA furnished.

 

[JLGWhiz]

I am trying to call a procedure and I can't figure out how to do it
correctly.

To call a procedure:

Sub callProc()
Call ProcedureName 'ProcedureName is everything between Sub and ().
End Sub

Opening a workbook in not calling a procedure.
Calling a procedure is not opening a workbook.
Workbook_Open is an event procedure and not a method to open workbooks or to
call other procedures.

It helps us if you can use the correct terminology to describe your problem,
but when you are just beginning, we understand if you don't.

 

[R Tanner]

I am trying to call a procedure and I can't figure out how to do it
correctly.

To call a procedure:

Sub callProc()
    Call ProcedureName  'ProcedureName is everything between Sub and ().
End Sub

Opening a workbook in not calling a procedure.
Calling a procedure is not opening a workbook.
Workbook_Open is an event procedure and not a method to open workbooks orto
call other procedures.

It helps us if you can use the correct terminology to describe your problem,
but when you are just beginning, we understand if you don't.






- Show quoted text -

Yes, I thought I had made myself clear when I said I was sorry for
getting Workbook_Open confused with what I am trying to do. I am
using this procedure to open a workbook. Period. How do I pass a
range argument to it, as I have previously outlined.

Again...

What do I need to change about this syntax?

BOpen(filename:=Range("O2").Value,cutrange:=range("O5").address)


If this is the procedure I am trying to call...

Private Sub BOpen(ByVal FileName As String, ByVal CutRange As
Range)

 

[JP]

Why use a loop to see if a file exists in a folder? You can use the
Dir function, or if you want to use FSO, use the FileExists method
(returns True if a file exists).

You are confusing the rest of us (and maybe also confuse Excel itself)
by using reserved words for your sub name (Workbook_Open, Sheet_Open).
You should give your subs names that describe their purpose.

i.e.

Sub Find_And_Open(ByVal FileName As String, ByVal CutRange As Range)
Sub UpdateWorkbooks(ByVal FileName As String, ByVal CutRange As Range)

Also I believe ByVal is the default for arguments, you don't need to
include it with every argument. Subs may be sped up by passing ranges
ByRef. (My memory of "Professional Excel Development" is shaky right
now, someone else correct me if I'm wrong on either of these counts).

To call a procedure with arguments, you have to pass arguments to it
(in parenthesis) that are the same type as the variable type you
specified. You don't include the variable names in the arguments, as
you did in your first post. So if your sub is named

Private Sub Sheet_Open(ByVal FileName As String, ByVal CutRange As
Range)

Then you call it from another macro like this:

Sheet_Open("MyFile.xls", Range("A1"))

You can use variables that represent these, as long as the variables
are the same type. For example

Dim strFileN As String
Dim rng As Excel.Range

strFileN = "MyFile.xls"
Set rng = Range("A1")

Sheet_Open(strFileN, rng)



HTH,
JP

 

[JP]

":=" (the colon followed by equal sign) is used when assigning a value
to a property of an object, you don't use it in the argument for a
sub.

So instead of

BOpen(filename:=Range("O2").Value,cutrange:=range("O5").address)

it should be

BOpen(Range("O2").Value,Range("O5").Address)


--JP

 

[R Tanner]

Why use a loop to see if a file exists in a folder? You can use the
Dir function, or if you want to use FSO, use the FileExists method
(returns True if a file exists).

You are confusing the rest of us (and maybe also confuse Excel itself)
by using reserved words for your sub name (Workbook_Open, Sheet_Open).
You should give your subs names that describe their purpose.

i.e.

Sub Find_And_Open(ByVal FileName As String, ByVal CutRange As Range)
Sub UpdateWorkbooks(ByVal FileName As String, ByVal CutRange As Range)

Also I believe ByVal is the default for arguments, you don't need to
include it with every argument. Subs may be sped up by passing ranges
ByRef. (My memory of "Professional Excel Development" is shaky right
now, someone else correct me if I'm wrong on either of these counts).

To call a procedure with arguments, you have to pass arguments to it
(in parenthesis) that are the same type as the variable type you
specified. You don't include the variable names in the arguments, as
you did in your first post. So if your sub is named

Private Sub Sheet_Open(ByVal FileName As String, ByVal CutRange As
Range)

Then you call it from another macro like this:

Sheet_Open("MyFile.xls", Range("A1"))

You can use variables that represent these, as long as the variables
are the same type. For example

Dim strFileN As String
Dim rng As Excel.Range

strFileN = "MyFile.xls"
Set rng = Range("A1")

Sheet_Open(strFileN, rng)

HTH,
JP




- Show quoted text -

Okay great thank you for your response...I tried to pass the arguments
as follows and it tells me: Compile Error Expected: =

If BID1.Offset(I, 0).Row = 20000 Then
cutrange(range("O2").value,range("O5"))

I renamed the procedure to the following to not confuse anyone....

Sub cutrange(ByVal FileName as String, ByVal CR as Range)

 

[JLGWhiz]

This is from VBA help files and substantiates JP's comments.

Calling Sub Procedures with More than One Argument
The following example shows two ways to call a Sub procedure with more than
one argument. The second time HouseCalc is called, parentheses are required
around the arguments because the Call statement is used.

Sub Main()
HouseCalc 99800, 43100 'Without the Call keyword
Call HouseCalc(380950, 49500) 'Does the same thing
End Sub

Sub HouseCalc(price As Single, wage As Single) 'Called procedure
If 2.5 * wage <= 0.8 * price Then
MsgBox "You cannot afford this house."
Else
MsgBox "This house is affordable."
End If
End Sub

 

[JP]

See the last email in the thread from JLGWhiz -- if you call a macro
from within another macro without the word "Call" then you have to
drop the parentheses.

Try:

If BID1.Offset(I, 0).Row = 20000 Then
cutrange range("O2").value,range("O5")


--JP

 

[Bob Phillips]

Not necessarily. If you include parentheses then the expression inside is
evaluated before being passed.

--
__________________________________
HTH

Bob

See the last email in the thread from JLGWhiz -- if you call a macro
from within another macro without the word "Call" then you have to
drop the parentheses.

Try:

If BID1.Offset(I, 0).Row = 20000 Then
cutrange range("O2").value,range("O5")


--JP