Microsoft Excel Function Reference

Database Functions

DAVERAGE(database,field,criteria)

Averages the values in a column in a list or database that match conditions you specify.

DCOUNT(database,field,criteria)

Counts the cells that contain numbers in a column in a list or database that match conditions you specify.

DCOUNTA(database,field,criteria)

Counts all of the nonblank cells in a column in a list or database that match conditions you specify.

DGET(database,field,criteria)

Extracts a single value from a column in a list or database that matches conditions you specify.

DMAX(database,field,criteria)

Returns the largest number in a column in a list or database that matches conditions you specify.

DMIN(database,field,criteria)

Returns the smallest number in a column in a list or database that matches conditions you specify.

Remove the Space

Sub TrimALL()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Delete Blank Rows

Sub DeleteBlankRows1()
'Deletes the entire row within the selection if the ENTIRE row contains no data.
'We use Long in case they have over 32,767 rows selected.
Dim i As Long
 'We turn off calculation and screenupdating to speed up the macro.
 With Application
  .Calculation = xlCalculationManual
  .ScreenUpdating = False
  'We work backwards because we are deleting rows.
 For i = Selection.Rows.Count To 1 Step -1
  If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
   Selection.Rows(i).EntireRow.Delete
  End If
 Next i
 .Calculation = xlCalculationAutomatic
 .ScreenUpdating = True
 End With
End Sub

Rows to Repeat

1. Excel having options for "Rows to Repeat at Top" - Press Alt + P + I, Select the Rows and OK.

2. There is no in-build function for "Rows to Repeat at Bottom", You can try below codes to show it in footer.
 Sub MyFooter()

    Dim StrFtr As String, Rng As Range, Sh As Worksheet, c As Range

    Set Sh = Worksheets("Sheet1")

    Set Rng = Sh.Range("A20:H20")

    For Each c In Rng

        StrFtr = StrFtr & c & " "

    Next c

    ActiveSheet.PageSetup.LeftFooter = StrFtr

End Sub

Avoid Scientific (Exponential) Notation

When we enter or paste the long numbers in cells, it’s automatically changed into scientific mode like 1.23E+10. It can be avoid with the help of below codes,

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
Target.NumberFormat = "0"
Else
Call Multipaste
End If
End Sub

Sub Multipaste()
Dim mycell As Range
For Each mycell In Selection.Cells
mycell.NumberFormat = "0"
Next
End Sub

=Sum("Formula", "Tips")

Shortcuts,
    Alt + Equal(=)
    Alt, H, U, S

Basic,
   Total  =SUM(A1,A2,A3)
   Total  =SUM(A1:A3,B1:B3)
   Total  =SUM(A:A 1:3)


Advance,
    =SUM(AVERAGE(A1:A3),MAX(B1:B3))


Array,
   Countif =SUM(--(A1:A10="Apple"))
   Character Count =SUM(LEN(A1:A10))
   Sum Unique Values =SUM(IF(FREQUENCY(A1:A10,A1:A10)>0,A1:A10,0))
   Count Unique Values =SUM(IF(A1:A10<>"",1/COUNTIF(A1:A10,A1:A10),0))

=Rept("Formula", "Tips")

Tip 1: The below formula uses to extract the last name from Full Name entries. Assume, Name is in A1.

 =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))
 

Tip 2: Recently i have been solved this query on FB with the help of REPT formula. I hope that It will be useful for advanced formula users.

Question:

Answer:

 C4 Formula,

=IFERROR(SUBSTITUTE(TRIM(MID(REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND("Colors",REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),(1*999))-25,100)),"Colors",""),"")

C5 Formula,

=IFERROR(IF(C4="","",SUBSTITUTE(TRIM(MID(REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND("Colors",REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND(C4,REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),1)+25)-25,100)),"Colors","")),"")

C6 Formula,

=IFERROR(IF(C5="","",SUBSTITUTE(TRIM(MID(REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND("Colors",REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND(C5,REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),1)+25)-25,100)),"Colors","")),"")

C7 Formula,

=IFERROR(IF(C6="","",SUBSTITUTE(TRIM(MID(REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND("Colors",REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND(C6,REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),1)+25)-25,100)),"Colors","")),"")

 The same formula can be use it for other entries by copy paste method.

Sample file - Download Link

Diffuser Size Calculator

 Download Link

Resistor Value Calculator

  Download Link

LIFE_TIME_DIARY

Download Link

Listbox ADODB Connection

VBA codes for bring the access database into list-box when userform get initialize.
Private Sub UserForm_Initialize()
On Error GoTo UserForm_Initialize_Err
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "/DATABASE.mdb"
rst.Open "SELECT DISTINCT [Field_Name] FROM Table_Name ORDER BY [ Field_Name]", _
cnn, adOpenStatic
rst.MoveFirst
With Me.ListBox1
.Clear
Do
.AddItem rst![ Field_Name]
rst.MoveNext
Loop Until rst.EOF
End With
UserForm_Initialize_Exit:
On Error Resume Next
cnn.Close
rst.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
UserForm_Initialize_Err:
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
Resume UserForm_Initialize_Exit
End Sub
Before running this macro you check ADO(Microsoft ActiveX Data Object Library x.x) at the VBEditor Tool →　Reference.


(winXP Pro & Excel2000)

Bold/Color

Make bold and change the color of the first 5 characters of text in a cell.

Sub FiveChrBoldColor()

Dim mycell As Range

For Each mycell In Selection.Cells

mycell.Characters(Start:=1, Length:=5).Font.FontStyle = "Bold"

mycell.Characters(Start:=1, Length:=5).Font.Color = -16776961

Next

End Sub

Age Calculation

If your Date of Birth is in A1 then Use below formula to Calculate Age,

0 Year 0 Month 0 Days,

=DATEDIF(A1,TODAY(),"Y")&" Year "&DATEDIF(A1,TODAY(),"YM")&" Month "&DATEDIF(A1,TODAY(),"MD")&" Days"

0 Year 0 Month,

=DATEDIF(A1,TODAY(),"Y")&" Year "&DATEDIF(A1,TODAY(),"YM")&" Month "

Only Year,

=DATEDIF(A1,TODAY(),"Y")&" Year "       

                              or

=DATEDIF(A1,TODAY(),"Y")

Removing Special Characters

Using below codes for removing specific set of characters which also includes  *,?~@#$%^&*()_+{}[]":;'<,>

Function ValidateString(strInput As String) As String
Dim strInvalidChars As String
Dim i As Long
strInvalidChars = "*,?~@#$%^&()_+{}[]:;<>" & "'" & """"
For i = 1 To Len(strInvalidChars)
strInput = Replace$(strInput, Mid$(strInvalidChars, i, 1), "")
Next
ValidateString = strInput
End Function