Call a procedure kinda "indirectly"

[dean12]

I've got a module with two procedures. Procedure 1 calls procedure 2 and passes a worksheet reference. Procedure 2 does some work and then wants to call a procedure which is stored as code on the worksheet. Specifically sheetA has a routine called Format and sheetB has the same thing.

The whole thing looks like this:

In the module
-------------
sub proc1
call proc2(sheetA)
call proc2(sheetB)
end sub

sub proc2 (byref ws as worksheet)
call ws.Format
end sub

In sheetA code
--------------
sub Format
end sub

In sheetB code
--------------
sub Format
end sub


Unfortunately Excell refuses to run and claims that the statement:

call ws.Format

is invalid. The compiler message being:

Method or Data Member not found.


Any thoughts would be appreciated.

 

[GlennUK]

Strange. I can get it to work by doing

Call Sheets(ws.Name).Format

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[combo]

Is 'format' public?

Sub proc1
CallByName ws, "format", VbMethod
End Sub

combo

 

[fumei]

It also may be better to give unique names to procedures.

faq219-2884

Gerry
My paintings and sculpture

 

[strongm]

This is one of those occassions where the handholding that VBA does for you can get in the way. The error message is correct; the normal Worksheet interface does not have a Format Method or property, and that's the interface you are passing)

Change

sub proc2 (byref ws as worksheet)

to

Sub proc2(ByRef ws As Object)

Now, thanks to late binding, we'll get the default interface of the object we are passing, which in this case includes the new Format method that you have introduced

 

[dean12]

Sub sub1()

Call sub2(Sheet1)
Call sub2(sheet2)

End Sub

Sub sub2(ByRef ws As Object)

Call ws.foobar("string 1")

End Sub



Yes "strongm" it does work. I could swear that I tried this strategy (object versus worksheet) as this trick is fairly common and it did not work for me. I guess i screwed up when i tried it.

Hey, my thanks to everyone that responded on this. The "call sheets(....)" approach works as well. With some 6 to 8 worksheets to manipulate having to approach this with case statements so I could hand code different worksheet names on 6 to 8 different call statements was getting out of control. So as we would say in Texas: All Yawll did real good helping out. Thanks again.