Using application.run() with a custom class function, or maybe an alternative?

[smiths87]

With the code example below I'm trying to loop through the collection and call a few custom class functions. What I'd like to do is loop through an array of the class functions and call them using application.run().

In the example below the "IsWidthOK" function would run, then I would loop to the "IsDepthOK" function, etc. Is that possible when it is a custom class function, and not macro? I've tried a few different variations like the function in quotes, then not, then fully qualified, etc. Usually get a cannot run macro.." error, or sometimes a 2015 error. Is there a way to do this? Or an alternative that is similar? Otherwise I'll need to right several functions within that loop, which I'm trying to avoid. Thanks.

ArrayOfFunctions = Array("IsWidthOK", "IsDepthOK", etc.)

For Each clsPOG In colPOGs
    For Each clsShelf In clsPOG.Shelves
        
        If clsShelf.ShelfType = MyType Then
        
            With clsShelf
		
		for i = lbound(ArrayOfFunction) to ubound(ArrayOfFunctions)
                	myvar = Application.Run(ArrayOfFunctions(i))
			
			'do something with myvar...
                next i

            End With
            
        End If
    
    Next clsShelf
Next clsPOG

 

[Norie]

How are the functions defined in the class?

Have you tried preceding the function name with the class name?

 

[smiths87]

How are the functions defined in the class?

They are defined in the class something like the below:

Public Function bLengthOK() As Boolean
bLengthOK = False
    If Me.ShelfLength = 47 Then
        bLengthOK = True
    End If
End Function

Have you tried preceding the function name with the class name?

I've tried running it within my iteration with this

 myvar = Application.Run(clsPOG.Shelves(clsShelf).bDepthOK)

That gives an "Item does not exist is CShelves Collection" error.

When I run like this

myvar = Application.Run(.bDepthOK)

...myvar = a 2015 error.

 

[smiths87]

So a little more research has led me to the 'CallByName' function, which seems to be doing what I want. I put the functions in an array called aMyFunctions() and iterated over that each time to supply the argument to the 'CallByName' procname parameter. Then of course I realized a few of the functions I need to call have parameters, so now I have to find a way to use the args() parameter on some but not all. Maybe a 2-d array or something. Who knows.

Link to the MS Dev Center page for 'CallByName': CallByName Function

                For j = LBound(aMyFunctions) To UBound(aMyFunctions)
                
                    bShelfCheckAnswer = CallByName(clsShelf, aMyFunctions(j), VbMethod)

                    If Not bShelfCheckAnswer Then
                        z = z + 1
                            With ThisWorkbook.Worksheets(aMyWorksheets(j))
                                .Range("A" & i) = clsPOG.pFileName
                                .Range("B" & i) = clsPOG.SectionName
                                .Cells(i, z + 2) = clsShelf.pShelfNumb
                            End With
                    End If
                    
                Next j