werafa
10-15-2018, 04:43 PM
Hi all,
I'm scratching my head on this one, and hope someone can say why it is so.
I have always (in the past) passed objects and variables to sub-procedures using this syntax
Sub Master()
dim myWB as workbook
DoSomethingSub(myWB)
more code etc.
end sub
Sub DoSomethingSub(sourceWB as workbook)
.....
End Sub
This is failing in the project I am currently working on (object doesn't support this property or method), and I am being forced to write the calls with the syntax:
DoSomethingSub SourceWB:=MyWB
which works fine
Does anyone know why this should be so?
the actual code is pasted below, and the procedure calls in 'CloseSrcWB' work fine
Thanks in advance
Werafa
Public Sub UpdateData()
Dim sArr(1 To 2) As String
Dim lLoop As Long
Dim sourceRange As Range
Dim sourceWB As Workbook
Dim arrSourceData() As Variant
sArr(1) = "MCG"
sArr(2) = "FNW"
DeleteOldData
'load new data
For lLoop = 1 To 2
Set sourceRange = OpenSourceData(sArr(lLoop))
arrSourceData = SelectSourceData(sourceRange)
Set sourceWB = sourceRange.Parent.Parent
CloseSrcWB myWB:=sourceWB
WriteDataToTable dataArray:=arrSourceData
Next lLoop
End Sub
Public Sub CloseSrcWB(myWB As Workbook)
' ----------------------------------------------------------------
' Procedure Name: CloseSrcWB
' Purpose: Close Source data WB and reset application environment parameters
' Procedure Kind: Sub
' Procedure Access: Public
' Parameter sourceWB (Workbook): WB to be closed
' ----------------------------------------------------------------
Call SilentClose(myWB)
Call SilentOpen(False)
End Sub
I'm scratching my head on this one, and hope someone can say why it is so.
I have always (in the past) passed objects and variables to sub-procedures using this syntax
Sub Master()
dim myWB as workbook
DoSomethingSub(myWB)
more code etc.
end sub
Sub DoSomethingSub(sourceWB as workbook)
.....
End Sub
This is failing in the project I am currently working on (object doesn't support this property or method), and I am being forced to write the calls with the syntax:
DoSomethingSub SourceWB:=MyWB
which works fine
Does anyone know why this should be so?
the actual code is pasted below, and the procedure calls in 'CloseSrcWB' work fine
Thanks in advance
Werafa
Public Sub UpdateData()
Dim sArr(1 To 2) As String
Dim lLoop As Long
Dim sourceRange As Range
Dim sourceWB As Workbook
Dim arrSourceData() As Variant
sArr(1) = "MCG"
sArr(2) = "FNW"
DeleteOldData
'load new data
For lLoop = 1 To 2
Set sourceRange = OpenSourceData(sArr(lLoop))
arrSourceData = SelectSourceData(sourceRange)
Set sourceWB = sourceRange.Parent.Parent
CloseSrcWB myWB:=sourceWB
WriteDataToTable dataArray:=arrSourceData
Next lLoop
End Sub
Public Sub CloseSrcWB(myWB As Workbook)
' ----------------------------------------------------------------
' Procedure Name: CloseSrcWB
' Purpose: Close Source data WB and reset application environment parameters
' Procedure Kind: Sub
' Procedure Access: Public
' Parameter sourceWB (Workbook): WB to be closed
' ----------------------------------------------------------------
Call SilentClose(myWB)
Call SilentOpen(False)
End Sub