Extract Error Cells in a Sheet using Excel VBA

Identify Error Cells in a Sheet using Excel VBA

The following code can be used to identify cells that contains error because of some formula

Sub Rows_Wt_Number_Errors()


Dim oNOCells

On Error GoTo Err_Hdlr
Set oNOCells = Range("A1:B5").Cells.SpecialCells(xlCellTypeFormulas, xlErrors)

For Each ocell In oNOCells
MsgBox ocell.Address
Next ocell

Err_Hdlr:
If Err <> 0 Then
If Err.Description = "No cells were found." Then
MsgBox "No cells with number in forumula found"
End If
Err.Clear

End If

End Sub


The above code will be useful to identify errors like #DIV/0! Error in Excel VBA, #N/A Error in Excel VBA

Extract Cells containing Numeric Formula Result

Identify cells containing numeric result of a formula

Sub Rows_Wt_Number_Formula()

Dim oNOCells

On Error GoTo Err_Hdlr
Set oNOCells = Range("B1:B5").Cells.SpecialCells(xlCellTypeFormulas, xlNumbers)

For Each ocell In oNOCells
MsgBox ocell.Address
Next ocell

Err_Hdlr:
If Err <> 0 Then
If Err.Description = "No cells were found." Then
MsgBox "No cells with number in forumula found"
End If
Err.Clear

End If

End Sub

If Type is either xlCellTypeConstants or xlCellTypeFormulas, this argument is used to determine which types of cells to include in the result. These values can be added together to return more than one type. The default is to select all constants or formulas, no matter what the type. Can be one of the following XlSpecialCellsValue constants:

XlSpecialCellsValue can be one of these XlSpecialCellsValue constants.

xlErrors

xlLogical

xlNumbers xlTextValues

Extract Cells Containing Numbers alone using Excel VBA

Identify cells with Numerals/Numbers using Excel VBA

Many times some text values create problem for which should be a numeric column. In those cases one can use the specialcells method to extract the cells containing numbers alone

Sub Rows_Wt_Numbers()

Dim oNOCells

On Error GoTo Err_Hdlr
Set oNOCells = Range("A1:A5").Cells.SpecialCells(xlCellTypeConstants, xlNumbers)

For Each ocell In oNOCells
MsgBox ocell.Address
Next ocell
Err_Hdlr:
If Err <> 0 Then
If Err.Description = "No cells were found." Then
MsgBox "No cells with number found"
End If
Err.Clear
End If

End Sub

If Type is either xlCellTypeConstants or xlCellTypeFormulas, this argument is used to determine which types of cells to include in the result. These values can be added together to return more than one type. The default is to select all constants or formulas, no matter what the type. Can be one of the following XlSpecialCellsValue constants:

XlSpecialCellsValue can be one of these XlSpecialCellsValue constants.

xlErrors

xlLogical

xlNumbers xlTextValues

Search and Open Files using Excel VBA (FileSearch)

Sort Files By Date using Excel VBA File Search

Use the FileSearch property to return the FileSearch object. The following example searches for files and displays the number of files found and the name of each file.

Sub App_FileSearch_Example()

Dim OWB As Workbook
With Application.FileSearch
.NewSearch
.LookIn = "c:\vbadud\delivered_files\"
.FileName = "*.xls"
If .Execute(SortBy:=msoSortByLastModified, SortOrder:=msoSortOrderDescending) > 0 Then

For i1 = 1 To .FoundFiles.Count
Set OWB = Workbooks.Open(.FoundFiles(i1))
OWB.Save
OWB.Close
Next i1

End If

End With

End Sub

Use the NewSearch method to reset the search criteria to the default settings. All property values are retained after each search is run, and by using the NewSearch method you can selectively set properties for the next file search without manually resetting previous property values. The following example resets the search criteria to the default settings before beginning a new search.

The above example will search for all excel files in a particular folder and opens the latest file. This method can also be used to Identify the last-modified or latest file using Excel VBA.

See also:

Open Excel Files - Open Dialog - GetOpenFilename Method

Browse a Folder / Select a Folder Thru Shell

OpenFileDialog in Visual Basic .Net

Visual Basic Common Dialog

Check Presence of Comboboxes in Excel Sheet using Excel VBA

Programatically Check Dropdowns in Excel Sheet using VBA

The following code will help to check the presence of combo boxes on Excel sheet

Sub Check_DropDowns_Count()


If ActiveSheet.DropDowns.Count = 0 Then
MsgBox "No combo boxes"
End If
End Sub


Sub Check_DropDowns_Source()

Dim oDDwn As Object
If ActiveSheet.DropDowns.Count <> 0 Then
Set oDDwn = ActiveSheet.DropDowns(1)
If InStr(1, oDDwn.ListFillRange, "!") Then
MsgBox "Reference Another Sheet"
End If
End If
End Sub

The above code uses the Sheet - Range Separator to check if the Dropdown refers to current sheet or another sheet

Check if Code is Trusted Outlook VBA

Check if Code is Trusted Outlook VBA

Function IsCodeTrusted() As Boolean

IsCodeTrusted = Application.IsTrusted

End Function

For out-of-process callers that have instantiated the Application object, IsTrusted always returns False. For Outlook add-ins, IsTrusted returns True if and only if the add-in is considered trusted by Outlook.

See also:

Creating Digital Signature for VBA Project (Outlook VBA / Excel VBA/ Word VBA)

Explorers Collection in Outlook

The Explorers collection contains views of all open folders.

Each open folder view is an Explorer object and has a CurrentFolder object, which is the folder

whose data is currently being displayed in the Explorer. The currently active Explorer object

is also available as the ActiveExplorer object.

Sub OL_Explorer_Collection()

Dim oExpo As Explorer

For Each oExpo In Explorers

MsgBox(oExpo.Caption)

Next

End Sub

When you open Outlook using code Explorers will be not be there and the count will be zero. Normally opening outlook will have one Explorer, which is the active explorer

An explorer need not be visible to be included in the Explorers collection.

Use the Explorers property to return the Explorers object from the Application object.