Checking for existence of a function in each worksheet in the workbook, and calling this function it if found

[coldwork]

I have a worksheet with multiple sheets. Some of these sheets contain similar data, which needs to be extracted.Intention was to have the following function in each sheet:Function isThisAValidSheet () as Boolean isThisAValidSheet = TrueEnd FunctionThe sheet would have other functions and subs as well, which would need to be called in case this function returned 'True'.In the modules, I now need to check all sheets one by one to see whether this function exists. And if it exists, then I need to call a whole lot of other functions and sub-routines in the same sheet. I am using something similar to the following code in one of the procedures:sub doThis()Dim wkSheet as WorksheetFor Each wkSheet In ActiveWorkbook.Worksheets doesWorkSheetHaveTheInformationRequired = wkSheet.isThisAValidSheet if doesWorkSheetHaveTheInformationRequired = True then wkSheet.exportWorkSheet '....Other Stuff Goes Here end ifNext wkSheetend subUnfortunately, as soon as I try to run this procedure, I get the the error "Compile Error: Method or data member not found" and the".isThisAValidSheet" highlighted in the 'doThis' sub above.Kindly advise.Thank you very much.

 

[Jeffrey Mahoney]

This function can be called from VBA or within a cell. Just feed it the String you're looking for and the Range you want to search.

Sub TestStringExist()
  Dim Sht As Worksheet
  Dim R As Range
  
  Set Sht = Sheets("DataHere")
  Set R = Sht.Range("A1:ZZ10000")
  If StringExists("isThisAValidSheet(", R) = True Then
    Debug.Print "Yup"
  Else
    Debug.Print "Nope"
  End If
  
  
End Sub

Function StringExists(aStr As String, Rng As Range) As Boolean
  Dim Cel As Range

  On Error Resume Next
  Set Cel = Rng.Find(What:=aStr, After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False)
  If Not Cel Is Nothing Then StringExist = True
  
End Function

 

[coldwork]

This function can be called from VBA or within a cell. Just feed it the String you're looking for and the Range you want to search.

Sub TestStringExist()
  Dim Sht As Worksheet
  Dim R As Range
  
  Set Sht = Sheets("DataHere")
  Set R = Sht.Range("A1:ZZ10000")
  If StringExists("isThisAValidSheet(", R) = True Then
    Debug.Print "Yup"
  Else
    Debug.Print "Nope"
  End If
  
  
End Sub

Function StringExists(aStr As String, Rng As Range) As Boolean
  Dim Cel As Range

  On Error Resume Next
  Set Cel = Rng.Find(What:=aStr, After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False)
  If Not Cel Is Nothing Then StringExist = True
  
End Function

Good day Jeffrey, and many thanks for the response. However, I am not looking for values in a cell in the sheet. Due to being unable to format the text properly at the time of starting this thread, I believe that the problem I am trying to highlight is not very clear. Let me re-post what I have asked:

I have a worksheet with multiple sheets. Some of these sheets contain similar data, which needs to be extracted.


Intention was to have the following function in each sheet:


Function isThisAValidSheet () as Boolean
isThisAValidSheet = True
End Function


The sheet would have other functions and subs as well, which would need to be called in case this function returned 'True'.


In the modules, I now need to check all sheets one by one to see whether this function exists. And if it exists, then I need to call a whole lot of other functions and sub-routines in the same sheet. I am using something similar to the following code in one of the procedures:


sub doThis()
Dim wkSheet as Worksheet
For Each wkSheet In ActiveWorkbook.Worksheets
doesWorkSheetHaveTheInformationRequired = wkSheet.isThisAValidSheet
if doesWorkSheetHaveTheInformationRequired = True then
wkSheet.exportWorkSheet
'....Other Stuff Goes Here
end if


Next wkSheet
end sub


Unfortunately, as soon as I try to run this procedure, I get the the error "Compile Error: Method or data member not found" and the
".isThisAValidSheet" highlighted in the 'doThis' sub above.


Kindly advise.
Thank you very much.

 

[Jeffrey Mahoney]

[COLOR=#333333]Function isThisAValidSheet () as Boolean[/COLOR]
[COLOR=#333333]isThisAValidSheet = True[/COLOR]
[COLOR=#333333]End Function[/COLOR]

That function always returns TRUE. If you wanted to put true in a cell, you could simply do that =TRUE

Does it serve a purpose. Are you using that cell to determine if other cells have valid data?


If wkSheet.range("isThisAValidSheet").value = TRUE then '(you would have to give it a named range for this to work)
'export worksheet
'do other stuff
End If


or

 

[coldwork]

[COLOR=#333333]Function isThisAValidSheet () as Boolean[/COLOR]
[COLOR=#333333]isThisAValidSheet = True[/COLOR]
[COLOR=#333333]End Function[/COLOR]

That function always returns TRUE. If you wanted to put true in a cell, you could simply do that =TRUE

Does it serve a purpose. Are you using that cell to determine if other cells have valid data?


If wkSheet.range("isThisAValidSheet").value = TRUE then '(you would have to give it a named range for this to work)
'export worksheet
'do other stuff
End If


or

Thank you yet again for your response.

This function is just one of the many in a sheet which returns similar value. Heres a brief description of what I am trying to achieve here;

Each worksheet has this and other functions. The function in the main module calls this and other such functions to query what kind of information is contained in this sheet. For example;


Worksheets with variables and no tables will have the following (for example in one of the sheets called CWdashboard:

function isHaveVariables() as boolean
isHaveVariable = True
end function

function isHaveTables() as boolean
isHaveTables = False
end function


Worksheets with tables but no variables will have the following (for example is one of the sheets called CWcargoPlanner):

function isHaveVariables() as boolean
isHaveVariable = False
end function

function isHaveTables() as boolean
isHaveTables = True
end function


Worksheets with tables and variables will have the following:

function isHaveVariables() as boolean
isHaveVariable = True
end function

function isHaveTables() as boolean
isHaveTables = True
end function


And so on...

And there are other 'properties' that are also being checked. After the function calling the module knows whether variables or tables exist, it then tries to extract values from variables declared in the worksheet depending on the type of information contained in the worksheet as determined by the isHaveTables and isHaveVariables.

The names of some of the sheets that need to be checked are CWdashboard, CWcargoPlanner etc. If I use the following statement;

doVariablesExistInThisSheet = CWdashboard.isHaveVariables, (which in this case will return true) then the appropriate procedure is called to extract the value of the required variable from that sheet.

Similarly, doVariablesExistInThisSheet = CWcargoPlanner.isHaveVariables will return false, and the procedure call will not be initiated.

Since there are numerous sheets, and the number of sheets will keep changing, I would like to just cycle thru all the sheets and check for these functions in those sheets so that the proper procedure may be called to extract the required variables.

Unfortunately, which I use the originally posted code which declares wkSheet as a worksheet object, and then tries to call .isHaveVariable for each of these objects, I immediately get a compile error. I understand that the compiler is not aware whether this function exists in the sheet contained in the object wkSheet. And hence, I am looking for a workaround. I hope that I have been able to express the problem in an understandable manner.

Many thanks all the same.

 

[Jeffrey Mahoney]

Coldwork

I really want to understand, but think I need a bigger picture. I don't want to offend, but those functions only provide a single answer. There is no test in the code to determine if the sheet has tables or variables. On another note it is not possible to call a function by referencing a sheet name and function like:

[COLOR=#333333]CWcargoPlanner.isHaveVariables[/COLOR]

Please tell me how you want to determine if a sheet has tables. Please tell me how a sheet has the proper variables. With that information we can build a function that will test those variables and return a proper TRUE or FALSE.

Jeff

 

[coldwork]

ColdworkI really want to understand, but think I need a bigger picture. I don't want to offend, but those functions only provide a single answer. There is no test in the code to determine if the sheet has tables or variables. On another note it is not possible to call a function by referencing a sheet name and function like:

[COLOR=#333333]CWcargoPlanner.isHaveVariables[/COLOR]

Please tell me how you want to determine if a sheet has tables. Please tell me how a sheet has the proper variables. With that information we can build a function that will test those variables and return a proper TRUE or FALSE.Jeff

Hello Jeff,Thank you for your response. There is not question of getting offended, and I appreciate your advise and I am always open to suggestions. Let me try to give you a bigger picture; I am working on building a VBA application. There are many sheets with different kinds of information. The application is for use on board cargo ships and oil tankers. Of the various types of information, some relates to the voyage of the ship, such as where its going to, information about that port etc. The user fills this information in this sheet. Similarly, there is other sheets with such 'categories' of information. Whats similar in these sheets is that of the information being entered, there is just one correct answer for each label, more like a form. For example, if the sheet has a label "Port", there is only one answer, which will be the name of the port where the ship is at, for example "Rotterdam".Then there are some sheets which have tables. Not Excel tables, but just data arranged in rows and columns. For example, one sheet CWCargoPlanner with information about where the planning for each port the ship will call. Each row is one particular cargo with all its information, such as the loading port, discharging port, quantity etc. Again, there are other sheets with different 'categories' of such 'tables'. Again, I would like to point out here that these are not any tables that may be defined in or by Excel.I had originally kept all the code related to each sheet within the sheet. Since all sheets had same code, it was not a problem, and just a matter of pasting the existing code into any new sheet that was created. However, when I found a bug in the code for any one sheet, I then had to re-copy the de-bugged code, and re-paste the corrected code into each sheet. So I created a new module, CWworksheet in the workbook, which contained all the code for each sheet. Now any procedure called in the worksheet would be redirected to the CWworksheet along with the name of the sheet so that even if any bug was later found, correcting the code in CWworksheet would take care of all the sheets.Now, each procedure, or sub as we call them in VBA within the CWworksheet module had another variable declared to take the name of the sheet. For example, if there was the following function in each sheet before:sub doThis (variable1 as integer, variable2 as string) variable3 = cells(2,3).value...all the statements hereend subthen the function in CWworksheet was:sub doThis (sheetName as worksheet, variable1 as integer, variable2 as string) variable3 = sheetName.cells(2,3).value...all the statements hereend subThe code in the CWworksheet would of course append the name of the sheet to each statement like in "variable3 = sheetName.cells(2,3).value"Now, since all the code is consolidated in the CWworksheet module, I have to determine which code to run when checking each respective sheet. Which is why I am using this 'isHaveTables' and 'isHaveVariables' as flags. I insert this code in each worksheet depending what actions may be performed in this sheet. I do not want to enter any values in any of the cells or ranges, but if that is unavoidable, then I will of course have to think of another way. I hope that I have brought some clarity into the issue.In any event, the issue at the very basic level is, how to call a VBA sub or function which is in a worksheet from another sub or function which is in the module when the name of the worksheet is either not known, or contained in a worksheet object.Thanks.

 

[mikerickson]

What do you mean "call a function in a sheet"?

When I hear "function in a sheet" I think of a function that is part of a formula that in a cell of the given worksheet.
If Calculation is set to automatic, one doesn't need to call anything to make those formulas calculate. If they aren't then ActiveSheet.Calculate will do the job.


If you want to know if there is a cell that has the formula =IsThisAValidSheet(), you could use code like

With someSheet
    Set foundCell = .Cells.Find(What:="=IsThisAValidSheet()", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False)
End With

If foundCell Is Nothing Then
    MsgBox "it is not there:"
Else
    MsgBox "valid sheet"
End If

 

[Jeffrey Mahoney]

I don't think I'm going to understand your project. I will leave you with these things:

To reference a value in a cell if the cell is a named range

If CWcargoPlanner.range("isHaveVariables").value = true then
  'Do something
end if

To reference a value in a cell if the cell isn't a named range

If CWcargoPlanner.range("G5").value = true then
  'Do something
end if

I use cells all the time to store results that I need to recover after an Excel session has been reopened. Normally it's a value or text, but sometimes can be a TRUE or FALSE. I don't know why a function, such as the one below would be used, because it just takes up unecessary resources. If I was testing for an existance of a table anything else, I would incorporate that into the code. Most times, when I'm trying to determine of a particular sheet is within parameters to reconfigure, I use named ranges. I test for the existance of the named range and then continue with the macro as needed. I use the function below (I forgot where I got it).

function isHaveVariables() as boolean
isHaveVariable = True
end function

Function SheetRangeExists(Wks As Worksheet, sRng As String) As Boolean    On Error Resume Next
    SheetRangeExists = Len(Wks.Range(sRng).Address)
    On Error GoTo 0
    Err.Clear
End Function

I'm going to have to bow out of this thread.

 

[coldwork]

The function is not in a cell. The function is in the VBA module for the sheet. Within the Excel Sheet Object.