This blog is about Microsoft Excel and includes tutorial videos demonstrating how to get things done easily using Excel. And this blog also proves you that Microsoft Excel simply is the best part of Microsoft Office and maybe the most powerful software ever made.
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
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
Excel handles Date values as integer numbers. Number one means 1st of January 1900, two means 2nd of January 1900 and so on. Today (27th of February 2013) is day number 41332. So date number can be understood as the number of days since 31st of December 1899.
The first day that Excel supports is 1st of January 1900 and if you need earlier dates, Excel's build in date functions are not working.
Excel actually supports two different Date Systems: In addition two this 1900 Date System you can also use 1904 Date System where number one means 1st of January 1904. This can be selected on Calculation settings but I recommend to use 1900 Date System to maintain better compatibility to e.g. other spreadsheet programs.
Excel handles times as decimal numbers between 0 and 1 and so 41332,41667 means that the date is 27th of February 2013 and time of the day is 10AM.
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.
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:
Tip: If you have difficulties to see the video, try to maximise it from lower right corner:
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