Tag Archives: Excel tips and tricks

VBA Macro: How to add and rename worksheets

Leave a reply

In following demonstration video I will implement Excel Visual Basic macro that reads worksheet names from the cells A1, A2, A3, … in sheet “Sheet1” and creates new worksheets with correct names.

For simplicity there is no error handling etc and following simple code does the trick:

Sub createsheets()
    Dim newsheet As Worksheet
    Dim r As Integer
    r = 1
    Do While Sheets("Sheet1").Cells(r, 1).Value <> ""
        Set newsheet = Sheets.Add
        newsheet.Name = Sheets("Sheet1").Cells(r, 1).Value
        r = r + 1
    Loop
End Sub

Excel VBA Macro: How to name Worksheets

Leave a reply

You can have several worksheets in one Excel workbook. Default names of worksheets are Sheet 1, Sheet 2, … You can change names manually by double clicking the name of the sheet, but you can also automatise this.

In the following demonstration video I will implement a simple VBA macro that names all worksheets in Excel workbook according to the content of the each worksheet’s cell A1. You can go through all worksheet objects by using For Each -loop:

Sub namesheets()
     Dim s As Worksheet
     For Each s In Worksheets
         s.Name = s.Cells(1, 1).Value
     Next
 End Sub

How to use Excel’s DATE, DATEVALUE, MONTH, YEAR and DAY functions

Leave a reply

Following video demonstrates the usage of Excel’s Date functions:

  • DATE() : Takes year, month and day as parameters and returns Excel date value.
  • DATEVALUE(): Takes date string as parameter and returns Excel date value.
  • YEAR(): Takes Excel date value as parameter and returns corresponding year number (1900..9999).
  • MONTH(): Takes Excel date value as parameter and returns corresponding month number (1..12).
  • DAY(): Takes Excel date value as parameter and returns corresponding day number (1..31).
If you are unfamiliar with Excel’s way to handle dates, please check
Excel date system basics.

Difference between TODAY() and NOW() functions

Leave a reply

Excel has two similar time functions TODAY() and NOW(). These functions are not the same. Difference is that TODAY() function returns only current date (time value is 12AM or 0:00) and NOW()-function returns current date and current time.

Following video demonstrates this:

 

How to use Excel’s cell comments

Leave a reply

You can add descriptive comments to Excel cells. Comment doesn’t have any influence to content of the cell. Each comment can be always visible or visible only when mouse pointer is on cell. You can drag always visible comments to any location on the worksheet. Comment boxes are also resizable.

Following video demonstrates this:

Excel VBA macro: How to implement running clock

Leave a reply

Following video demonstrates how to implement simple VBA macro showing running clock in Excel worksheet. My solution has three macros:

  • RunClock: Sets cell (A1) value to current time by using Now()-function. Then it recursively calls itself in every one second until global variable clockOn = FALSE.
  • StartClock: Sets global variable clockOn = TRUE and calls RunClock
  • StopClock: Sets global variable clockOn = FALSE
Recursive call is made using Excel’s function
 
Application.Ontime(EarliestTime, Procedure, LatestTime, Schedule)
In this case only first two parameters are needed:
  • EarliestTime: Time when Procedure is been run. In this case Now + TimeValue(“00:00:01”)
  • Procedure: The name of the procedure. In this case RunClock itself
If you want to create buttons for starting and stopping the clock, please check how to add button

How to use Excel COUNT functions

Leave a reply

Excel has several functions for counting the number of cells that meet specific criteria:

  • COUNT(value1, value2,…): Counts number of cells (or arguments) containing numbers
  • COUNTA(value1, value2,…): Counts number of non empty cells (or arguments)
  • COUNTIF(range, criteria): Counts number of cells meeting the criteria
  • COUNTIFS(range1, criteria1, range2,criteria2,…): Counts number of cells meeting all given criteria
  • COUNTBLANK(range): Counts number of empty cells
  • There are also functions DCOUNT and DCOUNTA, but I will handle those later
Following video demonstrates the usage of COUNTX-functions:

How to use Skip Blanks in Paste Special and what does the feature do

Leave a reply

There is an option Skip blanks in Excel’s Paste Special -dialog. You may have wondered what is the purpose of that feature.

After watching this video, you will know 🙂

The big secret has now been revealed: Skip blanks pastes only non blank cells and leaves other cells untouched.

Excel VBA macro: How to loop through selection

Leave a reply

In following video I will show an Excel VBA macro example about how to loop through each selected cells and write location information to cells.

We can use Excel’s Selection object (Range type containing selected cells of the active worksheet) and cycle through selected cells by using For-Each-Next loop:

For Each c in Selection
    ...
    c.Value = ...
    ...
Next

c stands for single cell (Range type).

Following video clarifies this.