CODEWITHSUNDEEP
functionvbareferenceworksheet
Pykas0
Pykas0

Reputation: 23

VBA custom procedure/function in Worksheet called outside its module

I've been playing with this problem for some time, and havent figured out how to do it. I have the same function in every worksheet (and those sheets are named like this Name="One", CodeName="SheetOne" ...):

const someVar as Boolean = True

Public Function myFunction() as Boolean
myFunction = someVar
End Function

Now I want it to be called from outside like this - In ThisWorkbook there is procedure "doThis()" and function "TestThis():

Sub doThis()
Dim i as Integer
For i = 1 to ThisWorkbook.Sheets.Count
   If testThis(ThisWorkbook.Worksheets(i)) = True then
      Debug.print "Success!"
   End If
Next i

Function testThis(x As Worksheet)
If x.myFunction = True Then
   testThis = True
Else
   testThis = False
End If

Now I know that at this line "If x.myFunction = True" it throws error "Method or data member not found", because i cant call that function with this reference, so I've tried it with VBComponent:

Sub doThis()
Dim i as Integer
For i = 1 to ThisWorkbook.Sheets.Count
   If testThis(ThisWorkbook.VBProject.VBComponents(Worksheets(i).CodeName)) _
   = True then
      Debug.print "Success!"
   End If
Next i

Function testThis(x As VBComponent)
If x.myFunction = True Then
   testThis = True
Else
   testThis = False
End If

But again, it throws an "Object does not support this property or method" error. Any ideas, How can I call that function from a variable, that has stored reference of any kind to that Worksheet? Any help would be appreciated.

Upvotes: 2

Views: 1265

Answers (2)

Cool Blue
Cool Blue

Reputation: 6476

I know this is an old thread but I thought I may as well share my discoveries. Also, I suspect there is a better way to evaluate the exact same function in every sheet but, for anyone searching, here is a solution to do exactly what you asked for in the title of the post.

In your ThisWorkbook include

Const myFunction = "myFunctuion"
Public ws as Worksheet

For each ws in Me.Sheets
    If testThis(ws) then Debug.print "Success!"
next ws

Function testThis(x as Worksheet)
    testThis = callByName x, x.CodeName & "." & myFunction
End Function

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166306

When compiling the code, Excel checks to see if "WorkSheet" object type has a myFunction method - it is looking at the generic built-in Worksheet object, and this doesn't include your "add on" function (and neither does the "VBComponent" type), so it throws an error.

If you change your parameter type to Object (a more generic type) then it will compile...

Function testThis(x As Object)
    testThis = x.myFunction()
End Function

Upvotes: 1

Related Questions