Invoke VBA codes to Detect Formula Error

Help Activities
, ,
1

Hello there,

I wish to find and populate #DIV/0! errors identified in every sheet of an excel file.
I have written the codes below thinking to apply it using Invoke VBA function to a long list of excel files.

Each excel file and excel sheet have different documentations, hence the error may appear anywhere (no specific range to be read, it should cover the whole workbook).

May I get some advice on how should I correct the below codes?

image

2

populate #DIV/0! errors identified in every sheet of an excel file.

what do you mean by this?
you want to find #DIV/0 errors in excel and replace with something else?

3

Hey Jack,

I would like to find #DIV/0! errors, not to replace anything but to extract the sheet names that contain this error.

4

do you want to extract cell addresses too or just the sheet name?

5

Hi Jack, with both the details on sheet name and cell addresses would be great.

Currently I have an existing VBA code that detects hidden tabs. On top of that, I now wish to add a function to identify formula errors (i.e. #DIV/0!, #VALUE, #REF).

The VBA code to populate hidden tabs is as follows:

Public Function GetHiddenSheet() As String
Dim returnText As String
returnText = “”

For Each ws In ActiveWorkbook.Worksheets
    If ws.Visible = 0 Then
        returnText = returnText + ws.Name + "|"
    Else
        'returnText = ""
    End If
Next ws

    If returnText = "" Then
       returnText = "This EGA does not contain any hidden sheet" 
    End If
GetHiddenSheet = returnText

End Function

6

use my code (you need to copy both functions but the function to use is GetErrorSheetsAndCells
)
note this only checks for #DIV/0!

  Function FindAll(rng As Range, What As Variant, Optional LookIn As XlFindLookIn = xlValues, Optional LookAt As XlLookAt = xlWhole, Optional SearchOrder As XlSearchOrder = xlByColumns, Optional SearchDirection As XlSearchDirection = xlNext, Optional MatchCase As Boolean = False, Optional MatchByte As Boolean = False, Optional SearchFormat As Boolean = False) As Range
      Dim SearchResult As Range
      Dim firstMatch As String
      With rng
          Set SearchResult = .Find(What, , LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
          If Not SearchResult Is Nothing Then
              firstMatch = SearchResult.Address
              Do
                  If FindAll Is Nothing Then
                      Set FindAll = SearchResult
                  Else
                      Set FindAll = Union(FindAll, SearchResult)
                  End If
                  Set SearchResult = .FindNext(SearchResult)
              Loop While Not SearchResult Is Nothing And SearchResult.Address <> firstMatch
          End If
      End With
  End Function
  
  
  Function GetErrorSheetsAndCells()

    Dim wk As Worksheet
    Dim result As String
    result = ""
    
    For Each wk In ActiveWorkbook.Worksheets
        Debug.Print wk.Name
        Dim FoundCells As Range
        
        wk.Activate
        
        Set FoundCells = FindAll(Cells, "#DIV/0!", xlValues)
        
        If Not FoundCells Is Nothing Then
            Dim errorCell As Range, errorArrayStr As String
            errorArrayStr = ""
            For Each errorCell In FoundCells
                errorArrayStr = errorArrayStr & errorCell.Address & ","
            Next errorCell
            errorArrayStr = Left(errorArrayStr, Len(errorArrayStr) - 1)
            
            result = result & wk.Name & "=" & errorArrayStr & "&"
        End If
        
        
    Next wk
    If Not (Len(result) = 0) Then
        result = Left(result, Len(result) - 1)
    End If
    Debug.Print result
    
    GetErrorSheetsAndCells = result
    
End Function

sample output

Sheet1=$C$9,$C$10,$D$8,$E$10,$F$8,$G$9,$G$10&Sheet3=$E$14

how to split output in uipath to sheet name and cells list???

  1. split output by “&” to arrayOfErrorSheetsAndCells
  2. for each string in arrayOfErrorSheetsAndCells
    a) .eg. string = Sheet1=$C$9,$C$10,$D$8,$E$10,$F$8,$G$9,$G$10
    b) split by “=” to “Sheet1” and “$C$9,$C$10,$D$8,$E$10,$F$8,$G$9,$G$10”
    c) we can split $C$9,$C$10,$D$8,$E$10,$F$8,$G$9,$G$10 to error cell array using comma

refer to my sequence for an example
Sequence1.xaml (8.7 KB)
Result
image

1 Like
7

@WS_Chai Is this working?

8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.