passing objects
 

I have run into problems with passing objects between procedures. When I
select range in one procedure, save it to local variable in prep_range() and
use that variable as an argument for another function VB suddenly converts it
to array (if argument type is not required), if object type is required it
throws object required error. See code. Why does it try to convert it instead
of passing it as an object?

many thanks.

Function s_setCols() As Object
//selects columns
Dim a As Object
Set a = Application.Union(ActiveSheet.Columns("B:F"), _
ActiveSheet.Columns("H:I"), _
ActiveSheet.Columns("K:N"), _
ActiveSheet.Columns("P:Q"))
a.Select
Set s_setCols = a
End Function

Sub s_delCols(rng As Object)
// deletes columns
Selection.Delete Shift:=xlToLeft
End Sub

Sub prep_range()
//calls procedures
Dim rng As Object
s_init
Set rng = s_setCols
// it throws an error 'object required' here, it basically tries to
convert object into array
s_delCols (rng)
End Sub

passing objects
 

Remove the "( )" around rng in Sub prep_range() so the call reads...

s_delCols rng
-or-
Call s_delCols (rng)

See the "Call Statement" and "Using Parentheses in Code" in help for more info.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Bobby Gontarski"
wrote in message
I have run into problems with passing objects between procedures. When I
select range in one procedure, save it to local variable in prep_range() and
use that variable as an argument for another function VB suddenly converts it
to array (if argument type is not required), if object type is required it
throws object required error. See code. Why does it try to convert it instead
of passing it as an object?

many thanks.

Function s_setCols() As Object
//selects columns
Dim a As Object
Set a = Application.Union(ActiveSheet.Columns("B:F"), _
ActiveSheet.Columns("H:I"), _
ActiveSheet.Columns("K:N"), _
ActiveSheet.Columns("P:Q"))
a.Select
Set s_setCols = a
End Function

Sub s_delCols(rng As Object)
// deletes columns
Selection.Delete Shift:=xlToLeft
End Sub

Sub prep_range()
//calls procedures
Dim rng As Object
s_init
Set rng = s_setCols
// it throws an error 'object required' here, it basically tries to
convert object into array
s_delCols (rng)
End Sub

passing objects
 

Thaks a lot

"Jim Cone" wrote:


Remove the "( )" around rng in Sub prep_range() so the call reads...

s_delCols rng
-or-
Call s_delCols (rng)

See the "Call Statement" and "Using Parentheses in Code" in help for more info.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Bobby Gontarski"
wrote in message
I have run into problems with passing objects between procedures. When I
select range in one procedure, save it to local variable in prep_range() and
use that variable as an argument for another function VB suddenly converts it
to array (if argument type is not required), if object type is required it
throws object required error. See code. Why does it try to convert it instead
of passing it as an object?

many thanks.

Function s_setCols() As Object
//selects columns
Dim a As Object
Set a = Application.Union(ActiveSheet.Columns("B:F"), _
ActiveSheet.Columns("H:I"), _
ActiveSheet.Columns("K:N"), _
ActiveSheet.Columns("P:Q"))
a.Select
Set s_setCols = a
End Function

Sub s_delCols(rng As Object)
// deletes columns
Selection.Delete Shift:=xlToLeft
End Sub

Sub prep_range()
//calls procedures
Dim rng As Object
s_init
Set rng = s_setCols
// it throws an error 'object required' here, it basically tries to
convert object into array
s_delCols (rng)
End Sub