1. Write "Hello, Excel" in Cell A1:
Sub WriteHello()
Range("A1").Value = "Hello, Excel"
End Sub
2. Create a New Worksheet:
Sub CreateNewWorksheet()
Sheets.Add
End Sub
3. Format Cell A1 as Bold and Red:
Sub FormatCell()
Range("A1").Font.Bold = True
Range("A1").Font.Color = RGB(255, 0, 0) ' Red
End Sub
4. Loop through a Range and Calculate the Sum:
Sub CalculateSum()
Dim total As Double
total = 0
For Each cell In Range("A1:A10")
total = total + cell.Value
Next cell
Range("B1").Value = total
End Sub
5. Display a Message Box:
Sub ShowMessageBox()
MsgBox "This is a message box", vbInformation, "Information"
End Sub
1. SUM Function:
Calculates the sum of a range of cells.
=SUM(A1:A10)
2. IF Function:
Returns different values based on a condition.
=IF(A1 > 10, "Yes", "No")
3. VLOOKUP Function:
Searches for a value in a table and returns a corresponding value from another column.
=VLOOKUP(A1, TableRange, 2, FALSE)
4. CONCATENATE Function:
Joins multiple text strings into one.
=CONCATENATE("First", "Second", "Third")
5. AVERAGE Function:
Calculates the average of a range of numbers.
=AVERAGE(A1:A10)
6. COUNTIF Function:
Counts the number of cells that meet a specified condition.
=COUNTIF(A1:A10, ">5")
7. TODAY Function:
Returns the current date.
=TODAY()
8. INDEX and MATCH Functions:
Look up a value in a table and return a corresponding value from another column based on a matching condition.
=INDEX(ColumnToReturn, MATCH(ValueToMatch, LookupColumn, 0))
These are just a few examples of Excel VBA macros and functions. You can use these as starting points to automate tasks and perform calculations in Excel. Remember to use the VBA Editor to create and run macros, and you can use functions directly in Excel cells.
Calculates the sum of a range of cells.
Excel Formula:
=SUM(A1:A10)
Calculates the average of a range of numbers.
Excel Formula:
=AVERAGE(A1:A10)
Returns the maximum value from a range of numbers.
Excel Formula:
=MAX(A1:A10)
Returns the minimum value from a range of numbers.
Excel Formula:
=MIN(A1:A10)
Returns different values based on a condition.
Excel Formula:
=IF(A1 > 10, "Yes", "No")
Searches for a value in a table and returns a corresponding value from another column.
Excel Formula:
=VLOOKUP(A1, TableRange, 2, FALSE)
Similar to VLOOKUP but searches horizontally.
Excel Formula:
=HLOOKUP(A1, TableRange, 2, FALSE)
Joins multiple text strings into one.
Excel Formula:
=CONCATENATE("First", "Second", "Third")
Formats a value as text using a specified format.
Excel Formula:
=TEXT(A1, "dd/mm/yyyy")
Returns a specified number of characters from the beginning of a text string.
Excel Formula:
=LEFT(A1, 5)
Returns a specified number of characters from the end of a text string.
Excel Formula:
=RIGHT(A1, 3)
Returns a specific number of characters from the middle of a text string.
Excel Formula:
=MID(A1, 3, 2)
Counts the number of cells that meet a specified condition.
Excel Formula:
=COUNTIF(A1:A10, ">5")
Calculates the sum of cells that meet a specified condition.
Excel Formula:
=SUMIF(A1:A10, ">5")
Calculates the average of cells that meet a specified condition.
Excel Formula:
=AVERAGEIF(A1:A10, ">5")
Counts the number of cells that meet multiple specified conditions.
Excel Formula:
=COUNTIFS(A1:A10, ">5", B1:B10, "<10")
Calculates the sum of cells that meet multiple specified conditions.
Excel Formula:
=SUMIFS(A1:A10, ">5", B1:B10, "<10")
Look up a value in a table and return a corresponding value from another column based on a matching condition.
Excel Formula:
=INDEX(ColumnToReturn, MATCH(ValueToMatch, LookupColumn, 0))
Creates a date from year, month, and day values.
Excel Formula:
=DATE(2023, 10, 15)
Returns the current date.
Excel Formula:
=TODAY()