Excel Anatomy- Part01 workbook,worksheet and cells

Work book, work sheet, cells

What is Workbook ?

A workbook is a file that contains one or more worksheets, to help you organize your data. You can create a workbook from a blank workbook or a template. Press CTRL+N to quickly create a new workbook.

In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets in a single file. Below is an example of a spreadsheet called "Sheet 1" in an Excel workbook file called "Book 1." Our example also has the "Sheet 2" and "Sheet 3" sheet tabs that are also part of the same workbook.

What is Worksheet?

An Excel worksheet is a single spreadsheet that contains cells organized by rows and columns. A worksheet begins with row number one and column A. Each cell can contain a number, text or formula. A cell can also reference another cell in the same worksheet, the same workbook or a different workbook. In Excel 2010, the maximum size of a worksheet is 1,048,576 rows by 16,384 columns.

There can be many sheets in your Excel document and you can see the sheets listed as tabs along the bottom of your document. We can insert as many worksheets as you want to quickly insert a new worksheet, click the plus sign at the bottom of the document window.

If you want more worksheets select file tab click excel option to general and select to include many new sheets change.
File > options > general > include many new sheets

If you want to move the work sheet, to select right click move to sheet position.

Press ctrl + page down to move the worksheet. Move to the sheet1 from sheet5 to select sheet 1 tab right click on the mouse to select move and click OK.

What is cell?

A cell is the intersection between a row and a column on a spreadsheet that starts with cell A1. Below is an example of a highlighted cell ; the cell address, cell name, or cell pointer "D8" (column D, row 8) is the selected cell and the location of what is being modified.

Active cell is surround by bold layer

Vertical columns are numbered with alphabetic values such as A, B, C. Horizontal rows are numbered with numeric values such as 1, 2, 3.A cell can only store 1 piece of data at a time. you can store data in a cell such as a formula, text value, numeric value, or date value.

VBA String Functions - Part 6

LTRIM Function (VBA)

In Microsoft Excel, the LTRIM function removes leading spaces from a string.

Syntax

LTRIM( text )
text is the string that you wish to remove leading spaces from.

VBA Function Example

The LTRIM function can only be used in VBA code in Microsoft Excel. Here are some examples of what the LTRIM function would return:

LTRIM("  Google com") would return "Google com"
LTRIM("  Alphabet") would return "Alphabet"
LTRIM("  Alphabet  ") would return "Alphabet  "

Dim sResult As String
sResult = LTrim("   Alphabet   ")

The variable sResult would now contain the value of "Alphabet  ".

VBA String Functions - Part 7

INSTR Function (VBA)

In Microsoft Excel, the INSTR function returns the position of the first occurrence of a substring in a string.

Syntax

The syntax for the INSTR function is:  

InStr( [start], string, substring, [compare] )
start is optional. It is the starting position for the search. If this parameter is omitted, the search will begin at position 1.

string is the string to search  within.

substring is the substring that you want to find.

VBA Function Example

The INSTR function can only be used in VBA code in Microsoft Excel. Here are some examples of what the INSTR function would return:

InStr(1, "President of India", "of") would return 11
InStr("President of India", "India") would return 14
InStr(5, "President of India", "e") would return 7

Dim iPosition As Integer 
iPosition = InStr(5, "President of India", "e")

In this example, the variable called iPosition would now contain the value 7.

VBA String Functions - Part 5

LEFT Function (VBA)

In Microsoft Excel, the LEFT function allows you to extract a substring from a string, starting from the left-most character.

Syntax

The syntax for the LEFT function is:

LEFT( text, [number_of_characters] )
text is the string that you wish to extract from.

number_of_characters is optional. It indicates the number of characters that you wish to extract starting from the left-most character. If this parameter is omitted, only 1 character is returned.

VBA Function Example

Dim sResult As String
sResult = Left("Alphabet",3)

The variable sResult would now contain the value of "Alp".

VBA String Functions - Part 4

STRCONV Function (VBA)

In Microsoft Excel, the STRCONV function returns a string converted as specified.

Syntax

The syntax for the STRCONV function is:

StrConv ( text, conversion, [LCID] )
text is the string that you wish to convert.

LCID is optional. If this parameter is omitted, the STRCONV function assumes the system LocaleID.

VBA Function Example

The STRCONV function can only be used in VBA code in Microsoft Excel. Here are some examples of what the STRCONV function would return:

StrConv("vbaexpress.blogspot.in", 1) would return "VBAEXPRESS.BLOGSPOT.IN"
StrConv("VBAEXPRESS.BLOGSPOT.IN", 2) would return "vbaexpress.blogspot.in"
StrConv("vbaexpress.blogspot.in", 3) would return "Vbaexpress.Blogspot.In"

Dim sResult As String
sResult = StrConv("VBAEXPRESS.BLOGSPOT.IN", vbProperCase)

In this example, the variable called sResult would now contain the value "Vbaexpress.Blogspot.In".

VBA String Functions - Part 3

LCASE Function (VBA)

In Microsoft Excel, the LCASE function converts a string to lower-case.

Syntax

The syntax for the LCASE function is:

LCase( text )
Text is the string that you wish to convert to lower-case.

VBA Function Example

The LCASE function can only be used in VBA code in Microsoft Excel. Here are some examples of what the LCASE function would return:

LCase("ALPHABET") would return "alphabet"
LCase("Google SearcH") would return "google search"
LCase("124ABC") would return "124abc"

Dim sResult As String
sResult = LCase("This is a TEST")

In this example, the variable called sResult would now contain the value "this is a test".

VBA String Functions - Part 2

FORMAT Function  (VBA)

In Microsoft Excel, the FORMAT function takes an expression and returns it as a formatted string.

Syntax

The syntax for the FORMAT function is:

Format ( expression, [ format ] )
expression is the value to format.

format is optional. It is the format to apply to the expression. You can either define your own format or use one of the named formats that Excel has predefined such as:

VBA Function Example

The FORMAT function can only be used in VBA code in Microsoft Excel. Here are some examples of what the FORMAT function would return:

Format("210.6", "#,##0.00") would return '210.60'
Format("210.6", "Standard") would return '210.60'
Format("0.981", "Percent") would return '98.10%'
Format("1267.5", "Currency") would return '$1,267.50'
Format("Sep 3, 2003", "Short Date") would return '9/3/2003'

Dim sValue As String
sValue = Format("0.981", "Percent")

In this example, the variable called sValue would now contain the value of '98.10%'.