Visual Basic for Applications (VBA) for the Microsoft 2007 Office System Language Reference

1. Microsoft forms Reference --> Helps on Userform and controls and how to program with them using Visual Basic

2. Visual Basic Conceptual Topics --> Helps you to understand Visual Basic Programming

3. Visual Basic Definition --> it is a glossary definition to help you understand concepts presented in this documentation

4. Visual Basic How-To Topics --> To look for useful common procedure, for example, how to use the Object Browser or how to set Visual Basic Environment options

5. Visual Basic Language Reference: The language: all its methods, properties, statements, functions, operators, and objects

6. Visual Basic Add-in model
If you want to customize the Visual Basic Editor, see this language reference for Help on the object model that allows you to extend the environment.

7. Visual Basic User Interface help
Look here for help on interface elements of the Visual Basic Editor, such as commands, dialog boxes, windows, and toolbars

 

How to: Reference Cells and Ranges

A common task when using Visual Basic is to specify a cell or range of cells and then do something with it, such as enter a formula or change the format. You can usually do this in one statement that identifies the range and also changes a property or applies a method.
A Range object in Visual Basic can be either a single cell or a range of cells. The following topics show the most common ways to identify and work with Range objects.

Some ways to reference cells

How to: Refer to All the Cells on the Worksheet

How to: Refer to Cells and Ranges by Using A1 Notation

How to: Refer to Rows and Columns

How to: Refer to Cells by Using Index Numbers

How to: Refer to Cells by Using Shortcut Notation

How to: Refer to Cells Relative to Other Cells

How to: Refer to Cells by Using a Range Object

How to: Refer to Named Ranges

How to: Refer to Multiple Ranges

How to Reference Cells and Ranges

How to: Add a Table of Contents to a Workbook

The following code example was provided by MVP Bill Jelen. Bill is the author of 24 books on Microsoft Office Excel. He is a regular guest on TechTV with Leo Laporte and the host of MrExcel.com, which includes more than 300,000 questions and answers about Excel.

The following code example verifies that a sheet named "TOC" already exists. If it exists, the example updates the table of contents. Otherwise, the example creates a new TOC sheet at the beginning of the workbook. The name of each worksheet, along with the corresponding printed page numbers, is listed in the table of contents. To retrieve the page numbers the example opens the Print Preview dialog box. You must close the dialog box and then the table of contents is created.

Sub CreateTableOfContents() ' Determine if there is already a Table of Contents ' Assume it is there, and if it is not, it will raise an error ' if the Err system variable is > 0, you know the sheet is not there Dim WST As Worksheet On Error Resume Next Set WST = Worksheets("Table of Contents") If Not Err = 0 Then ' The Table of contents doesn't exist. Add it Set WST = Worksheets.Add(Before:=Worksheets(1)) WST.Name = "TOC" End If On Error GoTo 0 ' Set up the table of contents page WST.[A2] = "Table of Contents" With WST.[A6] .CurrentRegion.Clear .Value = "Subject" End With WST.[B6] = "Page(s)" WST.Range("A1:B1").ColumnWidth = Array(36, 12) TOCRow = 7 PageCount = 0 ' Do a print preview on all sheets so Excel calcs page breaks ' The user must manually close the PrintPreview window Msg = "Excel needs to do a print preview to calculate the number of pages. " Msg = Msg & "Please dismiss the print preview by clicking close." MsgBox Msg ActiveWindow.SelectedSheets.PrintPreview ' Loop through each sheet, collecting TOC information For Each S In Worksheets If S.Visible = -1 Then S.Select ThisName = ActiveSheet.Name HPages = ActiveSheet.HPageBreaks.Count + 1 VPages = ActiveSheet.VPageBreaks.Count + 1 ThisPages = HPages * VPages ' Enter info about this sheet on TOC Sheets("TOC").Select Range("A" & TOCRow).Value = ThisName Range("B" & TOCRow).NumberFormat = "@" If ThisPages = 1 Then Range("B" & TOCRow).Value = PageCount + 1 & " " Else Range("B" & TOCRow).Value = PageCount + 1 & " - " & PageCount + ThisPages End If PageCount = PageCount + ThisPages TOCRow = TOCRow + 1 End If Next S End Sub

Saving Documents as Web Pages

In Microsoft Excel, you can save a workbook, worksheet, chart, range, query table, PivotTable report, print area, or AutoFilter range to a Web page. You can also edit HTML files directly in Excel.

Saving a Document as a Web Page

Saving a document as a Web page is the process of creating and saving an HTML file and any supporting files. To do this, use the SaveAs method, as shown in the following example, which saves the active workbook as C:\Reports\myfile.htm.

ActiveWorkbook.SaveAs _ Filename:="C:\Reports\myfile.htm", _ FileFormat:=xlHTML

Customizing the Web Page

You can customize the appearance, content, browser support, editing support, graphics formats, screen resolution, file organization, and encoding of the HTML document by setting properties of the DefaultWebOptions object and the WebOptions object. The DefaultWebOptions object contains application-level properties. These settings are overridden by any workbook-level property settings that have the same names (these are contained in the WebOptions object).
After setting the attributes, you can use the Publish method to save the workbook, worksheet, chart, range, query table, PivotTable report, print area, or AutoFilter range to a Web page. The following example sets various application-level properties and then sets the AllowPNG property of the active workbook, overriding the application-level default setting. Finally, the example saves the range as "C:\Reports\1998_Q1.htm."

With Application.DefaultWebOptions .RelyonVML = True .AllowPNG = True .PixelsPerInch = 96 End With With ActiveWorkbook .WebOptions.AllowPNG = False With .PublishObjects(1) .FileName = "C:\Reports\1998_Q1.htm" .Publish End With End With

You can also save the files directly to a Web server. The following example saves a range to a Web server, giving the Web page the URL address http://example.homepage.com/annualreport.htm.

With ActiveWorkbook With .WebOptions .RelyonVML = True .PixelsPerInch = 96 End With With .PublishObjects(1) .FileName = _ "http://example.homepage.com/annualreport.htm" .Publish End With End With

Opening an HTML Document in Microsoft Excel

To edit an HTML document in Excel, first open the document by using the Open method. The following example opens the file "C:\Reports\1997_Q4.htm" for editing.

Workbooks.Open Filename:="C:\Reports\1997_Q4.htm"

After opening the file, you can customize the appearance, content, browser support, editing support, graphics formats, screen resolution, file organization, and encoding of the HTML document by setting properties of the DefaultWebOptions and WebOptions objects.

Controlling One Microsoft Office Application from Another

If you want to run code in one Microsoft Office application that works with the objects in another application, follow these steps.

1. Set a reference to the other application's type library in the References dialog box (Tools menu). After you have done this, the objects, properties, and methods will show up in the Object Browser and the syntax will be checked at compile time. You can also get context-sensitive Help on them.
2. Declare object variables that will refer to the objects in the other application as specific types. Make sure you qualify each type with the name of the application that is supplying the object. For example, the following statement declares a variable that points to a Microsoft Word document and another that refers to a Microsoft Excel workbook:

   Dim appWD As Word.Application, wbXL As Excel.Workbook

   Note
   You must follow the preceding steps if you want your code to be early bound.

3. Use the CreateObject function with the OLE Programmatic Identifiers of the object you want to work with in the other application, as shown in the following example. If you want to see the session of the other application, set the Visible property to True.

   Dim appWD As Word.Application Set appWD = CreateObject("Word.Application") appWd.Visible = True

4. Apply properties and methods to the object contained in the variable. For example, the following instruction creates a new Word document.

   Dim appWD As Word.Application Set appWD = CreateObject("Word.Application") appWD.Documents.Add

5. When you are done working with the other application, use the Quit method to close it, as shown in the following example.

   appWd.Quit

Microsoft Excel 2007 help's table of content

Accessibility
Activating Excel
Add-ins
Automation and programmability
Charts
Collaboration
Customizing
Excel and the web
Excel demos
Exchanging data with other programs
File conversion and compatibility
Filtering, sorting and conditionally formating
Forms
Formula and name basics
Function reference
Getting help
Glossary
Importing data
Installing
Macros
PivotTable reports and PivotCharts reports
Publishing to SharePoint server excel services
Saving and printing
Security and privacy
Summarizing, consolidating, and outlining data
Trainning
Validating data
What-if analysis
What's new
Workbook management
Working in different language
Working with XML
Worksheet and Excel table basic

Concepts
Functions
Workbooks and Worksheets
Cells and Ranges
Controls, Dialog boxes and Forms
Events, Worksheet Functions, and Shapes
Working with Other Applications
 

Electronic Workbench 5.12

1. Sources
1.1 Ground
1.2 Battery
1.3 DC Current Source
1.4 AC Voltage Source
1.5 AC Current Source
1.6 Voltage-Controlled Voltage Source
1.7 Voltage-Controlled Current Source
1.8 Current-Controlled Voltage Source
1.9 Current-Controlled Current Source
1.10 +Vcc Voltage Source
1.11 +Vdd Voltage Source
1.12 Clock
1.13 AM Source
1.14 FM Source
1.15 Voltage-Controlled Sine Wave Oscillator
1.16 Voltage-Controlled Triangle Wave Oscillator
1.17 Voltage-Controlled Square Wave Oscillator
1.18 Controlled One-Shot
1.19 Piecewise Linear Source
1.20 Voltage-Controlled Piecewise Linear Source
1.21 Frequency-Shift-Keying Source
1.22 Polynomial Source
1.23 Nonlinear Dependent Source

2. Basic
2.1 Connector
2.2 Resistor
2.3 Capacitor
2.4 Inductor
2.5 Transformer
2.6 Relay
2.7 Switch
2.8 Time-delay switch
2.9 Voltage-Controlled switch
2.10 Current-Controlled switch
2.11 Pull-up resistor
2.12 Potentiometer
2.13 Resistor pack
2.14 Voltage-controlled switch
2.15 Polarized Capacitor
2.16 Variable Capacitor
2.17 Variable Inductor
2.18 Coreless Coil
2.19 Magnetic Core
2.20 Nonlinear Transformer

3. Diodes
3.1 Diode
3.2 Zener Diode
3.3 LED
3.4 Full-Wave Bridge Rectifier
3.5 Shockley Diode
3.6 Silicon-Controlled Rectifier
3.7 Diac
3.8 Triac

4. Transistors
4.1 NPN Transistor
4.2 N-Channel JFET
4.3 P-Channel JFET
4.4 3-Terminal Depletion N-MOSFET
4.5 3-Terminal Depletion P-MOSFET
4.6 4-Terminal Depletion N-MOSFET
4.7 4-Terminal Depletion P-MOSFET
4.8 3-Terminal Enhancement N-MOSFET
4.9 3-Terminal Enhancement P-MOSFET
4.10 4-Terminal Enhancement N-MOSFET
4.11 4-Terminal Enhancement P-MOSFET
4.12 N-Channel GaAsFET
4.13 P-Channel GaAsFET

5. Analog ICs
5.1 3-Terminal Opamp
5.2 5-Terminal Opamp
5.3 7-Terminal Opamp
5.4 9-Terminal Opamp
5.5 Comparator
5.6 Phase Locked-Loop


6. Mixed ICs
6.1 Analog-to-digital converter
6.2 Digital-to-analog converter (I)
6.3 Digital-to-analog converter (V)
6.4 Monostable Multivibrator
6.5 555 Timer

7. Digital ICs
7.1 74XX templates
7.2 741XX Template
7.3 742XX Templates
7.4 743XX Templates
7.5 744XX Templates
7.6 4XXX Templates

8. Logic Gates
8.1 2-Input AND gate
8.2 2-Input OR gate
8.3 NOT gate
8.4 2-Input NOR gate
8.5 2-Input NAND gate
8.6 2-Input XOR gate
8.7 2-Input XNOR gate
8.8 Tristate buffer
8.9 Buffer
8.10 Schmitt-triggered buffer
8.11 AND
8.12 OR
8.13 NAND
8.14 NOR
8.15 NOT
8.16 XOR
8.17 XNOR
8.18 BUFFERS

9. Digital
9.1 Half Adder
9.2 Full Adder
9.3 RS Flip-flop
9.4 JK Flip-flop with  Active High Asynch Inputs
9.5 JK Flip-flop with Active Low Asynch Inputs
9.6 D Flip-flop
9.7 D Flip-flop with Active Low Asynch Inputs
9.8 Multiplexer (MUX)
9.9 Demultiplexer
9.10 Encoder
9.11 Arithmetic
9.12 Counters
9.13 Shift Registers
9.14 Flip-flops

10. Indicators
10.1 Voltemeter
10.2 Ammeter
10.3 Bulb
10.4 Red Probe
10.5 Seven-Segment Display
10.6 Decoded Seven-Segment Display
10.7 Buzzer
10.8 Bargraph Display
10.9 Decoded Bargraph Display

11. Controls
11.1 Voltage Differentiator
11.2 Voltage Integrator
11.3 Voltage Gain Block
11.4 Transfer Function Block
11.5 Multiplier
11.6 Divider
11.7 Three-way voltage summer
11.8 Voltage Limiter
11.9 Voltage-controlled limiter
11.10 Current-limiter block
11.11 Voltage hysteresis block
11.12 Voltage slew rate block

12. Miscellaneous
12.1 Fuse
12.2 Write Data
12.3 Netlist component
12.4 Lossy Transmission Line
12.5 Lossless Transmission Line
12.6 Crystal
12.7 DC Motor
12.8 Triode Vacuum Tube
12.9 Boost (Step-up) Converter
12.10 Buck (Step-down) Converter
12.11 Buck-boost converter
12.12 Textbox
12.13 Title Block

13. Instruments
13.1. Multimeter
13.2. Function Generator
13.3. Oscilloscope
13.4. Bode Potter
13.5. Word Generator
13.6. Logic Analyzer
13.7. Logic Converter

Link untuk download Electronic Workbench 5.12 : Disini