Return to Microsoft Visual Basic
3.
The Color of a Border
To programmatically specify the color of a border, access the Borders indexed property of a
cell or a group of cells and specify the border whose color you want to change, as we saw in
the previous section. To support colors, the Borders object is equipped with a property
named Color. To specify the color, assign the desired color to the property. The VBA
provides a (limited) list of colors such as vbBlackvbWhitevbRedvbGreen, and vbBlue.
In reality, a color in Microsoft Windows is represented as a color between 0 and 16,581,375.
Practical Learning: Controlling the Colors of Cells Borders
Change the code as follows:
Sub CreateWorkbook()
' CreateWorkbook Macro
' This macro is used to create a workbook for the
' Georgetown Dry Cleaning Services
' Keyboard Shortcut: Ctrl+Shift+W
. . . No Change
Rem To draw a thick line, change the bottom
Rem borders of the cells from B5 to J5
Range("B5:J5").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B5:J5").Borders(xlEdgeBottom).Weight = xlMedium
Range("B5:J5").Borders(xlEdgeBottom).ThemeColor = 5
. . . No Change
Rem To draw a thick line, change the bottom
Rem borders of the cells from B5 to J5
Range("B12:J12").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B12:J12").Borders(xlEdgeBottom).Weight = xlMedium
Range("B12:J12").Borders(xlEdgeBottom).ThemeColor = 5
. . . No Change
Range("H16").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("H16:I16").Borders(xlEdgeBottom).Weight = xlMedium
Range("H16:I16").Borders(xlEdgeBottom).ThemeColor = 5
Rem Hide the gridlines
ActiveWindow.DisplayGridlines = False
End Sub
1.
Return to Microsoft Excel and press Ctrl + Shift + W to see the result
2.
Return to Microsoft Visual Basic
3.
The Cell's Background
A cell has a background color which, by default, is white. If you want to change a
background, specify the cell or group of cells, using the Range class. The Range class is
equipped with a property named Interior. From this property, you can access the
ThemeColor and assign the desired color.
Practical Learning: Painting the Background of Cells
http://www.functionx.com/vbaexcel/Lesson18.htm
141
Pdf text search - search text inside PDF file in C#.net, ASP.NET, MVC, Ajax, WinForms, WPF
Learn how to search text in PDF document and obtain text content and location information
select text in pdf; search text in pdf using java
Pdf text search - VB.NET PDF Text Search Library: search text inside PDF file in vb.net, ASP.NET, MVC, Ajax, WinForms, WPF
Learn How to Search Text in PDF Document and Obtain Text Content and Location Information in VB.NET application
select text pdf file; search pdf for text in multiple files
Change the code as follows:
Sub CreateWorkbook()
' CreateWorkbook Macro
' This macro is used to create a workbook for the
' Georgetown Dry Cleaning Services
' Keyboard Shortcut: Ctrl+Shift+W
Rem Just in case there is anything on the
Rem worksheet, delete everything
Range("A:K").Delete
Range("1:20").Delete
Rem Create the sections and headings of the worksheet
Range("B2") = "Georgetown Dry Cleaning Services"
Range("B2").Font.Name = "Rockwell Condensed"
Range("B2").Font.Size = 24
Range("B2").Font.Bold = True
Range("B2").Font.Color = vbBlue
Range("B3:J3").Interior.ThemeColor = xlThemeColorLight2
Range("B5") = "Order Identification"
Range("B5").Font.Name = "Cambria"
Range("B5").Font.Size = 14
Range("B5").Font.Bold = True
Range("B5").Font.ThemeColor = 5
Rem To draw a thick line, change the bottom
Rem borders of the cells from B5 to J5
Range("B5:J5").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B5:J5").Borders(xlEdgeBottom).Weight = xlMedium
Range("B5:J5").Borders(xlEdgeBottom).ThemeColor = 5
Range("B6") = "Receipt #:"
Range("D6:F6").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("D6:F6").Borders(xlEdgeBottom).Weight = xlHairline
Range("G6") = "Order Status:"
Range("I6:J6").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("I6:J6").Borders(xlEdgeBottom).Weight = xlHairline
Range("B7") = "Customer Name:"
Range("D7:F7").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("D7:F7").Borders(xlEdgeBottom).Weight = xlHairline
Range("G7") = "Customer Phone:"
Range("I7:J7").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("I7:J7").Borders(xlEdgeBottom).Weight = xlHairline
Rem To draw a thick line, change the bottom
Rem borders of the cells from B5 to J5
Range("B8:J8").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B8:J8").Borders(xlEdgeBottom).Weight = xlThin
Range("B9") = "Date Left:"
Range("D9:F9").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("D9:F9").Borders(xlEdgeBottom).Weight = xlHairline
Range("G9") = "Time Left:"
Range("I9:J9").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("I9:J9").Borders(xlEdgeBottom).Weight = xlHairline
Range("B10") = "Date Expected:"
Range("D10:F10").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("D10:F10").Borders(xlEdgeBottom).Weight = xlHairline
Range("G10") = "Time Expected:"
Range("I10:J10").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("I10:J10").Borders(xlEdgeBottom).Weight = xlHairline
Range("B11") = "Date Picked Up:"
Range("D11:F11").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("D11:F11").Borders(xlEdgeBottom).Weight = xlHairline
Range("G11") = "Time Picked Up:"
Range("I11:J11").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("I11:J11").Borders(xlEdgeBottom).Weight = xlHairline
Rem To draw a thick line, change the bottom
Rem borders of the cells from B5 to J5
Range("B12:J12").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B12:J12").Borders(xlEdgeBottom).Weight = xlMedium
Range("B12:J12").Borders(xlEdgeBottom).ThemeColor = 5
Range("B13") = "Items to Clean"
Range("B13").Font.Name = "Cambria"
Range("B13").Font.Size = 14
Range("B13").Font.Bold = True
Range("B13").Font.ThemeColor = 5
1.
http://www.functionx.com/vbaexcel/Lesson18.htm
142
C# Word - Search and Find Text in Word
C# Word - Search and Find Text in Word. Learn How to Search Text in PDF Document and Obtain Text Content and Location Information. Overview.
search text in pdf image; text select tool pdf
C# PowerPoint - Search and Find Text in PowerPoint
C# PowerPoint - Search and Find Text in PowerPoint. Learn How to Search Text in PDF Document and Obtain Text Content and Location Information. Overview.
make pdf text searchable; searching pdf files for text
Range("B14") = "Item"
Range("D14") = "Unit Price"
Range("E14") = "Qty"
Range("F14") = "Sub-Total"
Range("B14:F14").Borders(xlEdgeLeft).LineStyle = xlContinuous
Range("B14:F14").Borders(xlEdgeLeft).Weight = xlThin
Range("B14:F14").Borders(xlEdgeTop).LineStyle = xlContinuous
Range("B14:F14").Borders(xlEdgeTop).Weight = xlThin
Range("B14:F14").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("B14:F14").Borders(xlEdgeRight).Weight = xlThin
Range("B14:F14").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B14:F14").Borders(xlEdgeBottom).Weight = xlThin
Range("C14").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("C14").Borders(xlEdgeRight).Weight = xlThin
Range("D14").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("D14").Borders(xlEdgeRight).Weight = xlThin
Range("E14").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("E14").Borders(xlEdgeRight).Weight = xlThin
Range("B15") = "Shirts"
Range("B15").Borders(xlEdgeLeft).LineStyle = xlContinuous
Range("B15").Borders(xlEdgeLeft).Weight = xlThin
Range("H15") = "Order Summary"
Range("H15").Font.Name = "Cambria"
Range("H15").Font.Size = 14
Range("H15").Font.Bold = True
Range("H15").Font.ThemeColor = 5
Range("B16") = "Pants"
Range("B16").Borders(xlEdgeLeft).LineStyle = xlContinuous
Range("B16").Borders(xlEdgeLeft).Weight = xlThin
Range("B17") = "None"
Range("B17").Borders(xlEdgeLeft).LineStyle = xlContinuous
Range("B17").Borders(xlEdgeLeft).Weight = xlThin
Range("H17") = "Cleaning Total:"
Range("I17").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("I17").Borders(xlEdgeBottom).Weight = xlHairline
Range("B18") = "None"
Range("B18").Borders(xlEdgeLeft).LineStyle = xlContinuous
Range("B18").Borders(xlEdgeLeft).Weight = xlThin
Range("H18") = "Tax Rate:"
Range("I18").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("I18").Borders(xlEdgeBottom).Weight = xlHairline
Range("I18") = "5.75"
Range("J18") = "%"
Range("B19") = "None"
Range("B19").Borders(xlEdgeLeft).LineStyle = xlContinuous
Range("B19").Borders(xlEdgeLeft).Weight = xlThin
Range("H19") = "Tax Amount:"
Range("I19").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("I19").Borders(xlEdgeBottom).Weight = xlHairline
Range("B20") = "None"
Range("B20").Borders(xlEdgeLeft).LineStyle = xlContinuous
Range("B20").Borders(xlEdgeLeft).Weight = xlThin
Range("C15").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("C15").Borders(xlEdgeRight).Weight = xlThin
Range("C16").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("C16").Borders(xlEdgeRight).Weight = xlThin
Range("C17").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("C17").Borders(xlEdgeRight).Weight = xlThin
Range("C18").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("C18").Borders(xlEdgeRight).Weight = xlThin
Range("C19").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("C19").Borders(xlEdgeRight).Weight = xlThin
Range("C20").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("C20").Borders(xlEdgeRight).Weight = xlThin
Range("B14:C14").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B14:C14").Borders(xlEdgeBottom).Weight = xlThin
Range("B15:C15").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B15:C15").Borders(xlEdgeBottom).Weight = xlThin
Range("D15:F15").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("D15:F15").Borders(xlEdgeBottom).Weight = xlHairline
Range("D15").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("D15").Borders(xlEdgeRight).Weight = xlHairline
Range("E15").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("E15").Borders(xlEdgeRight).Weight = xlHairline
Range("F15").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("F15").Borders(xlEdgeRight).Weight = xlThin
Range("B16:C16").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B16:C16").Borders(xlEdgeBottom).Weight = xlThin
Range("D16:F16").Borders(xlEdgeBottom).LineStyle = xlContinuous
http://www.functionx.com/vbaexcel/Lesson18.htm
143
C# PDF delete text Library: delete, remove text from PDF file in
The following C# coding example illustrates how to perform PDF text deleting function in your .NET project, according to search option. // Open a document.
pdf make text searchable; how to search a pdf document for text
C# PDF replace text Library: replace text in PDF content in C#.net
The following C# coding example illustrates how to perform PDF text replacing function in your .NET project, according to search option. // Open a document.
converting pdf to searchable text format; can't select text in pdf file
Range("D16:F16").Borders(xlEdgeBottom).Weight = xlHairline
Range("D16").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("D16").Borders(xlEdgeRight).Weight = xlHairline
Range("E16").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("E16").Borders(xlEdgeRight).Weight = xlHairline
Range("F16").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("F16").Borders(xlEdgeRight).Weight = xlThin
Range("B17:C17").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B17:C17").Borders(xlEdgeBottom).Weight = xlThin
Range("D17:F17").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("D17:F17").Borders(xlEdgeBottom).Weight = xlHairline
Range("D17").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("D17").Borders(xlEdgeRight).Weight = xlHairline
Range("E17").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("E17").Borders(xlEdgeRight).Weight = xlHairline
Range("F17").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("F17").Borders(xlEdgeRight).Weight = xlThin
Range("B18:C18").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B18:C18").Borders(xlEdgeBottom).Weight = xlThin
Range("D18:F18").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("D18:F18").Borders(xlEdgeBottom).Weight = xlHairline
Range("D18").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("D18").Borders(xlEdgeRight).Weight = xlHairline
Range("E18").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("E18").Borders(xlEdgeRight).Weight = xlHairline
Range("F18").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("F18").Borders(xlEdgeRight).Weight = xlThin
Range("B19:C19").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B19:C19").Borders(xlEdgeBottom).Weight = xlThin
Range("D19:F19").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("D19:F19").Borders(xlEdgeBottom).Weight = xlHairline
Range("D19").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("D19").Borders(xlEdgeRight).Weight = xlHairline
Range("E19").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("E19").Borders(xlEdgeRight).Weight = xlHairline
Range("F19").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("F19").Borders(xlEdgeRight).Weight = xlThin
Range("B20:F20").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B20:F20").Borders(xlEdgeBottom).Weight = xlThin
Range("D20").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("D20").Borders(xlEdgeRight).Weight = xlHairline
Range("E20").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("E20").Borders(xlEdgeRight).Weight = xlHairline
Range("F20").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("F20").Borders(xlEdgeRight).Weight = xlThin
Range("H20") = "Order Total:"
Range("I20").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("I20").Borders(xlEdgeBottom).Weight = xlHairline
Rem Change the widths and heights of some columns and rows
Rem In previous lessons, we learned all these things
Range("E:E, G:G").ColumnWidth = 4
Columns("H").ColumnWidth = 14
Columns("J").ColumnWidth = 1.75
Rows("3").RowHeight = 2
Range("8:8, 12:12").RowHeight = 8
Rem Merge the cells H15, I15, H16, and I16
Range("H15:I16").MergeCells = True
Rem Align the merged text to the left
Range("H15:H16").VerticalAlignment = xlBottom
Range("H16").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("H16:I16").Borders(xlEdgeBottom).Weight = xlMedium
Range("H16:I16").Borders(xlEdgeBottom).ThemeColor = 5
Rem Hide the gridlines
ActiveWindow.DisplayGridlines = False
End Sub
Return to Microsoft Excel and press Ctrl + Shift + W to see the result
2.
http://www.functionx.com/vbaexcel/Lesson18.htm
144
VB.NET PDF replace text library: replace text in PDF content in vb
The following coding example illustrates how to perform PDF text replacing function in your VB.NET project, according to search option.
text searchable pdf; find text in pdf files
VB.NET PDF- View PDF Online with VB.NET HTML5 PDF Viewer
PDF Read. Text: Extract Text from PDF. Text: Search Text in PDF. Image: Extract Image from PDF. Easy to search PDF text in whole PDF document.
search multiple pdf files for text; pdf text search tool
Close the worksheet
3.
When asked whether you want to save, click No
4.
Previous
Copyright © 2008-2009 FunctionX, Inc.
http://www.functionx.com/vbaexcel/Lesson18.htm
145
C# PDF Text Highlight Library: add, delete, update PDF text
The following C# coding example illustrates how to perform PDF text highlight function in your .NET project, according to search option. // Open a document.
search pdf files for text programmatically; how to select text in pdf and copy
C# HTML5 PDF Viewer SDK to view PDF document online in C#.NET
Page: Rotate a PDF Page. PDF Read. Text: Extract Text from PDF. Text: Search Text in PDF. Easy to search PDF text in whole PDF document.
how to select text in a pdf; pdf searchable text
Constants, Expressions and Formulas
Introduction to Constants
A constant is a value that does not change. It can be a number, a string, or an expression. To
create a constant, use the Const keyword and assign the desired value to it. Here is an
example:
Private Sub CreateConstant()
Const Number6 = 6
End Sub
After creating the constant, you can use its name wherever its value would have been used. Some
of the constants you will use in your expressions have already been created. We will mention
them when necessary.
Introduction to Expressions
An expression is one or more symbols combined with one or more values to create another value.
For example, +16 is an expression that creates the positive value 16. Most expressions that we
know are made of arithmetic calculations. An example is 422.82 * 15.55.
To add an expression to a selected cell, assign it to the ActiveCell object. Here is an example:
Sub Exercise()
ActiveCell = 422.82 * 15.5
End Sub
Practical Learning: Introducing Expressions
Start Microsoft Excel
1.
On the 
, click Developer
2.
In the Code section, click Record Macro 
3.
Set the Macro Name to CreateWorkbook
4.
In the Shortcut Key text box, type W to get Ctrl + Shift + W and click OK
5.
On the Ribbon, click Stop Recording
6.
In the Code section of the Ribbon, click Macros 
7.
In the Macro dialog box, make sure CreateWorkbook is selected and click Edit
8.
Change the code as follows:
Sub CreateWorkbook()
'
' CreateWorkbook Macro
' This macro is used to create a workbook for the
' Georgetown Dry Cleaning Services
'
' Keyboard Shortcut: Ctrl+Shift+W
'
Rem Just in case there is anything on the
Rem worksheet, delete everything
Range("A:K").Delete
Range("1:20").Delete
Rem Create the sections and headings of the worksheet
Range("B2") = "Georgetown Dry Cleaning Services"
Range("B2").Font.Name = "Rockwell Condensed"
Range("B2").Font.Size = 24
Range("B2").Font.Bold = True
Range("B2").Font.Color = vbBlue
Range("B3:J3").Interior.ThemeColor = xlThemeColorLight2
Range("B5") = "Order Identification"
Range("B5").Font.Name = "Cambria"
9.
Ribbon
http://www.functionx.com/vbaexcel/Lesson19.htm
146
Range("B5").Font.Size = 14
Range("B5").Font.Bold = True
Range("B5").Font.ThemeColor = 5
Rem To draw a thick line, change the bottom
Rem borders of the cells from B5 to J5
Range("B5:J5").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B5:J5").Borders(xlEdgeBottom).Weight = xlMedium
Range("B5:J5").Borders(xlEdgeBottom).ThemeColor = 5
Range("B6") = "Receipt #:"
Range("D6:F6").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("D6:F6").Borders(xlEdgeBottom).Weight = xlHairline
Range("G6") = "Order Status:"
Range("I6:J6").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("I6:J6").Borders(xlEdgeBottom).Weight = xlHairline
Range("B7") = "Customer Name:"
Range("D7:F7").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("D7:F7").Borders(xlEdgeBottom).Weight = xlHairline
Range("G7") = "Customer Phone:"
Range("I7:J7").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("I7:J7").Borders(xlEdgeBottom).Weight = xlHairline
Rem To draw a thick line, change the bottom
Rem borders of the cells from B5 to J5
Range("B8:J8").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B8:J8").Borders(xlEdgeBottom).Weight = xlThin
Range("B9") = "Date Left:"
Range("D9:F9").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("D9:F9").Borders(xlEdgeBottom).Weight = xlHairline
Range("G9") = "Time Left:"
Range("I9:J9").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("I9:J9").Borders(xlEdgeBottom).Weight = xlHairline
Range("B10") = "Date Expected:"
Range("D10:F10").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("D10:F10").Borders(xlEdgeBottom).Weight = xlHairline
Range("G10") = "Time Expected:"
Range("I10:J10").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("I10:J10").Borders(xlEdgeBottom).Weight = xlHairline
Range("B11") = "Date Picked Up:"
Range("D11:F11").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("D11:F11").Borders(xlEdgeBottom).Weight = xlHairline
Range("G11") = "Time Picked Up:"
Range("I11:J11").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("I11:J11").Borders(xlEdgeBottom).Weight = xlHairline
Rem To draw a thick line, change the bottom
Rem borders of the cells from B5 to J5
Range("B12:J12").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B12:J12").Borders(xlEdgeBottom).Weight = xlMedium
Range("B12:J12").Borders(xlEdgeBottom).ThemeColor = 5
Range("B13") = "Items to Clean"
Range("B13").Font.Name = "Cambria"
Range("B13").Font.Size = 14
Range("B13").Font.Bold = True
Range("B13").Font.ThemeColor = 5
Range("B14") = "Item"
Range("D14") = "Unit Price"
Range("E14") = "Qty"
Range("F14") = "Sub-Total"
Range("B14:F14").Borders(xlEdgeLeft).LineStyle = xlContinuous
Range("B14:F14").Borders(xlEdgeLeft).Weight = xlThin
Range("B14:F14").Borders(xlEdgeTop).LineStyle = xlContinuous
Range("B14:F14").Borders(xlEdgeTop).Weight = xlThin
Range("B14:F14").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("B14:F14").Borders(xlEdgeRight).Weight = xlThin
Range("B14:F14").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B14:F14").Borders(xlEdgeBottom).Weight = xlThin
Range("C14").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("C14").Borders(xlEdgeRight).Weight = xlThin
Range("D14").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("D14").Borders(xlEdgeRight).Weight = xlThin
Range("E14").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("E14").Borders(xlEdgeRight).Weight = xlThin
Range("B15") = "Shirts"
Range("B15").Borders(xlEdgeLeft).LineStyle = xlContinuous
Range("B15").Borders(xlEdgeLeft).Weight = xlThin
Range("H15") = "Order Summary"
Range("H15").Font.Name = "Cambria"
Range("H15").Font.Size = 14
Range("H15").Font.Bold = True
Range("H15").Font.ThemeColor = 5
http://www.functionx.com/vbaexcel/Lesson19.htm
147
Range("B16") = "Pants"
Range("B16").Borders(xlEdgeLeft).LineStyle = xlContinuous
Range("B16").Borders(xlEdgeLeft).Weight = xlThin
Range("B17") = "None"
Range("B17").Borders(xlEdgeLeft).LineStyle = xlContinuous
Range("B17").Borders(xlEdgeLeft).Weight = xlThin
Range("H17") = "Cleaning Total:"
Range("I17").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("I17").Borders(xlEdgeBottom).Weight = xlHairline
Range("B18") = "None"
Range("B18").Borders(xlEdgeLeft).LineStyle = xlContinuous
Range("B18").Borders(xlEdgeLeft).Weight = xlThin
Range("H18") = "Tax Rate:"
Range("I18").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("I18").Borders(xlEdgeBottom).Weight = xlHairline
Range("I18") = "5.75"
Range("J18") = "%"
Range("B19") = "None"
Range("B19").Borders(xlEdgeLeft).LineStyle = xlContinuous
Range("B19").Borders(xlEdgeLeft).Weight = xlThin
Range("H19") = "Tax Amount:"
Range("I19").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("I19").Borders(xlEdgeBottom).Weight = xlHairline
Range("B20") = "None"
Range("B20").Borders(xlEdgeLeft).LineStyle = xlContinuous
Range("B20").Borders(xlEdgeLeft).Weight = xlThin
Range("C15").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("C15").Borders(xlEdgeRight).Weight = xlThin
Range("C16").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("C16").Borders(xlEdgeRight).Weight = xlThin
Range("C17").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("C17").Borders(xlEdgeRight).Weight = xlThin
Range("C18").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("C18").Borders(xlEdgeRight).Weight = xlThin
Range("C19").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("C19").Borders(xlEdgeRight).Weight = xlThin
Range("C20").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("C20").Borders(xlEdgeRight).Weight = xlThin
Range("B14:C14").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B14:C14").Borders(xlEdgeBottom).Weight = xlThin
Range("B15:C15").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B15:C15").Borders(xlEdgeBottom).Weight = xlThin
Range("D15:F15").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("D15:F15").Borders(xlEdgeBottom).Weight = xlHairline
Range("D15").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("D15").Borders(xlEdgeRight).Weight = xlHairline
Range("E15").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("E15").Borders(xlEdgeRight).Weight = xlHairline
Range("F15").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("F15").Borders(xlEdgeRight).Weight = xlThin
Range("B16:C16").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B16:C16").Borders(xlEdgeBottom).Weight = xlThin
Range("D16:F16").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("D16:F16").Borders(xlEdgeBottom).Weight = xlHairline
Range("D16").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("D16").Borders(xlEdgeRight).Weight = xlHairline
Range("E16").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("E16").Borders(xlEdgeRight).Weight = xlHairline
Range("F16").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("F16").Borders(xlEdgeRight).Weight = xlThin
Range("B17:C17").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B17:C17").Borders(xlEdgeBottom).Weight = xlThin
Range("D17:F17").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("D17:F17").Borders(xlEdgeBottom).Weight = xlHairline
Range("D17").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("D17").Borders(xlEdgeRight).Weight = xlHairline
Range("E17").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("E17").Borders(xlEdgeRight).Weight = xlHairline
Range("F17").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("F17").Borders(xlEdgeRight).Weight = xlThin
Range("B18:C18").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B18:C18").Borders(xlEdgeBottom).Weight = xlThin
Range("D18:F18").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("D18:F18").Borders(xlEdgeBottom).Weight = xlHairline
Range("D18").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("D18").Borders(xlEdgeRight).Weight = xlHairline
Range("E18").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("E18").Borders(xlEdgeRight).Weight = xlHairline
Range("F18").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("F18").Borders(xlEdgeRight).Weight = xlThin
Range("B19:C19").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B19:C19").Borders(xlEdgeBottom).Weight = xlThin
Range("D19:F19").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("D19:F19").Borders(xlEdgeBottom).Weight = xlHairline
http://www.functionx.com/vbaexcel/Lesson19.htm
148
Range("D19").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("D19").Borders(xlEdgeRight).Weight = xlHairline
Range("E19").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("E19").Borders(xlEdgeRight).Weight = xlHairline
Range("F19").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("F19").Borders(xlEdgeRight).Weight = xlThin
Range("B20:F20").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B20:F20").Borders(xlEdgeBottom).Weight = xlThin
Range("D20").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("D20").Borders(xlEdgeRight).Weight = xlHairline
Range("E20").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("E20").Borders(xlEdgeRight).Weight = xlHairline
Range("F20").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("F20").Borders(xlEdgeRight).Weight = xlThin
Range("H20") = "Order Total:"
Range("I20").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("I20").Borders(xlEdgeBottom).Weight = xlHairline
Rem Change the widths and heights of some columns and rows
Rem In previous lessons, we learned all these things
Range("E:E, G:G").ColumnWidth = 4
Columns("H").ColumnWidth = 14
Columns("J").ColumnWidth = 1.75
Rows("3").RowHeight = 2
Range("8:8, 12:12").RowHeight = 8
Rem Merge the cells H15, I15, H16, and I16
Range("H15:I16").MergeCells = True
Rem Align the merged text to the left
Range("H15:H16").VerticalAlignment = xlBottom
Range("H16").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("H16:I16").Borders(xlEdgeBottom).Weight = xlMedium
Range("H16:I16").Borders(xlEdgeBottom).ThemeColor = 5
Rem Hide the gridlines
ActiveWindow.DisplayGridlines = False
End Sub
To return to Microsoft Excel, click the View Microsoft Excel button 
10.
To fill the worksheet, press Ctrl + Shift + W
11.
Introduction to Formulas
A formula is another name for an expression. It combines one or more values, one or more
variables, to an operator, to produce a new value. This also means that you use the same
approach or building an expression when creating a formula.
To assist you with assigning the result of a formula to a cell or a group of cells, the Range class is
equipped with a property named Formula. This property is of type Variant, which means its value
can be anything, not necessarily a number. After accessing the Formula property, you can assign
whatever value, expression, or formula you want to it. Here are examples:
Sub Exercise()
Rem Using the Formula property to assign a string to the active cell
ActiveCell.Formula = "Weekly Salary:"
Rem Using the Formula property to assign an expression to cell B2
Range("B2").Formula = 24.5 * 42.5
Rem  Using the Formula property to assign
Rem the same string to a group of cells
Range("C2:F5, B8:D12").Formula = "Antoinette"
End Sub
If you are creating a worksheet that would be used on computers of different languages, use
FormulaLocal instead. The FormulaLocal property is equipped to adapt to a different
language-based version of Microsoft Excel when necessary.
Besides Formula, the Range class is also equipped with a property named FormulaR1C1. Its
functionality is primarily the same as Formula. Here are examples:
Sub Exercise()
Rem Using the Formula property to assign a string to the active cell
ActiveCell.FormulaR1C1 = "Weekly Salary:"
Rem Using the Formula property to assign an expression to cell B2
Range("B2").FormulaR1C1 = 24.5 * 42.5
Rem  Using the Formula property to assign
Rem the same string to a group of cells
Range("C2:F5, B8:D12").FormulaR1C1 = "Antoinette"
End Sub
If you are creating the worksheet for various languages, use FormulaR1C1Local instead.
Practical Learning: Creating Formulas
In the Developer tab of the Ribbon and in the Code section, click Record Macro 
1.
http://www.functionx.com/vbaexcel/Lesson19.htm
149
Set the Macro Name to CalculateOrder
2.
In the Shortcut Key text box, type C to get Ctrl + Shift + C
3.
Click OK
4.
On the Ribbon, click Stop Recording
5.
In the Code section of the Ribbon, click Macros 
6.
In the Macro dialog box, make sure CalculateOrder is selected and click Edit
7.
Change the code as follows:
Sub CalculateOrder()
'
' CreateWorkbook Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
'
Rem Calculate the sub-total of each category of items as:
Rem SubTotal = Unit Price * Quantity
Rem And display the total in the equivalent F cell
Range("F15").Formula = Range("D15") * Range("E15")
Range("F16").Formula = Range("D16") * Range("E16")
Range("F17").Formula = Range("D17") * Range("E17")
Range("F18").Formula = Range("D18") * Range("E18")
Range("F19").Formula = Range("D19") * Range("E19")
Range("F20").Formula = Range("D20") * Range("E20")
Rem Retrieve the values of the cleaning total and the tax rate
Rem Use them to calculate the amount of tax
Range("I19").Formula = Range("I17") * Range("I18") / 100
Rem Calculate the total order by adding 
Rem the cleaning total to the tax amount
Range("I20").Formula = Range("I17") + Range("I19")
End Sub
8.
Fundamentals of Built-In Functions
Introduction
Instead of creating your own function, you can use one of those that ship with the VBA language.
This language provides a very extensive library of functions so that, before creating your own,
check whether the function exists already. If so, use it instead.
To use a VBA built-in function, simply use as you would an expression. That is, assign its returned
value to a cell. Here is an example:
Sub Exercise()
Range("B2:B2") = Len("Paul Bertrand Yamaguchi")
End Sub
Microsoft Excel Built-In Functions
To assist you with developing smart worksheets, Microsoft Excel provides a very large library of
functions.
To use a Microsoft Excel built-in function in your code, you have many functions.
In double-quotes, you can include the assignment operator followed by the function's whole
expression. Here is an example:
Sub Exercise()
Range("B5:B5") = "=SomeFunction(B2, B3, B4)"
End Sub
As an alternative, the Application class is equipped with a property named WorksheetFunction.
This property represents all functions of the Microsoft Excel library. Therefore, to access a
function, type Application, followed by a period, followed by a period. Then type (or select the
name of the function you want to use:
http://www.functionx.com/vbaexcel/Lesson19.htm
150
Documents you may be interested
Documents you may be interested