PDA

View Full Version : Solved: Pass Variable Reference



YellowLabPro
09-28-2007, 04:56 AM
I have to subs. The main calls a secondary sub, Sub getname().
The secondary's job is to retrieve the active workbook's name and pass it back to the Main sub.
The secondary sub is ending, losing the variable.
How do I pass the variable back from the secondary sub to the main sub?

variable i want to pass is wbsource


Sub CopyRange()
Dim wbsource As Workbook, wbTarget As Workbook
Dim wsSource As Worksheet, wsTarget As Worksheet
Dim lrSource As Long
Dim wbnt As String, wsnt As String, wPath As String

Call getname
'wbns =
wbnt = "Book1.xls" '"TGSItemRecordCreator.xls"
wsnt = "Test" '"RecordCreator"
wPath = "C:\Documents and Settings\Doug\Desktop\Excel Projects\"
'Set wbTarget = Workbooks(wbn)
'Set wsTarget = Workbooks(wbn).Worksheets(wsn)

lrSource = LR(ActiveSheet, "H")
If IsWbOpen(wbnt) Then
Set wsTarget = Workbooks(wbnt).Worksheets(wsnt)
Else
If Not (Dir(wPath & wbnt) = "") Then
Workbooks.Open (wPath & wbnt)
Set wsTarget = Workbooks(wbnt).Worksheets(wsnt)
Else
MsgBox ("The Workbook """ & wbnt & """ Does Not Exist" & vbCrLf & vbCrLf & "In The " & wPath & " Folder"), vbCritical
Exit Sub
End If
End If

wsTarget.Range("H6:S" & lrSource).Value = wbsource.Range("H6:S" & lrSource).Value
wsTarget.Range("Z6:AC" & lrSource).Value = wbsource.Range("Z6:AC" & lrSource).Value
Exit Sub



End Sub
Sub getname()
Dim wbsource As Workbook
Dim wsSource As Worksheet
Debug.Print ActiveWorkbook.Name
Set wbsource = ActiveWorkbook
Debug.Print wbsource.Name
End Sub

rory
09-28-2007, 05:02 AM
I don't really see the point of the second sub, so I assume this is just theory? Make the sub into a Function that returns a workbook:
Function GetBook() As Workbook
Set GetBook = ActiveWorkbook
End Function

then your first sub just uses:
set wbSource = GetBook()

You could also move the Dim wbsource As Workbook line to the declarations section of the module, and remove the declaration from both procedures.

YellowLabPro
09-28-2007, 06:51 AM
Rory,
Actually your remark: "I don't see the point".... helped me rethink this through.
The information was already there I just did not see it. Thanks