Problem passing by reference

  • Thread starter Thread starter Ted
  • Start date Start date
T

Ted

I am passing an integer variable into a function. In the
function I am changing the value of the variable. However,
the variable is not changing in the calling function.

I have specifically declared the variable as an integer in
the calling function, and I have specified byRef in the
function being called. However, the value in the calling
function still does not change.

I have tried a couple different variation without success.
Can anyone help me?

Ted
 
Ted,

Post the code for us.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
It should work as you describe it. Please post code that illustrates your
dilemma.
Bob
 
There are issues with passing parameters with or without barckets/Call
statement.

NickHK
 
Not issues, syntax, but anyway that would not appear to be the problem as
Ted knows the variable doesn't change when he calls the function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,
So he thinks it's being passed ByRef, but really it's being passed ByVal.

NickHK
 
Nick,

He may have declared the same name variable in his function. Who knows,
until we see the code.

Bob
 
The code is below. The problem is that the rowNumber
variable never changes in the OprenReviewFiles procedure
even though I increment it in the ImportManagerReview.

Thanks for the help!

Sub OpenReviewFiles()
'
' OpenReviewFiles Macro
' Macro recorded 2/18/2004 by Ted Lapina
'

'Declare variables
Dim fs, f, f1, fc, s
Dim rowNumber As Integer

rowNumber = 2

'Select the directory where files will be imported
from.
folderspec = Application.InputBox("Please enter the
directory that contains the required filed.", "Enter
Directory Name", "f:\syd\mis\ReviewTest\")
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files

'Iterate through the files in a directory.
For Each f1 In fc
Workbooks.Open (folderspec & "\" &
f1.Name) 'Open the file
Module1.ImportManagerReview
(rowNumber) 'Run the macro
Workbooks
(f1.Name).Close 'Close the file
Next

End Sub


Function ImportManagerReview(ByRef rowNumber As Integer)
'
' ImportManagerReview Macro
' Macro recorded 2/18/2004 by Ted Lapina
'
'Import data from either the Manager Review or Peer
Review sheets.
If Range("A1").Value = "Manager Review" Then
For importRow = 6 To 15
'Copy first data row.
With Worksheets("Manager Review")
.Range(.Cells(importRow, 1), .Cells
(importRow, 52)).Copy
End With
'Paste first data row into summary spreadsheet.
With ThisWorkbook.Worksheets("Manager Summary")
.Range(.Cells(rowNumber, 1), .Cells
(rowNumber, 52)).PasteSpecial (xlPasteValues)
End With

rowNumber = rowNumber + 1
Next

Else 'Process Peer Review

End If

End Function
 
The code is below. The problem is that the rowNumber
variable never changes in the OprenReviewFiles procedure
even though I increment it in the ImportManagerReview.

Thanks for the help!

Sub OpenReviewFiles()
'
' OpenReviewFiles Macro
' Macro recorded 2/18/2004 by Ted Lapina
'

'Declare variables
Dim fs, f, f1, fc, s
Dim rowNumber As Integer

rowNumber = 2

'Select the directory where files will be imported
from.
folderspec = Application.InputBox("Please enter the
directory that contains the required filed.", "Enter
Directory Name", "f:\syd\mis\ReviewTest\")
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files

'Iterate through the files in a directory.
For Each f1 In fc
Workbooks.Open (folderspec & "\" &
f1.Name) 'Open the file
Module1.ImportManagerReview
(rowNumber) 'Run the macro
Workbooks
(f1.Name).Close 'Close the file
Next

End Sub


Function ImportManagerReview(ByRef rowNumber As Integer)
'
' ImportManagerReview Macro
' Macro recorded 2/18/2004 by Ted Lapina
'
'Import data from either the Manager Review or Peer
Review sheets.
If Range("A1").Value = "Manager Review" Then
For importRow = 6 To 15
'Copy first data row.
With Worksheets("Manager Review")
.Range(.Cells(importRow, 1), .Cells
(importRow, 52)).Copy
End With
'Paste first data row into summary spreadsheet.
With ThisWorkbook.Worksheets("Manager Summary")
.Range(.Cells(rowNumber, 1), .Cells
(rowNumber, 52)).PasteSpecial (xlPasteValues)
End With

rowNumber = rowNumber + 1
Next

Else 'Process Peer Review

End If

End Function
 
The problem is that you are enclosing the argument in
parentheses. This forces the variable to be passed ByVal instead
of ByRef. Change

Module1.ImportManagerReview (rowNumber)
to
Module1.ImportManagerReview rowNumber


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Back
Top