Excel Function
Excel Function (VBA)
Excel Method (VBA)
Excel Object Property (VBA)
Excel Event (VBA)
This page lists all the Excel Functions / Procedures Tips that have been explained in this website.
[table width=”750″ colwidth=”750″ ]
Application
Calculation Property (automatic and manual)
Enable or disable automatic update of external data
Force to recalculate User Defined Function
Iterative calculation and circular reference
[/table]
[table width=”750″ colwidth=”750″ ]
Array
Create an unique array using Dictionary
[/table]
[table width=”750″ colwidth=”750″ ]
Boolean/Checking
Check if formula contains error
Check if text contains any alphabet
Check if text contains any number
Check if text contains only alphabet
Check if text contains only number
Check if text contains any symbol
Check if last digit of HKID is correct
Check if number is prime number
[/table]
[table width=”750″ colwidth=”750″ ]
Date/Time
Add year / month / day / hour / minute / second to date
Auto open Excel with Windows Task Scheduler
Combine year month and day as date
Difference between two dates in complete year/month/day
Difference between two Date Time in year/month/day/hour/minute/second
Find the minimum Date or Maximum Date of each record
Fill blank rows or blank cells
Net working days (exclude Sat and Sun) between two days
Schedule event at specific time or after specific time
[/table]
[table width=”750″ colwidth=”750″ ]
File/Printing
[/table]
[table width=”750″ colwidth=”750″ ]
Graph
[/table]
[table width=”750″ colwidth=”750″ ]
Lookup/Reference/Range/Filter
AutoFilter for Table (using VBA to add cancel reset sort)
AutoFilter for Worksheet (using VBA to add cancel reset sort)
Combine column number and row number to return Cell address in Text format
Convert column number to column letter
Convert column letter to column number
Find a Range that has a relative location to another Range
Find the address of last row of specific column
Find address of last column Cell of specific row
Find the last column number of specific row
Find the last column name of specific row
Find the last row of specific column
Find the position of a substring in a string (case sensitive)
Find the position of a substring in a string (can use Wildcard and case insensitive)
Find the value of last column Cell of specific row
Find the value of last row of specific column
Look up a value in a Range and return the corresponding result value in a Range
Lookup multiple criteria not in first column
Lookup value with partial text
Lookup (vlookup) second matched value or specific occurrence
Sort data using custom Function by Bubble Sort
Sort data using custom Function by assigning number to Text
Sort data using Range Sort Method
Sort data using Table AutoFilter
Sort data using Worksheet AutoFilter
[/table]
[table width=”750″ colwidth=”750″ ]
Numeric
Calculate Auto Filter value with aggregate Function
Count number of cells that contain number
Categorize/ group decimal number
Check if a number is prime number
Extract substring from text by specifying position
Generate a random number between two integers
Multiply the sum of two or more arrays
Solution to Maximum number of digits in Excel
Rounding that uses Round Half Up logic
Sum a Range of values that meet a condition
Sum a Range of values that meet multiple conditions
Sum number part of text in a Range
Sum the same Cells in different worksheet
Use If condition on aggregate Function using Array
[/table]
[table width=”750″ colwidth=”750″ ]
Pivot Table
Add multiple criteria in Label Filter of Pivot Table
Change Pivot Table Layout using VBA
Create Pivot Table using Excel VBA
Refresh all Pivot Table/Pivot Cache
[/table]
[table width=”750″ colwidth=”750″ ]
String
Check if a text contains any alphabet
Check if a text contains any number
Check if a text contains only alphabet
Check if a text contains only number
Check if a text contains any symbol
Convert ASCII value to character
Convert the first character of a text into ASCII value
Copy website contents and paste as Text Unicod to remove format
Delimit a text and extract the Nth substring
Extract a substring from a string
Find the position of a substring in a text (case sensitive)
Find the position of a substring in a text (can use Wildcard and case insensitive)
Find the number of characters in a text
Generate a random letter a to z / A to Z / a to Z
Generate random HKID (Hong Kong ID card number)
Remove line break and carriage return
Sum number part of text in a Range
[/table]
[table width=”750″ colwidth=”750″ ]
Worksheet/Workbook Automation
Auto open Excel with Windows Task Scheduler
Auto run Macro when workbook is opened
Consolidate Comments into one worksheet
Consolidate worksheets into one worksheet
Extract columns to new workbook
Extract columns with specific header to new workbook
Export Workbook / all Worksheet to PDF
Fill blank rows or blank cells
Find all external links and broken links in workbook
Loop through worksheets in the same workbook
Loop through workbooks in folders and subfolders
Refresh closed workbook (links / Pivot Tables)
[/table]
[table width=”750″ colwidth=”750″ ]
Excel VBA Programming
Assign hotkey to Macro and override original hotkey action
Change font color and format in Visual Basics Editor VBE
Difference among Function Sub Method Property
Difference between ByRef and ByVal
Difference between Dim and Set
Option Explicit to force declaration of variables
[/table]
[table width=”750″ colwidth=”750″ ]
Others
Create Excel Add-in and add in Ribbon
Convert xlLinkInfoStatus from numeric value to description
Create Hyperlink to link to other cell to open website or open document
Send Email through Outlook or SMTP
[/table]