Tuesday, November 25, 2014

Tomcat

Apache Tomcat Servlet/JSP container.

Apache Tomcat version 7.0 implements the Servlet 3.0 and JavaServer Pages 2.2
-------
$CATALINA_HOME: root of your Tomcat installation.
Optionally, Tomcat may be configured for multiple instances by defining $CATALINA_BASE for each instance. If multiple instances are not configured, $CATALINA_BASE is the same as $CATALINA_HOME.
---------
These are some of the key tomcat directories:
/bin - Startup, shutdown, and other scripts. The *.sh files (for Unix systems) are functional duplicates of the *.bat files (for Windows systems).
/conf - Configuration files and related DTDs. The most important file in here is server.xml. It is the main configuration file for the container.
/logs - Log files are here by default.
/webapps - This is where your webapps go.
----------
All of the information in the configuration files is read at startup, meaning that any change to the files necessitates a restart of the container.
--------
Installing Tomcat on Windows can be done using the Windows installer.
Installation as a service:Tomcat is automatically started when Windows starts. For optimal security, the service should be run as a separate user, with reduced permissions.
-----
Tomcat 7.0 was designed to run on Java SE 6.
---------
set an environment variable CATALINA_HOME that contains the pathname to the directory in which Tomcat has been installed. Optionally, if Tomcat has been configured for multiple instances, each instance will have its own CATALINA_BASE configured.
---------
WAR: Web Application Archive
A web application is defined as a hierarchy of directories and files in a standard layout. Such a hierarchy can be accessed in its "unpacked" form, where each directory and file exists in the filesystem separately, or in a "packed" form known as a Web ARchive, or WAR file.
---------
The top-level directory of your web application hierarchy is also the document root of your application. Here, you will place the HTML files and JSP pages that comprise your application's user interface. When the system administrator deploys your application into a particular server, he or she assigns a context path to your application. Thus, if the system administrator assigns your application to the context path
/catalog
, then a request URI referring to
/catalog/index.html
 will retrieve the index.html file from your document root.
-------
document root directory:
    *.html, *.jsp, etc. - The HTML and JSP pages, along with other files that must be visible to the client browser (such as JavaScript, stylesheet files, and images) for your application. In larger applications you may choose to divide these files into a subdirectory hierarchy, but for smaller apps, it is generally much simpler to maintain only a single directory for these files.
    /WEB-INF/web.xml - The Web Application Deployment Descriptor for your application. This is an XML file describing the servlets and other components that make up your application, along with any initialization parameters and container-managed security constraints that you want the server to enforce for you.
    /WEB-INF/classes/ - This directory contains any Java class files (and associated resources) required for your application, including both servlet and non-servlet classes, that are not combined into JAR files. If your classes are organized into Java packages, you must reflect this in the directory hierarchy under /WEB-INF/classes/. For example, a Java class named com.mycompany.mypackage.MyServlet would need to be stored in a file named /WEB-INF/classes/com/mycompany/mypackage/MyServlet.class.
    /WEB-INF/lib/ - This directory contains JAR files that contain Java class files (and associated resources) required for your application, such as third party class libraries or JDBC drivers.
-----------
When you install an application into Tomcat, the classes in the WEB-INF/classes/ directory, as well as all classes in JAR files found in the WEB-INF/lib/ directory, are made visible to other classes within your particular web application.
----------
/WEB-INF/web.xml file contains the Web Application Deployment Descriptor for your application. It defines everything about your application that a server needs to know (except the context path, which is assigned by the system administrator when the application is deployed).
-----------
DTD: Document Type Descriptor
---------
META-INF/context.xml file can be used to define Tomcat specific configuration options, such as an access log, data sources, session manager configuration and more.
-------------
In order to be executed, a web application must be deployed on a servlet container.
----------
A web application can be deployed in Tomcat by one of the following approaches:
・Copy unpacked directory hierarchy into a subdirectory in directory $CATALINA_BASE/webapps/. Tomcat will assign a context path to your application based on the subdirectory name you choose.
・Copy the web application archive file into directory $CATALINA_BASE/webapps/. When Tomcat is started, it will automatically expand the web application archive file into its unpacked form, and execute the application that way.
・Use the Tomcat "Manager" web application to deploy and undeploy web applications. Tomcat includes a web application, deployed by default on context path /manager, that allows you to deploy and undeploy applications on a running Tomcat server without restarting it.
・Use "Manager" Ant Tasks In Your Build Script. Tomcat includes a set of custom task definitions for the Ant build tool that allow you to automate the execution of commands to the "Manager" web application.
・Use the Tomcat Deployer. Tomcat includes a packaged tool bundling the Ant tasks, and can be used to automatically precompile JSPs which are part of the web application before deployment to the server.
--------
source code files

All of these components exist under a top level project source directory for your application:
    docs/ - Documentation for your application, in whatever format your development team is using.
    src/ - Java source files that generate the servlets, beans, and other Java classes that are unique to your application. If your source code is organized in packages (highly recommended), the package hierarchy should be reflected as a directory structure underneath this directory.
    web/ - The static content of your web site (HTML pages, JSP pages, JavaScript files, CSS stylesheet files, and images) that will be accessible to application clients. This directory will be the document root of your web application, and any subdirectory structure found here will be reflected in the request URIs required to access those files.
    web/WEB-INF/ - The special configuration files required for your application, including the web application deployment descriptor (web.xml, defined in the Servlet Specification), tag library descriptors for custom tag libraries you have created, and other resource files you wish to include within your web application. Even though this directory appears to be a subdirectory of your document root, the Servlet Specification prohibits serving the contents of this directory (or any file it contains) directly to a client request. Therefore, this is a good place to store configuration information that is sensitive (such as database connection usernames and passwords), but is required for your application to operate successfully.
-----------

Apache HTTP Server

Apache HTTP Server

Apache is configured by the files in the conf subdirectory.
ThreadsPerChild: It tells the server how many threads it should use. This is the maximum number of connections the server can handle at once, so be sure to set this number high enough for your site if you get a lot of hits. The recommended default is ThreadsPerChild 150.
You can review the Windows Application Event Log by using the Event Viewer, e.g. Start - Settings - Control Panel - Administrative Tools - Event Viewer.
-------
install Apache as a Windows NT service from the command prompt at the Apache bin subdirectory:
httpd.exe -k install
--------
Removing an Apache service
httpd.exe -k uninstall
---------
If Apache is running as a service, stop it by opening another console window and entering:
httpd.exe -k shutdown
------
To run Apache from the command line as a console application:
httpd.exe
Press control-C to stop
---------
Running as a service should be preferred over running in a console window because this lets Apache end any current operations and clean up gracefully.
-------
to restart. It forces it to reread the configuration file. Any operations in progress are allowed to complete without interruption. Press Control-Break in the console window you used for starting Apache, or if the server is running as a service, enter:
httpd.exe -k restart
--------
specifies an absolute path to a particular configuration file:
httpd.exe -f "c:\my server files\anotherconfig.conf"

specifies a relative path to a particular configuration file:
httpd.exe -f files\anotherconfig.conf
-----------
If you don't specify a configuration file with -f or -n, Apache will use the file name compiled into the server, such as conf\httpd.conf. This built-in path is relative to the installation directory.
----------
After starting Apache (either in a console window or as a service) it will be listening on port 80.
-----
To connect to the server and access the default page, launch a browser and enter this URL:
http://localhost/
You may have to use this URL:
http://127.0.0.1/
If you happen to be running Apache on an alternate port, you need to explicitly put that in the URL:
http://127.0.0.1:8080/
-------
Addresses on the Web are expressed with URLs - Uniform Resource Locators - which specify a protocol (e.g. http), a servername (e.g. www.apache.org), a URL-path (e.g. /docs/current/getting-started.html), and possibly a query string (e.g. ?arg=value) used to pass additional arguments to the server.
A client (e.g., a web browser) connects to a server (e.g., your Apache HTTP Server), with the specified protocol, and makes a request for a resource using the URL-path.
---------
In order to connect to a server, the client will first have to resolve the servername to an IP address - the location on the Internet where the server resides. Thus, in order for your web server to be reachable, it is necessary that the servername be in DNS.
More than one hostname may point to the same IP address, and more than one IP address can be attached to the same physical server. Thus, you can run more than one web site on the same physical server, using a feature called virtual hosts.
-------
The default configuration file is usually called httpd.conf.
The configuration is frequently broken into multiple smaller files, for ease of management. These files are loaded via the Include directive.
The server is configured by placing configuration directives in these configuration files. A directive is a keyword followed by one or more arguments that set its value.
--------
Static content is things like HTML files, image files, CSS files, and other files that reside in the filesystem. The DocumentRoot directive specifies where in your filesystem you should place these files.
----------
Typically, a document called index.html will be served when a directory is requested without a file name being specified.
---------
The location of the error log is defined by the ErrorLog directive, which may be set globally, or per virtual host.
----------
When httpd starts, it binds to some port and address on the local machine and waits for incoming requests. By default, it listens to all addresses on the machine.
--------
The Listen directive tells the server to accept incoming requests only on the specified port(s) or address-and-port combinations.
For example, to make the server accept connections on both port 80 and port 8000, on all interfaces, use:
Listen 80
Listen 8000
-------
https is the default for port 443 and http the default for all other ports.
-----
You only need to set the protocol if you are running on non-standard ports. For example, running an https site on port 8443:
Listen 192.170.2.1:8443 https
-------
httpd configuration files contain one directive per line. The backslash "\" may be used as the last character on a line to indicate that the directive continues onto the next line. There must be no other characters or white space between the backslash and the end of the line.
Arguments to directives are separated by whitespace. If an argument contains spaces, you must enclose that argument in quotes.
Directives in the configuration files are case-insensitive, but arguments to directives are often case sensitive. Lines that begin with the hash character "#" are considered comments, and are ignored. Comments may not be included on a line after a configuration directive. Blank lines and white space occurring before a directive are ignored, so you may indent directives for clarity.
--------
Only shell environment variables defined before the server is started can be used in expansions.
-------
You can check your configuration files for syntax errors without starting the server by using
apachectl configtest
or the -t command line option.
--------
To see which modules are currently compiled into the server, you can use the -l command line option. You can also see what modules are loaded dynamically using the -M command line option.
--------
Directives placed in the main configuration files apply to the entire server. If you wish to change the configuration for only a part of the server, you can scope your directives by placing them in <Directory>, <DirectoryMatch>, <Files>, <FilesMatch>, <Location>, and <LocationMatch> sections. These sections limit the application of the directives which they enclose to particular filesystem locations or URLs. They can also be nested, allowing for very fine grained configuration.
----------
httpd has the capability to serve many different websites simultaneously. This is called Virtual Hosting. Directives can also be scoped by placing them inside <VirtualHost> sections, so that they will only apply to requests for a particular website.
--------
httpd allows for decentralized management of configuration via special files placed inside the web tree. The special files are usually called .htaccess, but any name can be specified in the AccessFileName directive. Directives placed in .htaccess files apply to the directory where you place the file, and all sub-directories. The .htaccess files follow the same syntax as the main configuration files. Since .htaccess files are read on every request, changes made in these files take immediate effect.
----------
In deciding what file to serve for a given request, httpd's default behavior is to take the URL-Path for the request (the part of the URL following the hostname and port) and add it to the end of the DocumentRoot specified in your configuration files. Therefore, the files and directories underneath the DocumentRoot make up the basic document tree which will be visible from the web.
For example, if DocumentRoot were set to
/var/www/html
 then a request for
http://www.example.com/fish/guppies.html
would result in the file
/var/www/html/fish/guppies.html
being served to the requesting client.

If a directory is requested (i.e. a path ending with /), the file served from that directory is defined by the DirectoryIndex directive. For example, if DocumentRoot were set as above, and you were to set:
DirectoryIndex index.html index.php
Then a request for http://www.example.com/fish/ will cause httpd to attempt to serve the file /var/www/html/fish/index.html. In the event that that file does not exist, it will next attempt to serve the file /var/www/html/fish/index.php.
--------
There are frequently circumstances where it is necessary to allow web access to parts of the filesystem that are not strictly underneath the DocumentRoot.
the Alias directive will map any part of the filesystem into the web space. For example, with
Alias /docs /var/web
the URL http://www.example.com/docs/dir/file.html will be served from /var/web/dir/file.html. The ScriptAlias directive works the same way, with the additional effect that all content located at the target path is treated as CGI scripts.
---------
Sometimes, it is desirable instead to inform the client that the requested content is located at a different URL, and instruct the client to make a new request with the new URL. This is called redirection and is implemented by the Redirect directive. For example, if the contents of the directory /foo/ under the DocumentRoot are moved to the new directory /bar/, you can instruct clients to request the content at the new location as follows:
Redirect permanent /foo/   http://www.example.com/bar/
This will redirect any URL-Path starting in /foo/ to the same URL path on the www.example.com server with /bar/ substituted for /foo/. You can redirect clients to any server, not only the origin server.
----------

Monday, November 24, 2014

MySQL

MySQL
RDBMS: relational database management system
SQL: Structured Query Language
LAMP: Linux, Apache, MySQL, Perl/PHP/Python
MySQL Workbench: free integrated environment developed by MySQL AB, that enables users to graphically administer MySQL databases and visually design database structures. 
---------
ODBC: Open Database Connectivity
user account
privilege
----------
foreign key references
--------
trigger
---------
Ensuring high availability requires a certain amount of redundancy in the system. For database systems, the redundancy traditionally takes the form of having a primary server acting as a master, and using replication to keep secondaries available to take over in case the primary fails. This means that the "server" that the application connects to is in reality a collection of servers, not a single server. In a similar manner, if the application is using a sharded database, it is in reality working with a collection of servers, not a single server. In this case, a collection of servers is usually referred to as a farm.
---------
shell> mysql db_name
shell> mysql --user=user_name --password=your_password db_name
shell> mysql db_name < script.sql > output.tab

--column-names Write column names in results
--tee=file_name Append a copy of output to named file
--help, -?
--auto-vertical-output: Cause result sets to be displayed vertically if they are too wide for the current window, and using normal
tabular format otherwise. (This applies to statements terminated by ; or \G.)
--default-character-set=charset_name: Use charset_name as the default character set for the client and connection. A common issue that can occur when the operating system uses utf8 or another multibyte character set is that output from the mysql client is formatted incorrectly, due to the fact that the MySQL client uses the latin1 character set by default. You can usually fix such issues by using this option to force the client to use the system character set instead.
quit
exit: Exit mysql. Same as quit.
help: Display this help.
tee:  Set outfile [to_outfile]. Append everything into given outfile.
use:  Use another database. Takes database name as argument.
---------
The prompt command reconfigures the default mysql> prompt. The string for defining the prompt can
contain the following special sequences.
\d The default database
\h The server host
\p The current TCP/IP port or socket file
\U Your full user_name@host_name account name
\u Your user name
\v The server version
-------
SELECT 'Today is' , CURDATE();
--------
mysqladmin

 create db_name
 debug: Tell the server to write debug information to the error log.
 drop db_name
 extended-status: Display the server status variables and their values.
 flush-privileges: Reload the grant tables
 password new-password
 ping:  Check whether the server is available. The return status from mysqladmin is 0 if the server is running, 1 if it is not. This is 0 even in case of an error such as Access denied, because this means that the server is running but refused the connection, which is different from the server not running.
 shutdown: Stop the server.
 variables:  Display the server system variables and their values.
 version:  Display version information from the server.
------------
shell> mysql --help
shell> mysql -h host -u user -p
Enter password: ********
mysql> QUIT
SELECT VERSION(), CURRENT_DATE;
SELECT SIN(PI()/4), (4+1)*5;
SELECT VERSION(); SELECT NOW();
SELECT USER(),CURRENT_DATE;
SELECT USER();
SELECT * FROM my_table WHERE name = 'Smith' AND age < 30;
SHOW DATABASES;
USE test; use the database called "test"
GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';
CREATE DATABASE menagerie;
USE menagerie
shell> mysql -h host -u user -p menagerie
SELECT DATABASE()  : to see which database is currently selected
SHOW TABLES;
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
DESCRIBE pet;
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet; (to create a text file pet.txt containing one record per line, with values separated by tabs, and given in the order in which the columns were listed in the CREATE TABLE statement. For missing values
you can use NULL values. To represent these in your text file, use \N (backslash, capital-N).)
INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
SELECT * FROM pet;
DELETE FROM pet;
UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
SELECT * FROM pet WHERE name = 'Bowser';
SELECT * FROM pet WHERE birth >= '1998-1-1';
SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f');
SELECT name, birth FROM pet;
SELECT owner FROM pet;
SELECT DISTINCT owner FROM pet;
SELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat';
SELECT name, birth FROM pet ORDER BY birth;
SELECT name, birth FROM pet ORDER BY birth DESC;
SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;
SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY name;
SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY age;
SELECT name, birth, death, TIMESTAMPDIFF(YEAR,birth,death) AS age FROM pet WHERE death IS NOT NULL ORDER BY age;
SELECT name, birth, MONTH(birth) FROM pet;
SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
SELECT name, birth FROM pet WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
SELECT 1 IS NULL, 1 IS NOT NULL;
SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
SELECT * FROM pet WHERE name LIKE 'b%';
SELECT * FROM pet WHERE name LIKE '%fy';
SELECT * FROM pet WHERE name LIKE '%w%';
SELECT * FROM pet WHERE name LIKE '_____';
SELECT * FROM pet WHERE name REGEXP '^b';
SELECT * FROM pet WHERE name REGEXP BINARY '^b';
SELECT * FROM pet WHERE name REGEXP 'fy$';
SELECT * FROM pet WHERE name REGEXP 'w';
SELECT * FROM pet WHERE name REGEXP '^.....$';
SELECT * FROM pet WHERE name REGEXP '^.{5}$';
SELECT COUNT(*) FROM pet;
SELECT owner, COUNT(*) FROM pet GROUP BY owner;
SELECT species, COUNT(*) FROM pet GROUP BY species;
SELECT sex, COUNT(*) FROM pet GROUP BY sex;
SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
SELECT species, sex, COUNT(*) FROM pet WHERE species = 'dog' OR species = 'cat' GROUP BY species, sex;
SELECT species, sex, COUNT(*) FROM pet WHERE sex IS NOT NULL GROUP BY species, sex;
CREATE TABLE event (name VARCHAR(20), date DATE, type VARCHAR(15), remark VARCHAR(255));
LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;
SELECT pet.name, (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age, remark FROM pet INNER JOIN event ON pet.name = event.name WHERE event.type = 'litter';
SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species FROM pet AS p1 INNER JOIN pet AS p2 ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
SELECT DATABASE();
SELECT MAX(article) AS article FROM shop;
SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop);
SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.price < s2.price WHERE s2.article IS NULL;
SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1;
SELECT article, MAX(price) AS price FROM shop GROUP BY article;
SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article);
SELECT s1.article, dealer, s1.price FROM shop s1 JOIN ( SELECT article, MAX(price) AS price FROM shop GROUP BY article) AS s2 ON s1.article = s2.article AND s1.price = s2.price;
SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL;
SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);
INSERT INTO person VALUES (NULL, 'Antonio Paz');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);
SELECT * FROM person;
SELECT * FROM shirt;
SELECT s.* FROM person p INNER JOIN shirt s ON s.owner = p.id WHERE p.name LIKE 'Lilliana%' AND s.color <> 'white';
CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) );
INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'), ('lax'),('whale'),('ostrich');
SELECT * FROM animals;
-----------
A string is a sequence of bytes or characters, enclosed within either single quote (“'”) or double quote (“"”) characters. Examples:
'a string'
"another string"
Quoted strings placed next to each other are concatenated to a single string. The following lines are equivalent:
'a string'
'a' ' ' 'string'
--------------

Monday, October 13, 2014

VBA editor

-------
Insert > Macro > Module.
-------
To enable VBA:
File > Options > Customise the Ribbon > Developer box
-------
1. Right click anywhere on the ribbon, and then click Customize the Ribbon.
2. Under Customize the Ribbon, on the right side of the dialog box, select Main tabs (if necessary).
3. Check the Developer check box.
4. Click OK.
5. You can find the Developer tab next to the View tab.
-------
File > Options > Trust Center > Trust Center Settings > Macro settings > enable all macros.
-------
ツール >  マクロ > Visual Basic Editor
挿入 > 標準モジュール

プロジェクト ウィンドウ
プロパティ   ウィンドウ
コード         ウィンドウ

標準 モジュール
クラス モジュール
-------
Display messages to the Immediate window.

Debug.Print "message"
Debug.Print "variable1 = "; variable1
Debug.Print "The value of variable X is: " & X
Debug.Print X, Y, Z

Debug.Assert Var >= 0
This will pause on the Debug.Assert statement if Var >= 0 is False;

Type or paste a statement into the Immediate window, and then press ENTER.
print variable1
? variable1
? Backcolor
? Text1.Height
-------
To place a command button on your worksheet, execute the following steps.
1. On the Developer tab, click Insert.
2. In the ActiveX Controls group, click Command Button.
3. Drag a command button on your worksheet.
---------
To assign a macro (one or more code lines) to the command button, execute the following steps.
1. Right click CommandButton1 (make sure Design Mode is selected).
2. Click View Code.
3. Place your cursor between Private Sub CommandButton1_Click() and End Sub.
4. Add the code line shown below.
5. Close the Visual Basic Editor.
6. Click the command button on the sheet (make sure Design Mode is deselected).
--------
1. Open the Visual Basic Editor.
2. Double click on This Workbook in the Project Explorer.
3. Choose Workbook from the left drop-down list. Choose Open from the right drop-down list.
4. Add the following code line to the Workbook Open Event:
MsgBox "Good Morning"
5. Save, close and reopen the Excel file.
---------
1. Open the Visual Basic Editor.
2. Double click on a sheet (for example Sheet1) in the Project Explorer.
3. Choose Worksheet from the left drop-down list. Choose Change from the right drop-down list.
4. The Worksheet Change Event listens to all changes on Sheet1. We only want Excel VBA to do something if something changes in cell B2. To achieve this, add the following code lines:
If Target.Address = "$B$2" Then
End If
5. We only want Excel VBA to show a MsgBox if the user enters a value greater than 80. To achieve this, add the following code line between If and End If.
If Target.Value > 80 Then MsgBox "Goal Completed"
6. On Sheet1, enter a number greater than 80 into cell B2.

Thursday, October 9, 2014

VBA excel

--APPLICATION--
Properties:

ActiveCell : Returns a Range object that represents the active cell in the active window (the window on top) or in the specified window. If the window isn't displaying a worksheet, this property fails. Read-only.
ActiveChart : Returns a Chart object that represents the active chart (either an embedded chart or a chart sheet). An embedded chart is considered active when it's either selected or activated. When no chart is active, this property returns Nothing.
ActiveEncryptionSession : Read-only
ActivePrinter : Returns or sets the name of the active printer. Read/write String.
ActiveSheet : Returns an object that represents the active sheet (the sheet on top) in the active workbook or in the specified window or workbook. Returns Nothing if no sheet is active.
ActiveWindow : Returns a Window object that represents the active window (the window on top). Read-only. Returns Nothing if there are no windows open.
ActiveWorkbook : Returns a Workbook object that represents the workbook in the active window (the window on top). Read-only. Returns Nothing if there are no windows open or if either the Info window or the Clipboard window is the active window.

AddIns : Returns an AddIns collection that represents all the add-ins listed in the Add-Ins dialog box (Tools menu). Read-only.
AlertBeforeOverwriting : True if Microsoft Excel displays a message before overwriting nonblank cells during a drag-and-drop editing operation. Read/write Boolean.
AltStartupPath : Returns or sets the name of the alternate startup folder. Read/write String.
AlwaysUseClearType : Returns or sets a Boolean that represents whether to use ClearType to display fonts in the menu, Ribbon, and dialog box text. Read/write Boolean.
AnswerWizard : Returns the AnswerWizard object for Microsoft Excel. Read-only.
Application : When used without an object qualifier, this property returns an Application object that represents the Microsoft Excel application. When used with an object qualifier, this property returns an Application object that represents the creator of the specified object (you can use this property with an OLE Automation object to return the application of that object). Read-only.
ArbitraryXMLSupportAvailable : Returns a Boolean value that indicates whether the XML features in Microsoft Excel are available. Read-only.
AskToUpdateLinks : True if Microsoft Excel asks the user to update links when opening files with links. False if links are automatically updated with no dialog box. Read/write Boolean.
Assistance : Returns an IAssistance object for short_Excel2007 that represents the Microsoft Office Help Viewer. Read-only.
Assistant : Returns an Assistant object for Microsoft Excel.
AutoCorrect : Returns an AutoCorrect object that represents the Microsoft Excel AutoCorrect attributes. Read-only.
AutoFormatAsYouTypeReplaceHyperlinks : True (default) if Microsoft Excel automatically formats hyperlinks as you type. False if Excel does not automatically format hyperlinks as you type. Read/write Boolean.
AutomationSecurity : Returns or sets an MsoAutomationSecurity constant that represents the security mode Microsoft Excel uses when programmatically opening files. Read/write.
AutoPercentEntry : True if entries in cells formatted as percentages aren’t automatically multiplied by 100 as soon as they are entered. Read/write Boolean.
AutoRecover : Returns an AutoRecover object, which backs up all file formats on a timed interval.
Build : Returns the Microsoft Excel build number. Read-only Long.
CalculateBeforeSave : True if workbooks are calculated before they're saved to disk (if the Calculation property is set to xlManual). This property is preserved even if you change the Calculation property. Read/write Boolean.
Calculation : Returns or sets a XlCalculation value that represents the calculation mode.
CalculationInterruptKey : Sets or returns an XlCalculationInterruptKey constant that specifies the key that can interrupt Microsoft Excel when performing calculations. Read/write.
CalculationState : Returns an XlCalculationState constant that indicates the calculation state of the application, for any calculations that are being performed in Microsoft Excel. Read-only.
CalculationVersion : Returns a number whose rightmost four digits are the minor calculation engine version number, and whose other digits (on the left) are the major version of Microsoft Excel. Read-only Long.
Caller : Returns information about how Visual Basic was called (for more information, see the Remarks section).
Caption : Returns or sets a String value that represents the name that appears in the title bar of the main Microsoft Excel window.
CellDragAndDrop : True if dragging and dropping cells is enabled. Read/write Boolean.
Cells : Returns a Range object that represents all the cells on the active worksheet. If the active document isn’t a worksheet, this property fails.
Charts : Returns a Sheets collection that represents all the chart sheets in the active workbook.
ClipboardFormats : Returns the formats that are currently on the Clipboard, as an array of numeric values. To determine whether a particular format is on the Clipboard, compare each element in the array with the appropriate constant listed in the Remarks section. Read-only Variant.
Columns : Returns a Range object that represents all the columns on the active worksheet. If the active document isn't a worksheet, the Columns property fails.
COMAddIns : Returns the COMAddIns collection for Microsoft Excel, which represents the currently installed COM add-ins. Read-only.
CommandBars : Returns a CommandBars object that represents the Microsoft Excel command bars. Read-only.
CommandUnderlines : Returns or sets the state of the command underlines in Microsoft Excel for the Macintosh. Can be one of the constants of XlCommandUnderlines. Read/write Long.
ConstrainNumeric : True if handwriting recognition is limited to numbers and punctuation only. Read/write Boolean.
ControlCharacters : True if Microsoft Excel displays control characters for right-to-left languages. Read/write Boolean.
CopyObjectsWithCells : True if objects are cut, copied, extracted, and sorted with cells. Read/write Boolean.
Creator : Returns a 32-bit integer that indicates the application in which this object was created. Read-only Long.
Cursor : Returns or sets the appearance of the mouse pointer in Microsoft Excel. Read/write XlMousePointer.
CursorMovement : Returns or sets a value that indicates whether a visual cursor or a logical cursor is used. Can be one of the following constants: xlVisualCursor or xlLogicalCursor. Read/write Long.
CustomListCount : Returns the number of defined custom lists (including built-in lists). Read-only Long.
CutCopyMode : Returns or sets the status of Cut or Copy mode. Can be True, False, or an XLCutCopyMode constant, as shown in the following tables. Read/write Long.
DataEntryMode : Returns or sets Data Entry mode, as shown in the following table. When in Data Entry mode, you can enter data only in the unlocked cells in the currently selected range. Read/write Long.
DDEAppReturnCode : Returns the application-specific DDE return code that was contained in the last DDE acknowledge message received by Microsoft Excel. Read-only Long.
DecimalSeparator : Sets or returns the character used for the decimal separator as a String. Read/write.
DefaultFilePath : Returns or sets the default path that Microsoft Excel uses when it opens files. Read/write String.
DefaultSaveFormat : Returns or sets the default format for saving files. For a list of valid constants, see the FileFormat property. Read/write Long.
DefaultSheetDirection : Returns or sets the default direction in which Microsoft Excel displays new windows and worksheets. Can be one of the following constants: xlRTL (right to left) or xlLTR (left to right). Read/write Long.
DefaultWebOptions : Returns the DefaultWebOptions object that contains global application-level attributes used by Microsoft Excel whenever you save a document as a Web page or open a Web page. Read-only.
DeferAsyncQueries : Gets or sets whether asychronous queries to OLAP data sources are executed when a worksheet is calculated by VBA code. Read/write Boolean.
Dialogs : Returns a Dialogs collection that represents all built-in dialog boxes. Read-only.
DisplayAlerts : True if Microsoft Excel displays certain alerts and messages while a macro is running. Read/write Boolean.
DisplayClipboardWindow : Returns True if the Microsoft Office Clipboard can be displayed. Read/write Boolean.
DisplayCommentIndicator : Returns or sets the way cells display comments and indicators. Can be one of the XlCommentDisplayMode constants.
DisplayDocumentActionTaskPane : Set to True to display the Document Actions task pane; set to False to hide the Document Actions task pane. Read/write Boolean.
DisplayDocumentInformationPanel : Returns or sets a Boolean that represents whether the document properties panel is displayed. Read/write Boolean.
DisplayExcel4Menus : True if Microsoft Excel displays version 4.0 menu bars. Read/write Boolean.
DisplayFormulaAutoComplete : Gets or sets whether to show a list of relevant functions and defined names when building cell formulas. Read/write Boolean.
DisplayFormulaBar : True if the formula bar is displayed. Read/write Boolean.
DisplayFullScreen : True if Microsoft Excel is in full-screen mode. Read/write Boolean.
DisplayFunctionToolTips : True if function ToolTips can be displayed. Read/write Boolean.
DisplayInsertOptions : True if the Insert Options button should be displayed. Read/write Boolean.
DisplayNoteIndicator : True if cells containing notes display cell tips and contain note indicators (small dots in their upper-right corners). Read/write Boolean.
DisplayPasteOptions : True if the Paste Options button can be displayed. Read/write Boolean.
DisplayRecentFiles : True if the list of recently used files is displayed in the UI. Read/write Boolean.
DisplayScrollBars : True if scroll bars are visible for all workbooks. Read/write Boolean.
DisplayStatusBar : True if the status bar is displayed. Read/write Boolean.
EditDirectlyInCell : True if Microsoft Excel allows editing in cells. Read/write Boolean.
EnableAnimations : True if animated insertion and deletion is enabled. Read/write Boolean.
EnableAutoComplete : True if the AutoComplete feature is enabled. Read/write Boolean.
EnableCancelKey : Controls how Microsoft Excel handles CTRL+BREAK (or ESC or COMMAND+PERIOD) user interruptions to the running procedure. Read/write XlEnableCancelKey.
EnableEvents : True if events are enabled for the specified object. Read/write Boolean.
EnableLargeOperationAlert : Sets or returns a Boolean that represents whether to display an alert message when a user attempts to perform an operation that affects a larger number of cells than is specified in the Office center UI. Read/write Boolean.
EnableLivePreview : Sets or returns a Boolean that represents whether to show or hide gallery previews that appear when using galleries that support previewing. Setting this property to True shows a preview of your workbook before applying the command. Read/write Boolean.
EnableSound : True if sound is enabled for Microsoft Office. Read/write Boolean.
ErrorCheckingOptions : Returns an ErrorCheckingOptions object, which represents the error checking options for an application.
Excel4IntlMacroSheets : Returns a Sheets collection that represents all the Microsoft Excel 4.0 international macro sheets in the specified workbook. Read-only.
Excel4MacroSheets : Returns a Sheets collection that represents all the Microsoft Excel 4.0 macro sheets in the specified workbook. Read-only.
ExtendList : True if Microsoft Excel automatically extends formatting and formulas to new data that is added to a list. Read/write Boolean.
FeatureInstall : Returns or sets a value (constant) that specifies how Microsoft Excel handles calls to methods and properties that require features that aren’t yet installed. Can be one of the MsoFeatureInstall constants listed in the following table. Read/write MsoFeatureInstall.
FileConverters : Returns information about installed file converters. Returns null if there are no converters installed. Read-only Variant.
FileDialog : Returns a FileDialog object representing an instance of the file dialog.
FindFormat : Sets or returns the search criteria for the type of cell formats to find.
FixedDecimal : All data entered after this property is set to True will be formatted with the number of fixed decimal places set by the FixedDecimalPlaces property. Read/write Boolean.
FixedDecimalPlaces : Returns or sets the number of fixed decimal places used when the FixedDecimal property is set to True. Read/write Long.
FormulaBarHeight : Allows the user to specify the height of the formula bar in lines. Read/write Long.
GenerateGetPivotData : Returns True when Microsoft Excel can get PivotTable report data. Read/write Boolean.
GenerateTableRefs : The GenerateTableRefs property determines whether the traditional notation method or the new structured referencing notation method is used for referencing tables in formulas. Read/write.
Height : Returns or sets a Double value that represents tThe height, in points, of the main application window.
Hinstance : Returns the instance handle of the instance that is calling Microsoft Excel. Read-only Long.
Hwnd : Returns a Long indicating the top-level window handle of the Microsoft Excel window. Read-only.
IgnoreRemoteRequests : True if remote DDE requests are ignored. Read/write Boolean.
Interactive : True if Microsoft Excel is in interactive mode; this property is usually True. If you set the this property to False, Microsoft Excel will block all input from the keyboard and mouse (except input to dialog boxes that are displayed by your code). Read/write Boolean.
International : Returns information about the current country/region and international settings. Read-only Variant.
Iteration : True if Microsoft Excel will use iteration to resolve circular references. Read/write Boolean.
LanguageSettings : Returns the LanguageSettings object, which contains information about the language settings in Microsoft Excel. Read-only.
LargeOperationCellThousandCount : Returns or sets the maximum number of cells needed in an operation beyond which an alert is triggered. Read/write Long.
Left : Returns or sets a Double value that represents the distance, in points, from the left edge of the screen to the left edge of the main Microsoft Excel window.
LibraryPath : Returns the path to the Library folder, but without the final separator. Read-only String.
MailSession : Returns the MAPI mail session number as a hexadecimal string (if there's an active session), or returns null if there's no session. Read-only Variant.
MailSystem : Returns the mail system that's installed on the host machine. Read-only XlMailSystem.
MapPaperSize : True if documents formatted for the standard paper size of another country/region (for example, A4) are automatically adjusted so that they're printed correctly on the standard paper size (for example, Letter) of your country/region. Read/write Boolean.
MathCoprocessorAvailable : True if a math coprocessor is available. Read-only Boolean.
MaxChange : Returns or sets the maximum amount of change between each iteration as Microsoft Excel resolves circular references. Read/write Double.
MaxIterations : Returns or sets the maximum number of iterations that Microsoft Excel can use to resolve a circular reference. Read/write Long.
MeasurementUnit : Specifies the measurement unit used in the application. Read/write xlMeasurementUnit.
MouseAvailable : True if a mouse is available. Read-only Boolean.
MoveAfterReturn : True if the active cell will be moved as soon as the ENTER (RETURN) key is pressed. Read/write Boolean.
MoveAfterReturnDirection : Returns or sets the direction in which the active cell is moved when the user presses ENTER. Read/write XlDirection.
MultiThreadedCalculation : Returns a MultiThreadedCalculation object that controls the multi-threaded recalculation settings that are new in short_Excel2007. Read-only.
Name : Returns a String value that represents the name of the object.
Names : Returns a Names collection that represents all the names in the active workbook. Read-only Names object.
NetworkTemplatesPath : Returns the network path where templates are stored. If the network path doesn’t exist, this property returns an empty string. Read-only String.
NewWorkbook : Returns a NewFile object.
ODBCErrors : Returns an ODBCErrors collection that contains all the ODBC errors generated by the most recent query table or PivotTable report operation. Read-only.
ODBCTimeout : Returns or sets the ODBC query time limit, in seconds. The default value is 45 seconds. Read/write Long.
OLEDBErrors : Returns the OLEDBErrors collection, which represents the error information returned by the most recent OLE DB query. Read-only.
OnWindow : Returns or sets the name of the procedure that’s run whenever you activate a window. Read/write String.
OperatingSystem : Returns the name and version number of the current operating system — for example, "Windows (32-bit) 4.00" or "Macintosh 7.00". Read-only String.
OrganizationName : Returns the registered organization name. Read-only String.
Parent : Returns the parent object for the specified object. Read-only.
Path : Returns a String value that represents the complete path to the application, excluding the final separator and name of the application.
PathSeparator : Returns the path separator character ("\"). Read-only String.
PivotTableSelection : True if PivotTable reports use structured selection. Read/write Boolean.
PreviousSelections : Returns an array of the last four ranges or names selected. Each element in the array is a Range object. Read-only Variant.
ProductCode : Returns the globally unique identifier (GUID) for Microsoft Excel. Read-only String.
PromptForSummaryInfo : True if Microsoft Excel asks for summary information when files are first saved. Read/write Boolean.
Range : Returns a Range object that represents a cell or a range of cells.
Ready : Returns True when the Microsoft Excel application is ready; False when the Excel application is not ready. Read-only Boolean.
RecentFiles : Returns a RecentFiles collection that represents the list of recently used files.
RecordRelative : True if macros are recorded using relative references; False if recording is absolute. Read-only Boolean.
ReferenceStyle : Returns or sets how Microsoft Excel displays cell references and row and column headings in either A1 or R1C1 reference style. Read/write XlReferenceStyle.
RegisteredFunctions : Returns information about functions in either dynamic-link libraries (DLLs) or code resources that were registered with the REGISTER or REGISTER.ID macro functions. Read-only Variant.
ReplaceFormat : Sets the replacement criteria to use in replacing cell formats. The replacement criteria is then used in a subsequent call to the Replace method of the Range object.
RollZoom : True if the IntelliMouse zooms instead of scrolling. Read/write Boolean.
Rows : Returns a Range object that represents all the rows on the active worksheet. If the active document isn’t a worksheet, the Rows property fails. Read-only Range object.
RTD : Returns an RTD object.
ScreenUpdating : True if screen updating is turned on. Read/write Boolean.
Selection : Returns the selected object in the active window for an Application object.
Sheets : Returns a Sheets collection that represents all the sheets in the active workbook. Read-only Sheets object.
SheetsInNewWorkbook : Returns or sets the number of sheets that Microsoft Excel automatically inserts into new workbooks. Read/write Long.
ShowChartTipNames : True if charts show chart tip names. The default value is True. Read/write Boolean.
ShowChartTipValues : True if charts show chart tip values. The default value is True. Read/write Boolean.
ShowDevTools : Returns or sets a Boolean that represents whether the Developer tab is displayed in the Ribbon. Read/write Boolean.
ShowMenuFloaties : Returns or sets a Boolean that represents whether to display Mini toolbars when the user right-clicks in the workbook window. Read/write Boolean.
ShowSelectionFloaties : Returns or sets a Boolean that represents whether Mini toolbars displays when a user selects text. Read/write Boolean.
ShowStartupDialog : Returns True (default is False) when the New Workbook task pane appears for a Microsoft Excel application. Read/write Boolean.
ShowToolTips : True if ToolTips are turned on. Read/write Boolean.
ShowWindowsInTaskbar : True if there’s a separate Windows taskbar button for each open workbook. The default value is True. Read/write Boolean.
SmartTagRecognizers : Returns a SmartTagRecognizers collection for an application.
Speech : Returns a Speech object.
SpellingOptions : Returns a SpellingOptions object that represents the spelling options of the application.
StandardFont : Returns or sets the name of the standard font. Read/write String.
StandardFontSize : Returns or sets the standard font size, in points. Read/write Long.
StartupPath : Returns the complete path of the startup folder, excluding the final separator. Read-only String.
StatusBar : Returns or sets the text in the status bar. Read/write String.
TemplatesPath : Returns the local path where templates are stored. Read-only String.
ThisCell : Returns the cell in which the user-defined function is being called from as a Range object.
ThisWorkbook : Returns a Workbook object that represents the workbook where the current macro code is running. Read-only.
ThousandsSeparator : Sets or returns the character used for the thousands separator as a String. Read/write.
Top : Returns or sets a Double value that represents the distance, in points, from the top edge of the screen to the top edge of the main Microsoft Excel window.
TransitionMenuKey : Returns or sets the Microsoft Excel menu or help key, which is usually "/". Read/write String.
TransitionMenuKeyAction : Returns or sets the action taken when the Microsoft Excel menu key is pressed. Can be either xlExcelMenus or xlLotusHelp. Read/write Long.
TransitionNavigKeys : True if transition navigation keys are active. Read/write Boolean.
UsableHeight : Returns the maximum height of the space that a window can occupy in the application window area, in points. Read-only Double.
UsableWidth : Returns the maximum width of the space that a window can occupy in the application window area, in points. Read-only Double.
UsedObjects : Returns a UsedObjects object representing objects allocated in a workbook. Read-only
UserControl : True if the application is visible or if it was created or started by the user. False if you created or started the application programmatically by using the CreateObject or GetObject functions, and the application is hidden. Read/write Boolean.
UserLibraryPath : Returns the path to the location on the user’s computer where the COM add-ins are installed. Read-only String.
UserName : Returns or sets the name of the current user. Read/write String.
UseSystemSeparators : True (default) if the system separators of Microsoft Excel are enabled. Read/write Boolean.
Value : Returns a String value that represents the name of the application.
VBE : Returns a VBE object that represents the Visual Basic Editor. Read-only.
Version : Returns a String value that represents the Microsoft Excel version number.
Visible : Returns or sets a Boolean value that determines whether the object is visible. Read/write.
WarnOnFunctionNameConflict : The WarnOnFunctionNameConflict property, when set to True, raises an alert if a developer tries to create a new function using an existing function name. Read/write Boolean.
Watches : Returns a Watches object representing a range which is tracked when the worksheet is recalculated.
Width : Returns or sets a Double value that represents the distance, in points, from the left edge of the application window to its right edge.
Windows : Returns a Windows collection that represents all the windows in all the workbooks. Read-only Windows object.
WindowsForPens : True if the computer is running under Microsoft Windows for Pen Computing. Read-only Boolean.
WindowState : Returns or sets the state of the window. Read/write XlWindowState.
Workbooks : Returns a Workbooks collection that represents all the open workbooks. Read-only.
WorksheetFunction : Returns the WorksheetFunction object. Read-only.
Worksheets : For an Application object, returns a Sheets collection that represents all the worksheets in the active workbook. For a Workbook object, returns a Sheets collection that represents all the worksheets in the specified workbook. Read-only Sheets object.
--Chart Object----
Charts(1).SeriesCollection(1).Format.Fill.ForeColor.RGB = rgbRed
-------
Charts("Sales").Move after:=Sheets(Sheets.Count)
------
Charts(1).Activate
With ActiveChart
    .Type = xlLine
    .HasTitle = True
    .ChartTitle.Text = "January Sales"
End With
------
Worksheets(1).ChartObjects(1).Activate
ActiveChart.ChartType = xlLine
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "January Sales"
------
Charts("chart1").Activate
ActiveSheet.SeriesCollection(1).Format.Fill.ForeColor.RGB = rgbBlue
------
Charts("Chart1").SeriesCollection(1). _
    ApplyDataLabels Type:=xlDataLabelsShowLabel
--------
With Charts("Chart1").Axes(xlCategory)
    .HasTitle = True
    .AxisTitle.Text = "July Sales"
End With
--------
Charts("Chart1").Axes(xlCategory).HasMajorGridlines = False
--------
For Each a In Charts("Chart1").Axes
    a.HasMajorGridlines = False
    a.HasMinorGridlines = False
Next a
-------
With Charts("Chart1").ChartGroups(1)
    .HasUpDownBars = True
    .DownBars.Interior.ColorIndex = 3
    .UpBars.Interior.ColorIndex = 5
End With
----
With Worksheets("Sheet1").ChartObjects(1).Chart
    .HasTitle = True
    .ChartTitle.Text = "1995 Rainfall Totals by Month"
End With
--------
Worksheets("Sheet1").ChartObjects(1).Activate
ActiveChart.SeriesCollection.Add _
    source:=Worksheets("Sheet1").Range("B1:B10")
-------
Worksheets("Sheet1").ChartObjects(1).Chart.ChartArea.ClearFormats
-------
Charts("Chart1").ChartWizard _
    Gallery:=xlLine, _
    HasLegend:=True, CategoryTitle:="Year", ValueTitle:="Sales"
-------
Worksheets("Sheet1").Copy After:=Worksheets("Sheet3")
-------
[a1].Value = 25
Evaluate("A1").Value = 25
       
trigVariable = [SIN(45)]
trigVariable = Evaluate("SIN(45)")
       
Set firstCellInSheet = Workbooks("BOOK1.XLS").Sheets(4).[A1]
Set firstCellInSheet = _
    Workbooks("BOOK1.XLS").Sheets(4).Evaluate("A1")
-------
Worksheets("Sheet1").Activate
boldCell = "A1"
Application.Evaluate(boldCell).Font.Bold = True
-------
Worksheets("Sheet1").ChartObjects(1) _
.Chart. Export _
    FileName:="current_sales.gif", FilterName:="GIF"
-------
Private Sub Chart_MouseMove(ByVal Button As Long, _
        ByVal Shift As Long, ByVal X As Long, ByVal Y As Long)
    Dim IDNum As Long
    Dim a As Long
    Dim b As Long
   
    ActiveChart.GetChartElement X, Y, IDNum, a, b
    If IDNum = xlLegendEntry Then _
        MsgBox "WARNING: Move away from the legend"
End Sub
-------
Worksheets(1).ChartObjects(1).Chart _
    .Location xlLocationAsNewSheet, "Monthly Sales"
-------
Set newSheet = Worksheets.Add
i = 2
newSheet.Range("A1").Value = "Name"
newSheet.Range("B1").Value = "Link Type"
For Each obj In Worksheets("Sheet1").OLEObjects
    newSheet.Cells(i, 1).Value = obj.Name
    If obj.OLEType = xlOLELink Then
        newSheet.Cells(i, 2) = "Linked"
    Else
        newSheet.Cells(i, 2) = "Embedded"
    End If
    i = i + 1
Next
-------
Worksheets("Sheet1").Range("B1:B5").Copy
Charts("Chart1").Paste
-------
ActiveSheet.PrintOut
-------
Worksheets("Sheet1").PrintPreview
-------
ActiveChart.SaveChartTemplate _
    Filename:="Presentation Chart"
-------
Charts("Chart1").SeriesCollection(1).HasDataLabels = True
-------
ActiveChart.SetDefaultChart Name:="Monthly Sales"
-------
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
ActiveChart.SetElement (msoElementPrimaryCategoryGridLinesMinor)
ActiveChart.Walls.Select
Application.CommandBars("Clip Art").Visible = False
ActiveChart.SetElement (msoElementChartFloorShow)
-------
Charts(1).SetSourceData Source:=Sheets(1).Range("a1:a10"), _
    PlotBy:=xlColumns
-------
Set myObject = ActiveWorkbook
If myObject.Application.Value = "Microsoft Excel" Then
    MsgBox "This is an Excel Application object."
Else
    MsgBox "This is not an Excel Application object."
End If
-------
With Charts("Chart1")
    .RightAngleAxes = True
    .AutoScaling = True
End With
-------
With Charts("Chart1").ChartArea
    .Interior.ColorIndex = 3
    .Border.ColorIndex = 5
End With
-------
With Charts("Chart1")
    .HasTitle = True
    .ChartTitle.Text = "First Quarter Sales"
End With
-------
With Worksheets(1).ChartObjects(1).Chart
    If .ChartType = xlBubble Then
        .ChartGroups(1).BubbleScale = 200
    End If
End With
-------
MsgBox Worksheets(1).CodeName
-------
With Worksheets(1).ChartObjects(1).Chart
    .HasDataTable = True
    .DataTable.HasBorderOutline = True
End With
-------
Charts("Chart1").DepthPercent = 50
-------
Charts("Chart1").DisplayBlanksAs = xlNotPlotted
-------
Charts("Chart1").Elevation = 34
-------
Charts("Chart1").Floor.Interior.ColorIndex = 5
-------
Charts("Chart1").GapDepth = 200
-------
Charts("Chart1").HasAxis(xlValue, xlPrimary) = True
-------
With Worksheets(1).ChartObjects(1).Chart
    .HasDataTable = True
    With .DataTable
        .HasBorderHorizontal = False
        .HasBorderVertical = False
        .HasBorderOutline = True
    End With
End With
-------
With Charts("Chart1")
    .HasLegend = True
    .Legend.Font.ColorIndex = 5
End With
-------
Charts("Chart1").HeightPercent = 80
-------
Charts("Chart1").HasLegend = True
Charts("Chart1").Legend.Font.ColorIndex = 5
-------
Charts("Chart1").PageSetup.CenterHeader = "December Sales"
-------
Charts("Chart1").RightAngleAxes = False
Charts("Chart1").Perspective = 70
-------
Set objNewSheet = Worksheets.Add
objNewSheet.Activate
intRow = 1
For Each objPF In _
    Charts("Chart1").PivotLayout.PivotFields
    objNewSheet.Cells(intRow, 1).Value = objPF.Caption
    intRow = intRow + 1
Next objPF
-------
Charts("Chart1").PlotArea.Interior.ColorIndex = 8
-------
Worksheets(1).ChartObjects(1).Chart.PlotBy = xlColumns
-------
Charts("Chart1").PlotVisibleOnly = True
-------
Worksheets(1).ChartObjects(1).Chart.ProtectData = True
-------
Worksheets(1).ChartObjects(1).Chart.ProtectFormatting = True
-------
MsgBox ActiveSheet.ProtectionMode
-------
Worksheets(1).ChartObjects(1).Chart.ProtectSelection = True
-------
Charts("Chart1").RightAngleAxes = True
-------
Charts("Chart1").Rotation = 30
-------
Charts("Chart1").Walls.Border.ColorIndex = 3
-------
Methods
Activate : Makes the current chart the active chart.
ApplyChartTemplate : Applies a standard or custom chart type to a chart.
ApplyDataLabels : Applies data labels to all the series in a chart.
ApplyLayout : Applies the layouts shown in the ribbon.
Axes : Returns an object that represents either a single axis or a collection of the axes on the chart.
ChartGroups : Returns an object that represents either a single chart group (a ChartGroup object) or a collection of all the chart groups in the chart (a ChartGroups object). The returned collection includes every type of group.
ChartObjects : Returns an object that represents either a single embedded chart (a ChartObject object) or a collection of all the embedded charts (a ChartObjects object) on the sheet.
ChartWizard : Modifies the properties of the given chart. You can use this method to quickly format a chart without setting all the individual properties. This method is noninteractive, and it changes only the specified properties.
CheckSpelling : Checks the spelling of an object.
ClearToMatchStyle : Clears the chart elements formatting to automatic.
Copy : Copies the sheet to another location in the workbook.
CopyPicture : Copies the selected object to the Clipboard as a picture.
Delete : Deletes the object.
Evaluate : Converts a Microsoft Excel name to an object or a value.
Export : Exports the chart in a graphic format.
ExportAsFixedFormat : Exports to a file of the specified format.
GetChartElement : Returns information about the chart element at specified X and Y coordinates. This method is unusual in that you specify values for only the first two arguments. Microsoft Excel fills in the other arguments, and your code should examine those values when the method returns.
Location : Moves the chart to a new location.
Move : Moves the chart to another location in the workbook.
OLEObjects : Returns an object that represents either a single OLE object (an OLEObject ) or a collection of all OLE objects (an OLEObjects collection) on the chart or sheet. Read-only.
Paste : Pastes chart data from the Clipboard into the specified chart.
PrintOut : Prints the object.
PrintPreview : Shows a preview of the object as it would look when printed.
Protect : Protects a chart so that it cannot be modified.
Refresh : Causes the specified chart to be redrawn immediately.
SaveAs : Saves changes to the chart or worksheet in a different file.
SaveChartTemplate : Saves a custom chart template to the list of available chart templates.
Select : Selects the object.
SeriesCollection : Returns an object that represents either a single series (a Series object) or a collection of all the series (a SeriesCollection collection) in the chart or chart group.
SetBackgroundPicture : Sets the background graphic for a chart.
SetDefaultChart : Specifies the name of the chart template that Microsoft Excel uses when creating new charts.
SetElement : Sets chart elements on a chart. Read/write MsoChartElementType.
SetSourceData : Sets the source data range for the chart.
Unprotect : Removes protection from a sheet or workbook. This method has no effect if the sheet or workbook isn't protected.
Properties:
Application : When used without an object qualifier, this property returns an Application object that represents the Microsoft Excel application. When used with an object qualifier, this property returns an Application object that represents the creator of the specified object (you can use this property with an OLE Automation object to return the application of that object). Read-only.
AutoScaling : True if Microsoft Excel scales a 3-D chart so that it's closer in size to the equivalent 2-D chart. The RightAngleAxes property must be True. Read/write Boolean.
BackWall : Returns a Walls object that allows the user to individually format the back wall of a 3-D chart. Read-only.
BarShape : Returns or sets the shape used with the 3-D bar or column chart. Read/write XlBarShape.
ChartArea : Returns a ChartArea object that represents the complete chart area for the chart. Read-only.
ChartStyle : Returns or sets the chart style for the chart. Read/write Variant.
ChartTitle : Returns a ChartTitle object that represents the title of the specified chart. Read-only.
ChartType : Returns or sets the chart type. Read/write XlChartType.
CodeName : Returns the code name for the object. Read-only String.
Creator : Returns a 32-bit integer that indicates the application in which this object was created. Read-only Long.
DataTable : Returns a DataTable object that represents the chart data table. Read-only.
DepthPercent : Returns or sets the depth of a 3-D chart as a percentage of the chart width (between 20 and 2000 percent). Read/write Long.
DisplayBlanksAs : Returns or sets the way that blank cells are plotted on a chart. Can be one of the XlDisplayBlanksAs constants. Read/write Long.
Elevation : Returns or sets the elevation of the 3-D chart view, in degrees. Read/write Long.
Floor : Returns a Floor object that represents the floor of the 3-D chart. Read-only.
GapDepth : Returns or sets the distance between the data series in a 3-D chart, as a percentage of the marker width. The value of this property must be between 0 and 500. Read/write Long.
HasAxis : Returns or sets which axes exist on the chart. Read/write Variant.
HasDataTable : True if the chart has a data table. Read/write Boolean.
HasLegend : True if the chart has a legend. Read/write Boolean.
HasTitle : True if the axis or chart has a visible title. Read/write Boolean.
HeightPercent : Returns or sets the height of a 3-D chart as a percentage of the chart width (between 1 and 10000 percent). Read/write Long.
Hyperlinks : Returns a Hyperlinks collection that represents the hyperlinks for the chart.
Index : Returns a Long value that represents the index number of the object within the collection of similar objects.
Legend : Returns a Legend object that represents the legend for the chart. Read-only.
MailEnvelope : Rrepresents an e-mail header for a document.
Name : Returns or sets a String value representing the name of the object.
Next : Returns a Worksheet object that represents the next sheet.
PageSetup : Returns a PageSetup object that contains all the page setup settings for the specified object. Read-only.
Parent : Returns the parent object for the specified object. Read-only.
Perspective : Returns or sets a Long value that represents the perspective for the 3-D chart view.
PivotLayout : Returns a PivotLayout object that represents the placement of fields in a PivotTable report and the placement of axes in a PivotChart report. Read-only.
PlotArea : Returns a PlotArea object that represents the plot area of a chart. Read-only.
PlotBy : Returns or sets the way columns or rows are used as data series on the chart. Can be one of the following XlRowCol constants: xlColumns or xlRows. Read/write Long.
PlotVisibleOnly : True if only visible cells are plotted. False if both visible and hidden cells are plotted. Read/write Boolean.
Previous : Returns a Worksheet object that represents the next sheet.
ProtectContents : True if the contents of the sheet are protected. For a chart, this protects the entire chart. To turn on content protection, use the Protect method with the Contents argument set to True. Read-only Boolean.
ProtectData : True if series formulas cannot be modified by the user. Read/write Boolean.
ProtectDrawingObjects : True if shapes are protected. To turn on shape protection, use the Protect method with the DrawingObjects argument set to True. Read-only Boolean.
ProtectFormatting : True if chart formatting cannot be modified by the user. Read/write Boolean.
ProtectionMode : True if user-interface-only protection is turned on. To turn on user interface protection, use the Protect method with the UserInterfaceOnly argument set to True. Read-only Boolean.
ProtectSelection : True if chart elements cannot be selected. Read/write Boolean.
RightAngleAxes : True if the chart axes are at right angles, independent of chart rotation or elevation. Applies only to 3-D line, column, and bar charts. Read/write Boolean.
Rotation : Returns or sets the rotation of the 3-D chart view (the rotation of the plot area around the z-axis, in degrees). The value of this property must be from 0 to 360, except for 3-D bar charts, where the value must be from 0 to 44. The default value is 20. Applies only to 3-D charts. Read/write Variant.
Shapes : Returns a Shapes collection that represents all the shapes on the chart sheet. Read-only.
ShowDataLabelsOverMaximum : Returns or sets whether to show the data labels when the value is greater than the maximum value on the value axis. Read/write Boolean.
SideWall : Returns a Walls object that allows the user to individually format the side wall of a 3-D chart. Read-only.
Tab : Returns a Tab object for a chart.
Visible : Returns or sets an XlSheetVisibility value that determines whether the object is visible.
Walls : Returns a Walls object that represents the walls of the 3-D chart. Read-only.
Events:
Activate : Occurs when a workbook, worksheet, chart sheet, or embedded chart is activated.
BeforeDoubleClick : Occurs when a chart element is double-clicked, before the default double-click action.
BeforeRightClick : Occurs when a chart element is right-clicked, before the default right-click action.
Calculate : Occurs after the chart plots new or changed data, for the Chart object.
Deactivate : Occurs when the chart, worksheet, or workbook is deactivated.
MouseDown : Occurs when a mouse button is pressed while the pointer is over a chart.
MouseMove : Occurs when the position of the mouse pointer changes over a chart.
MouseUp : Occurs when a mouse button is released while the pointer is over a chart.
Resize : Occurs when the chart is resized.
Select : Occurs when a chart element is selected.
SeriesChange : Occurs when the user changes the value of a chart data point by clicking a bar in the chart and dragging the top edge up or down thus changing the value of the data point.

----BEGIN-----
ワークブック(Workbook)
ワークシート(Worksheet)
セル(Cell)
---------
Worksheets("Sheet1").Range("A1").Value = "HELLO EXCEL VBA WORLD"
-------
Range("A1"): Cell A1
Range("A1:B5"): Cells A1 through B5
Range("C5:D9,G9:H16"): A multiple-area selection
Range("A:A"): Column A
Range("1:1"): Row 1
Range("A:C"): Columns A through C
Range("1:5"): Rows 1 through 5
Range("1:1,3:3,8:8"): Rows 1, 3, and 8
Range("A:A,C:C,F:F"): Columns A, C, and F
--------
MsgBox "Entered value is " & Range("A1").Value
& operator: to concatenate (join) two strings.
--------
Worksheets("Sheet1").Range("A1").Select
Range("A1").Value = "EXCEL VBA"
Cells(1,1).Value = "EXCEL VBA"

Worksheets("Sheet2").Range("A1").Value="EXCEL VBA"
Worksheets("Sheet2").Range("A1").RowHeight = 20
Worksheets("Sheet2").Range("A1").ColumnWidth = 20
Worksheets("Sheet2").Cells(1,1).Value="EXCEL VBA"
Worksheets("Sheet2").Cells(1,1).RowHeight=20
Worksheets("Sheet2").Cells(1,1).ColumnWidth=20
-------
With Worksheets("Sheet6")
.Range("A1").Value = "EXCEL VBA"
.Range("A1").RowHeight = 20
.Range("A1").ColumnWidth = 20
End With
------------
Dim MyFile, MyPath, MyName
' Returns "WIN.INI" if it exists.
MyFile = Dir("C:\WINDOWS\WIN.INI")  

' Returns filename with specified extension. If more than one *.INI
' file exists, the first file found is returned.
MyFile = Dir("C:\WINDOWS\*.INI")

' Call Dir again without arguments to return the next *.INI file in the
' same directory.
MyFile = Dir()

' Return first *.TXT file, including files with a set hidden attribute.
MyFile = Dir("*.TXT", vbHidden)

' Display the names in C:\ that represent directories.
MyPath = "c:\"   ' Set the path.
MyName = Dir(MyPath, vbDirectory)   ' Retrieve the first entry.
Do While MyName <> ""   ' Start the loop.
      ' Use bitwise comparison to make sure MyName is a directory.
      If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then
         ' Display entry only if it's a directory.
         Debug.WriteLine(MyName)
      End If  
   MyName = Dir()   ' Get next entry.
Loop
-------
If Len(Dir("c:\Instructions.doc")) = 0 Then
   Msgbox "This file does NOT exist."
Else
   Msgbox "This file does exist."
End If
--------
Sub LoopThroughFiles()
Dim MyObj As Object, MySource As Object, file As Variant
file = Dir("c:\testfolder\")
While (file <> "")
      If InStr(file, "test") > 0 Then
         MsgBox "found " & file
         Exit Sub
      End If
     file = Dir
Wend
End Sub
--------
Sub LoopThroughFiles()
Dim StrFile As String
StrFile = Dir("c:\testfolder\*test*")
Do While Len(StrFile) > 0
        Debug.Print StrFile
        StrFile = Dir
Loop
End Sub
--------------
With Worksheets("Sheet6")
With .Range("A1")
.Value = "EXCEL VBA"
.RowHeight = 20
.ColumnWidth = 20
End With
End With
------
Range("A1","E7").Value = "Excel VBA"
Range("A1:E7").Value= "Excel VBA"
Range(Cells(1,1),Cells(7,5)).Value = "Excel VBA"
Range(Cells(2,2),Cells(5, 5)).EntireRow.Value = "Excel VBA"
Range("B2:E5").EntireRow.Value = "Excel VBA"
Worksheets("Sheet1").Range(Cells(2,2),Cells(5, 5)) _
 .EntireRow.Value="Excel VBA"
Worksheets("Sheet1").Range("B2:E5").EntireRow.Value="Excel VBA"
Range(Cells(2,2),Cells(5, 5)).EntireColumn.Value = "Excel VBA"
Range("B2:E5").EntireColumn.Value="Excel VBA"
Worksheets("Sheet1").Range(Cells(2,2),Cells(5, 5)) _
 .EntireColumn.Value="Excel VBA"
Worksheets("Sheet1").Range("B2:E5") .EntireColumn.Value="Excel VBA"
Range(Cells(1,2),Cells(3,4)).Delete
Range("B1:D4").Delete
Worksheets("Sheet1").Range(Cells(1,2),Cells(3,5)).Delete

Worksheets("Sheet1").Range("B1:D4").Delete Shift := xlShiftUp
Worksheets("Sheet1").Range(Cells(1,2),Cells(3,5)) _
.Delete Shift:=xlShiftToLeft
Range(Cells(1,2),Cells(3,4)).EntireRow.Delete
Range("B1:D5").EntireRow.Delete
Worksheets("Sheet1").Range(Cells(1,2),Cells(3,4)) _
.EntireRow.Delete
Worksheets("Sheet1").Range("B1:D3").EntireRow.Delete
Range(Cells(1,2),Cells(3,4)).EntireColumn.Delete
Range("B1:D3").EntireColumn.Delete
Worksheets("Sheet1").Range(Cells(1,2),Cells(3,4)) _
.EntireColumn.Delete
Worksheets("Sheet1").Range("B1:D3").EntireColumn.Delete
--------
Clear: 全てクリア
ClearContents: 数式、文字列をクリア
ClearFormats: 書式をクリア
ClearComments: コメント文をクリア
ClearOutline: アウトラインをクリア
------
Range(Cells(1,2),Cells(3,4)).Clear
Range("B1:D3").ClearContents
Cells.Clear
Worksheets("Sheet1").Range(Cells(1,2),Cells(3,4)).Clear
Worksheets("Sheet1").Range("B1:D3").ClearFormats
Worksheets("Sheet1").Cells.ClearComments
-------
'アクティブなシートのセルB1~D3の範囲の挿入(行数=列数なので下方向にシフト)
Range(Cells(1,2),Cells(3,4)).Insert
-------
'アクティブなシートのセルB1~D3の範囲の挿入(行数>列数なので右方向にシフト)
Range("B1:D4").Insert
----------
'Sheet1のセルB1~D3の範囲の挿入(行数<列数なので下方向にシフト)
Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range(Cells(1,2),Cells(3,5))
--------
'Sheet1のセルB1~D3の範囲の挿入(行数>列数なので右方向にシフト)
Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("B1:D4").Insert
-------
'アクティブなシートの1~3行目を挿入後、下方向にシフト
Range(Cells(1,2),Cells(3,4)).EntireRow.Insert
-------
'アクティブなシートの1~3行目を挿入後、下方向にシフト
Range("B1:D5").EntireRow.Insert
------
'Sheet1の1~3行目を挿入後、下方向にシフト
Worksheets("Sheet1").Range(Cells(1,2),Cells(3,4)).EntireRow.Insert
---------
'Sheet1の1~3行目を挿入後、下方向にシフト
Worksheets("Sheet1").Range("B1:D3").EntireRow.Insert
--------
'アクティブなシートの1~3列目を挿入後、右方向にシフト
Range(Cells(1,2),Cells(3,4)).EntireColumn.Insert
-------
'アクティブなシートの1~3列目を挿入後、右方向にシフト
Range("B1:D3").EntireColumn.Insert
--------
'アクティブなシートの1~3列目を挿入後、右方向にシフト
Worksheets("Sheet1").Range(Cells(1,2),Cells(3,4)) _
 .EntireColumn.Insert
-------
'アクティブなシートの1~3列目を挿入後、右方向にシフト
Worksheets("Sheet1").Range("B1:D3").EntireColumn.Insert
--------
'セルB1~D3の範囲をコピー貼り付け
Range(Cells(1,2),Cells(3,4)).Copy Destination:=Cells(5,6)
-------
Range("B1:D3").Copy Destination:=Range("F5")
------
Worksheets("Sheet1").Range(Cells(1,2),Cells(3,4)) _
.Copy Destination:=Worksheets("Sheet1").Cells(5,6)
------
Worksheets("Sheet1").Range("B1:D3") _
.Copy Destination:=Worksheets("Sheet1").Range("F5")
-----
'セルB1~D3の範囲を切り取り貼り付け
Range(Cells(1,2),Cells(3,4)).Cut Destination:=Cells(5,6)
------
Range("B1:D3").Cut Destination:=Range("F5")
------
Worksheets("Sheet1").Range(Cells(1,2),Cells(3,4)) _
.Cut Destination:=Worksheets("Sheet1").Cells(5,6)
------
Worksheets("Sheet1").Range("B1:D3") _
.Cut Destination:=Worksheets("Sheet1").Range("F5")
--------
For Counter = 1 To 20
   Set curCell = Worksheets("Sheet1").Cells(Counter, 3)
   If Abs(curCell.Value) < 0.01 Then curCell.Value = 0
Next Counter
--------
For Each c In Worksheets("Sheet1").Range("A1:D10").Cells
  If Abs(c.Value) < 0.01 Then c.Value = 0
Next
---------
Worksheets("Sheet1").Range("D6").Formula = "=SUM(D2:D5)"
------
Sheets("Sheet1").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "Name"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Address"
Range("A1:B1").Select
Selection.Font.Bold = True
---------
With Worksheets("Sheet1")
.Range("A1") = "Name"
.Range("B1") = "Address"
.Range("A1:B1").Font.Bold = True
End With
--------
Worksheets("Sheet1").Rows(1).Copy
Worksheets("Sheet2").Select
Worksheets("Sheet2").Rows(1).Select
Worksheets("Sheet2").Paste
-------
Worksheets("Sheet1").Activate
Range("A1:D4").Select
Range("B2").Activate
------------
Sheets(Array("Sheet2", "Sheet3", "Sheet5")).Select
Range("A1:H1").Select
Selection.Borders(xlBottom).LineStyle = xlDouble
---------
Worksheets("Sheet2").Range("A1:H1").Borders(xlBottom).LineStyle = xlDouble
Worksheets.FillAcrossSheets (Worksheets("Sheet2").Range("A1:H1"))
-----
Worksheets("Sheet1").Activate
ActiveCell.Value = 35
-------
Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("B5").Activate
ActiveCell.Font.Bold = True
--------
temp = Application.Workbooks("Book1").Worksheets("Sheet1"). Range("A1").Value
---------
You can refer to a member of the collection, for example, a single Worksheet object, in three ways.
1. Using the worksheet name.
Worksheets("Sales").Range("A1").Value = "Hello"
2. Using the index number (1 is the first worksheet starting from the left).
Worksheets(1).Range("A1").Value = "Hello"
3. Using the CodeName.
Sheet1.Range("A1").Value = "Hello"
To see the CodeName of a worksheet, open the Visual Basic Editor. In the Project Explorer, the first name is the CodeName. The second name is the worksheet name (Sales).
Note: the CodeName remains the same if you change the worksheet name or the order of your worksheets so this is the safest way to reference a worksheet. Click View, Properties Window to change the CodeName of a worksheet. There is one disadvantage, you cannot use the CodeName if you reference a worksheet in a different workbook.
--------
1. The Add method of the Workbooks collection creates a new workbook.
Workbooks.Add
Note: the Add method of the Worksheets collection creates a new worksheet.
2. The Count property of the Worksheets collection counts the number of worksheets in a workbook.
MsgBox Worksheets.Count
Note: the Count property of the Workbooks collection counts the number of active workbooks.
-------
Range("B3").Value = 2
Range("A1:A4").Value = 5
Range("A1:A2,B3:C4").Value = 10
-----
Cells(3, 2).Value = 2
Range(Cells(1, 1), Cells(4, 1)).Value = 5
--------
Dim a As Range
Set a = Range("A1:C4")
a.Value = 8
a.Select
a.Rows(3).Select
a.Columns(2).Select
MsgBox a.Count
MsgBox a.Rows.Count
---------
Range("A1:A2").Select
Selection.Copy
Range("C3").Select
ActiveSheet.Paste
------
Range("C3:C4").Value = Range("A1:A2").Value
Clear
-----
Range("A1").ClearContents
Range("A1").Value = ""
---------
Range("A3").Value = "=AVERAGE(A1:A2)"
--------
Sometimes you may find it useful to disable screen updating (to avoid flickering) while executing code. As a result, your code will run faster.
Dim i As Integer
Application.ScreenUpdating = False
For i = 1 To 10000
    Range("A1").Value = i
Next i
Application.ScreenUpdating = True
---------
To instruct Excel VBA not to display this alert while executing code, update the code as follows.
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
As a result, Excel VBA closes your Excel file, without asking you to save the changes you made. Any changes are lost.
---------
By default,  calculation is set to automatic. As a result, Excel recalculates the workbook automatically each time a value affecting a formula changes. If your workbook contains many complex formulas, you can speed up your macro by setting calculation to manual.
1. For example, place a command button on your worksheet and add the following code line:
Application.Calculation = xlCalculationManual
2. You can verify this by clicking on File, Options, Formulas.
3. Now when you change the value of cell A1, the value of cell B1 is not recalculated.
4. In most situations, you will set calculation to automatic again at the end of your code. Simply add the following code line to achieve this.
Application.Calculation = xlCalculationAutomatic
---------
Create ActiveX controls such as command buttons, text boxes, list boxes etc.
1. On the Developer tab, click Insert.
2. For example, in the ActiveX Controls group, click Command Button to insert a command button control.
3. Drag a command button on your worksheet.
4. Right click the command button (make sure Design Mode is selected).
5. Click View Code.
6. Add the code line shown below between Private Sub CommandButton1_Click() and End Sub.
7. Select the range B2:B4 and click the command button (make sure Design Mode is deselected).
-------
To add the controls to the Userform, execute the following steps.
1. Open the Visual Basic Editor. If the Project Explorer is not visible, click View, Project Explorer.
2. Click Insert, Userform. If the Toolbox does not appear automatically, click View, Toolbox.
3. Add the controls listed in the table below.
For example, create a text box control by clicking on TextBox from the Toolbox. Next, you can drag a text box on the Userform. When you arrive at the Car frame, remember to draw this frame first before you place the two option buttons in it.
4. Change the names and captions of the controls according to the table below. Names are used in the Excel VBA code. Captions are those that appear on your screen. It is good practice to change the names of controls. This will make your code easier to read. To change the names and captions of the controls, click View, Properties Window and click on each control.
Note: a combo box is a drop-down list from where a user can select an item or fill in his/her own choice. Only one of the option buttons can be selected.
To show the Userform, place a command button on your worksheet and add the following code line:
Private Sub CommandButton1_Click()
DinnerPlannerUserForm.Show
End Sub
----------
1. Open the Visual Basic Editor.
2. In the Project Explorer, right click on DinnerPlannerUserForm and then click View Code.
3. Choose Userform from the left drop-down list. Choose Initialize from the right drop-down list.
4. Add the following code lines:
Private Sub UserForm_Initialize()
'Empty NameTextBox
NameTextBox.Value = ""
'Empty PhoneTextBox
PhoneTextBox.Value = ""
'Empty CityListBox
CityListBox.Clear
'Fill CityListBox
With CityListBox
    .AddItem "San Francisco"
    .AddItem "Oakland"
    .AddItem "Richmond"
End With
'Empty DinnerComboBox
DinnerComboBox.Clear
'Fill DinnerComboBox
With DinnerComboBox
    .AddItem "Italian"
    .AddItem "Chinese"
    .AddItem "Frites and Meat"
End With
'Uncheck DataCheckBoxes
DateCheckBox1.Value = False
DateCheckBox2.Value = False
DateCheckBox3.Value = False
'Set no car as default
CarOptionButton2.Value = True
'Empty MoneyTextBox
MoneyTextBox.Value = ""
'Set Focus on NameTextBox
NameTextBox.SetFocus
End Sub
Explanation: text boxes are emptied, list boxes and combo boxes are filled, check boxes are unchecked, etc.
----------
Assign the Macros
We have now created the first part of the Userform. Although it looks neat already, nothing will happen yet when we click the command buttons on the Userform.

1. Open the Visual Basic Editor.
2. In the Project Explorer, double click on DinnerPlannerUserForm.
3. Double click on the Money spin button.
4. Add the following code line:
Private Sub MoneySpinButton_Change()
MoneyTextBox.Text = MoneySpinButton.Value
End Sub
Explanation: this code line updates the text box when you use the spin button.
5. Double click on the OK button.
6. Add the following code lines:
Private Sub OKButton_Click()
Dim emptyRow As Long
'Make Sheet1 active
Sheet1.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer information
Cells(emptyRow, 1).Value = NameTextBox.Value
Cells(emptyRow, 2).Value = PhoneTextBox.Value
Cells(emptyRow, 3).Value = CityListBox.Value
Cells(emptyRow, 4).Value = DinnerComboBox.Value
If DateCheckBox1.Value = True Then Cells(emptyRow, 5).Value = DateCheckBox1.Caption
If DateCheckBox2.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox2.Caption
If DateCheckBox3.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox3.Caption
If CarOptionButton1.Value = True Then
    Cells(emptyRow, 6).Value = "Yes"
Else
    Cells(emptyRow, 6).Value = "No"
End If
Cells(emptyRow, 7).Value = MoneyTextBox.Value
End Sub
Explanation: first, we activate Sheet1. Next, we determine emptyRow. The variable emptyRow is the first empty row and increases every time a record is added. Finally, we transfer the information from the Userform to the specific columns of emptyRow.
7. Double click on the Clear button.
8. Add the following code line:
Private Sub ClearButton_Click()
Call UserForm_Initialize
End Sub
Explanation: this code line calls the Sub UserForm_Initialize when you click on the Clear button.
9. Double click on the Cancel Button.
10. Add the following code line:
Private Sub CancelButton_Click()
Unload Me
End Sub
Explanation: this code line closes the Userform when you click on the Cancel button.
--------
Test the Userform
Exit the Visual Basic Editor, enter the labels shown below into row 1 and test the Userform.
--------
Also, as a security measure, you cannot save a macro in the default Excel file format (.xlsx); instead, you must save the macro in a file with a special extension, .xlsm.
----------
Dim a As String
a = "Ask Your Question Here, OK?"
Dim b As String
b = "My Title"
Dim c As Integer
c = MsgBox(a, vbYesNo, b)
If c = vbNo Then
  MsgBox "NO!"
Else
  MsgBox "Yes!"
End If
------------
For Each a In Worksheets
    a.Name = a.Name & "-changed"
Next

----------
Worksheet Class

The Microsoft.Office.Tools.Excel.Worksheet host item is a Microsoft.Office.Interop.Excel.Worksheet object that exposes events and acts as a container for controls.
Application: This property gets a Microsoft.Office.Interop.Excel.Application that represents the Microsoft Office Excel application.
AutoFilter: Gets a Microsoft.Office.Interop.Excel.AutoFilter if filtering is on. Gets a null reference (Nothing in Visual Basic) if filtering is off.
AutoFilterMode: Gets or sets a value that indicates whether the AutoFilter drop-down arrows are currently displayed on the sheet.
-------
Cells: Gets a Range object that represents all the cells on the worksheet (not just the cells that are currently in use).
Rows: Gets a Range object that represents all the rows on the worksheet.
Columns: Gets a Range object that represents all the columns on the worksheet.
Comments: Gets a Microsoft.Office.Interop.Excel.Comments collection that represents all the comments for the worksheet.
Names: Gets a Microsoft.Office.Interop.Excel.Names collection that represents all the worksheet-specific names (names defined with the "WorksheetName!" prefix).
Shapes: Gets a Microsoft.Office.Interop.Excel.Shapes object that represents all the shapes on the worksheet.
-------
CircularReference: Gets a Range object that represents the range containing the first circular reference on the sheet, or gets a null reference (Nothing in Visual Basic) if there is no circular reference on the sheet.
ConsolidationFunction: Gets the function code used for the current consolidation.
ConsolidationOptions: Gets a three-element Array of consolidation options. 
ConsolidationSources: Gets an Array of strings that name the source sheets and ranges for the worksheet's current consolidation.
Controls: Gets the collection of controls contained within the worksheet.
Creator: Gets a value that indicates the application in which the worksheet was created.
CustomProperties: Gets a Microsoft.Office.Interop.Excel.CustomProperties object representing the identifier information associated with a worksheet.
DisplayPageBreaks: Gets or sets a value that indicates whether page breaks (both automatic and manual) on the worksheet are displayed.
DisplayRightToLeft: Gets or sets a value that indicates whether the worksheet is displayed from right to left instead of from left to right.
EnableAutoFilter: Gets or sets a value that indicates whether AutoFilter arrows are enabled when user-interface-only protection is turned on.
EnableCalculation: Gets or set a value that indicates whether Microsoft Office Excel automatically recalculates the worksheet when necessary.
EnableOutlining: Gets or sets a value that indicates whether outlining symbols are enabled when user-interface-only protection is turned on.
EnablePivotTable: Gets or sets a value that indicates whether PivotTable controls and actions are enabled when user-interface-only protection is turned on.
EnableSelection: Gets or sets a value indicating which cells can be selected on the sheet.
FilterMode: Gets a value that indicates whether the worksheet is in filter mode.
---------
HPageBreaks: Gets an Microsoft.Office.Interop.Excel.HPageBreaks collection that represents the horizontal page breaks on the sheet.
VPageBreaks: Gets a Microsoft.Office.Interop.Excel.VPageBreaks collection that represents the vertical page breaks on the sheet.
------
Hyperlinks: Gets a Microsoft.Office.Interop.Excel.Hyperlinks collection that represents the hyperlinks for the range or worksheet.
Index: Gets the index number of the worksheet within the collection of worksheets.
InnerObject: Gets a Microsoft.Office.Interop.Excel.Worksheet that represents the underlying native object for the Worksheet.
ListObjects: Gets a collection of Microsoft.Office.Interop.Excel.ListObject objects in the worksheet.
MailEnvelope: Gets an e-mail header for the worksheet.
Name: Gets or sets the name of the worksheet.
--------
Previous: Gets a Microsoft.Office.Interop.Excel.Worksheet that represents the previous sheet.
Next: Gets a Microsoft.Office.Interop.Excel.Worksheet that represents the next sheet.
--------
Outline: Gets an Microsoft.Office.Interop.Excel.Outline that represents the outline for the worksheet.
PageSetup: Gets a Microsoft.Office.Interop.Excel.PageSetup that contains all the page setup settings for the worksheet.
Parent: Gets the parent object for the worksheet.
ProtectContents: Gets a value that indicates whether the contents of the worksheet (the individual cells) are protected.
ProtectDrawingObjects: Gets a value that indicates whether shapes are protected.
Protection: Gets a Microsoft.Office.Interop.Excel.Protection object that represents the protection options of the worksheet.
ProtectionMode: Gets a value that indicates whether user interface-only protection is turned on.
ProtectScenarios: Gets a value that indicates whether worksheet scenarios are protected.
QueryTables: Gets the Microsoft.Office.Interop.Excel.QueryTables collection that represents all the query tables on the worksheet.
Range: Gets a Microsoft.Office.Interop.Excel.Range object that represents a cell or a range of cells.
Scripts: Gets the Microsoft.Office.Core.Scripts collection, which contains Script objects representing blocks of script or code in the worksheet when it is saved as a Web page.
ScrollArea: Gets or sets the range where scrolling is allowed, as an A1-style range reference.
SmartTags: Gets a Microsoft.Office.Interop.Excel.SmartTags object representing the collection of smart tags for the worksheet.
------
StandardHeight: Gets the standard (default) height of all the rows in the worksheet, in points.
StandardWidth: Gets or sets the standard (default) width of all the columns in the worksheet.
------
Tab: Gets a Microsoft.Office.Interop.Excel.Tab for the worksheet.
Tag: Gets or sets the object that contains data about the worksheet.
TransitionExpEval: Gets or sets a value that indicates whether Microsoft Office Excel uses Lotus 1-2-3 expression evaluation rules for the worksheet.
TransitionFormEntry: Gets or sets a value that indicates whether Microsoft Office Excel uses Lotus 1-2-3 formula entry rules for the worksheet.
Type: Gets the worksheet type.
UsedRange: Gets a Microsoft.Office.Interop.Excel.Range object that represents the used range on the worksheet.
Visible: Gets or sets a Microsoft.Office.Interop.Excel.XlSheetVisibility value that determines whether the object is visible.
------
Activate: Makes the current sheet the active sheet. 
Select: Selects the worksheet. 
------
CalculateMethod: Calculates the formulas in the worksheet. 
ChartObjects: Gets an object that represents either a single embedded chart (a Microsoft.Office.Interop.Excel.ChartObject) or a collection of all the embedded charts (a Microsoft.Office.Interop.Excel.ChartObjects) on the worksheet. 
CheckSpelling: Checks the spelling in a worksheet. 
CircleInvalid: Circles invalid entries on the worksheet. 
ClearArrows: Clears the tracer arrows from the worksheet. Tracer arrows are added by using the auditing feature. 
ClearCircles: Clears circles from invalid entries on the worksheet. 
-------
Copy: Copies the worksheet to another location in the workbook.
Delete: Deletes the underlying Microsoft.Office.Interop.Excel.Worksheet object, but does not delete the host item. It is highly recommended that this method not be used.
Move: Moves the worksheet to another location in the workbook.
-------
Dispose: Overloaded. Releases the unmanaged resources used by the Worksheet and optionally releases the managed resources. 
Evaluate: Converts a Microsoft Office Excel name to an object or a value. 
OLEObjects: Gets an object that represents either a single OLE object (an Microsoft.Office.Interop.Excel.OLEObject) or a collection of all OLE objects (an Microsoft.Office.Interop.Excel.OLEObjects collection) on the worksheet. 
Paste: Pastes the contents of the Clipboard onto the worksheet. 
PasteSpecial: Pastes the contents of the Clipboard onto the worksheet, using a specified format. Use this method to paste data from other applications or to paste data in a specific format. 
PivotTables: Gets an object that represents either a single PivotTable report (a Microsoft.Office.Interop.Excel.PivotTable object) or a collection of all the PivotTable reports (a Microsoft.Office.Interop.Excel.PivotTables object) on a worksheet. 
PivotTableWizard: Creates a Microsoft.Office.Interop.Excel.PivotTable object. 
PrintOut: Prints the worksheet. 
PrintPreview: Shows a preview of the worksheet as it would look when printed. 
ResetAllPageBreaks: Resets all page breaks on the specified worksheet. 
SaveAs: Saves changes to the worksheet in a different file. 
Scenarios: Gets an object that represents either a single scenario (a Microsoft.Office.Interop.Excel.Scenario object) or a collection of scenarios (a Microsoft.Office.Interop.Excel.Scenarios object) on the worksheet. 
SetBackgroundPicture: Sets the background graphic for the worksheet. 
ShowAllData: Makes all rows of the currently filtered list visible. If AutoFilter is in use, this method changes the arrows to "All." 
ShowDataForm: Displays the data form associated with the worksheet. 
-------
Protect: Protects a worksheet so that it cannot be modified. 
Unprotect: Removes protection from the worksheet. This method has no effect if the worksheet is not protected. 
-----
XmlDataQuery: Gets a Range object that represents the cells mapped to a particular XPath. Gets a null reference (Nothing in Visual Basic) if the specified XPath has not been mapped to the worksheet, or if the mapped range is empty. 
XmlMapQuery: Gets a Range object that represents the cells mapped to a particular XPath. Gets a null reference (Nothing in Visual Basic) if the specified XPath has not been mapped to the worksheet. 
Dispose: Overloaded. Overridden. Releases the unmanaged resources used by the Worksheet and optionally releases the managed resources. 

ActivateEvent: Occurs when the worksheet is activated.
BeforeDoubleClick: Occurs when the worksheet is double-clicked, before the default double-click action.
BeforeRightClick: Occurs when the worksheet is right-clicked, before the default right-click action.
Calculate: Occurs after the worksheet is recalculated.
Change: Occurs when something changes in the Worksheet cells.
Deactivate: Occurs when the worksheet loses focus.
FollowHyperlink: Occurs when you click any hyperlink on a worksheet.
PivotTableUpdate: Occurs after a PivotTable report is updated on a worksheet.
SelectionChange: Occurs when the selection changes on a worksheet.
Shutdown: Occurs when the application domain for the assembly is about to unload.
Startup: Occurs after the worksheet is running and all the initialization code in the assembly has been run.

-----------
Workbook Members
Represents a Microsoft.Office.Interop.Excel.Workbook object that exposes events and acts as a container for components.

AcceptLabelsInFormulas: Gets or sets a value that indicates whether labels can be used in worksheet formulas.
-------
ActiveChart: Gets a Microsoft.Office.Interop.Excel.Chart object that represents the active chart (either an embedded chart or a chart sheet). An embedded chart is considered active when it is either selected or activated. When no chart is active, this property returns a null reference (Nothing in Visual Basic).
ActiveSheet: Gets the active sheet (the sheet on top).
------
Application: Gets an Microsoft.Office.Interop.Excel.Application that represents the creator of the workbook.
Author: Gets or sets the author of the workbook.
AutoUpdateFrequency: Gets or sets the number of minutes between automatic updates to the shared workbook.
AutoUpdateSaveChanges: Gets or sets a value that indicates whether current changes to the shared workbook are posted to other users whenever the workbook is automatically updated.
BuiltinDocumentProperties: Gets a Microsoft.Office.Core.DocumentProperties collection that represents all the built-in document properties for the workbook.
CalculationVersion: Gets a number that indicates the version of Excel that the workbook was last fully recalculated by. The rightmost four digits are the minor calculation engine version number, and the other digits (on the left) are the major version of Microsoft Office Excel.
ChangeHistoryDuration: Gets or sets the number of days shown in the shared workbook's change history.
---------
Windows: Gets a Microsoft.Office.Interop.Excel.Windows collection that represents all the windows in the workbook.
Sheets: Gets a Microsoft.Office.Interop.Excel.Sheets collection that represents all the sheets in the workbook.
Worksheets: Gets a Microsoft.Office.Interop.Excel.Sheets collection that represents all the worksheets in the workbook.
Charts: Gets a Microsoft.Office.Interop.Excel.Sheets collection that represents all the chart sheets in the workbook.
-------
Colors: Gets or sets colors in the palette for the workbook. 
CommandBars: Gets a Microsoft.Office.Core.CommandBars object that represents the Microsoft Office Excel command bars.
Comments: Gets or sets the comments attached to the workbook.
ConflictResolution: Gets or sets the way conflicts are to be resolved whenever a shared workbook is updated.
Container: Gets the object that represents the container application for the workbook.
CreateBackup: Gets a value that indicates whether a backup file is created when this file is saved.
Creator: Gets the application in which the workbook was created.
CustomDocumentProperties: Gets a DocumentProperties collection that represents all the custom document properties for the workbook.
CustomViews: Gets a Microsoft.Office.Interop.Excel.CustomViews collection that represents all the custom views for the workbook.
Date1904: Gets or sets a value that indicates whether the workbook uses the 1904 date system.
DisplayDrawingObjects: Gets or sets how shapes are displayed.
DisplayInkComments: Gets or sets a value that indicates whether ink comments are displayed in the workbook.
DocumentLibraryVersions: Gets a Microsoft.Office.Core.DocumentLibraryVersions collection that represents the collection of versions of a shared workbook that has versioning enabled and that is stored in a document library on a server.
EnableAutoRecover: Gets or sets a value that indicates whether Microsoft Office Excel saves changed files, of all formats, on a timed interval.
EnvelopeVisible: Gets or sets a value that indicates whether the e-mail composition header and the envelope toolbar are both visible.
Excel4IntlMacroSheets: Gets a Microsoft.Office.Interop.Excel.Sheets collection that represents all the Microsoft Office Excel 4.0 international macro sheets in the workbook.
Excel4MacroSheets: Gets a Microsoft.Office.Interop.Excel.Sheets collection that represents all the Microsoft Office Excel 4.0 macro sheets in the workbook.
FileFormat: Gets the file format and type of the workbook.
-----------
HasPassword: Gets a value that indicates whether the workbook has a protection password.
Password: Gets or sets the password that must be supplied to open the workbook.
PasswordEncryptionAlgorithm: Gets the algorithm that Microsoft Office Excel uses to encrypt passwords for the workbook.
PasswordEncryptionFileProperties: Gets a value that indicates whether Microsoft Office Excel encrypts file properties for the specified password-protected workbook.
PasswordEncryptionKeyLength: Gets the key length of the algorithm that Microsoft Office Excel uses when encrypting passwords for the workbook.
PasswordEncryptionProvider: Gets the name of the algorithm encryption provider that Microsoft Office Excel uses when encrypting passwords for the workbook.
WritePassword: Gets or sets the write password of a workbook.
------------
HasRoutingSlip: Gets or sets a value that indicates whether the workbook has a routing slip.
HighlightChangesOnScreen: Gets or sets a value that indicates whether changes to the shared workbook are highlighted on screen.
HTMLProject: Gets the Microsoft.Office.Core.HTMLProject in the workbook, which represents a top-level project branch.
InactiveListBorderVisible: Gets or sets a value that indicates whether list borders are visible when a list is not active.
InnerObject: Gets a Microsoft.Office.Interop.Excel.Workbook that represents the underlying native object for the Workbook.
IsAddin: Gets or sets a value that indicates whether the workbook is running as an add-in.
IsInplace: Gets a value that indicates whether the specified workbook is being edited in place.
KeepChangeHistory: Gets or sets a value that indicates whether change tracking is enabled for the shared workbook.
Keywords: Gets or sets the keywords attached to the workbook.
ListChangesOnNewSheet: Gets or sets a value that indicates whether changes to the shared workbook are shown on a separate worksheet.
MultiUserEditing: Gets a value that indicates whether the workbook is open as a shared list.
--------
Name: Gets the name of the workbook.
FullName: Gets the name of the object, including its path on disk.
FullNameURLEncoded: Gets the name of the object, including its path on disk.
----------
Names: Gets a Microsoft.Office.Interop.Excel.Names collection that represents all the names in the workbook (including all worksheet-specific names).
Parent: Gets the parent object for the workbook.
Path: Gets the complete path to the application, excluding the final separator and name of the application.
Permission: Gets a Microsoft.Office.Core.Permission object that represents the permission settings in the workbook.
PersonalViewListSettings: Gets or sets a value that indicates whether filter and sort settings for lists are included in the user's personal view of the shared workbook.
PersonalViewPrintSettings: Gets or sets a value that indicates whether print settings are included in the user's personal view of the shared workbook.
PrecisionAsDisplayed: Gets or sets a value that indicates whether calculations in this workbook will be done using only the precision of the numbers as they are displayed.
ProtectStructure: Gets a value that indicates whether the order of the sheets in the workbook is protected.
ProtectWindows: Gets a value that indicates whether the windows of the workbook are protected.
PublishObjects: Gets the Microsoft.Office.Interop.Excel.PublishObjects collection.
ReadOnly: Gets a value that indicates whether the workbook has been opened as read-only.
ReadOnlyRecommended: Gets or sets a value that indicates whether the workbook was saved as read-only recommended.
RemovePersonalInformation: Gets or sets a value that indicates whether personal information can be removed from the workbook.
RevisionNumber: Gets the number of times the workbook has been saved while open as a shared list. If the workbook is open in exclusive mode, this property returns 0 (zero).
Routed: Gets a value that indicates whether the workbook has been routed to the next recipient.
RoutingSlip: Gets a Microsoft.Office.Interop.Excel.RoutingSlip object that represents the routing slip for the workbook.
Saved: Gets or sets a value that indicates whether no changes have been made to the workbook since it was last saved.
SaveLinkValues: Gets or sets a value that indicates whether Microsoft Office Excel saves external link values with the workbook.
SharedWorkspace: Gets a SharedWorkspace that represents the Document Workspace in which the workbook is located.
ShowConflictHistory: Gets or sets a value that indicates whether the Conflict History worksheet is visible in the workbook that is open as a shared list.
ShowPivotTableFieldList: Gets or sets a value that indicates whether the PivotTable field list can be shown.
SmartDocument: Gets a Microsoft.Office.Core.SmartDocument that represents the settings for a smart document solution.
SmartTagOptions: Gets a Microsoft.Office.Interop.Excel.SmartTagOptions object representing the options that can be performed with a smart tag.
Styles: Gets a Microsoft.Office.Interop.Excel.Styles collection that represents all the styles in the workbook.
Subject: Gets or sets the subject for the mailer or routing slip.
Sync: Gets a Microsoft.Office.Core.Sync object that provides access to the methods and properties of documents that are part of a Document Workspace.
Tag: Gets or sets the object that contains data about the workbook.
TemplateRemoveExtData: Gets or sets a value that indicates whether external data references are removed when the workbook is saved as a template.
Title: Gets or sets the title of the Web page when the workbook is saved as a Web page.
UpdateLinks: Gets or sets a value that indicates a workbook's setting for updating embedded OLE links.
UpdateRemoteReferences: Gets or sets a value that indicates whether Microsoft Office Excel updates remote references in the workbook.
UserStatus: Gets a 1-based, two-dimensional array that provides information about each user who has the workbook open as a shared list.
VBASigned: Gets a value that indicates whether the Visual Basic for Applications project for the workbook has been digitally signed.
VBProject: Gets the virtual Microsoft.Vbe.Interop.VBProject project that is in the workbook.
VstoSmartTags: Gets the Microsoft.Office.Tools.SmartTagCollection associated with the workbook.
WebOptions: Gets the Microsoft.Office.Interop.Excel.WebOptions collection, which contains workbook-level attributes used by Microsoft Office Excel when you save a document as a Web page or open a Web page.
WriteReserved: Gets a value that indicates whether the workbook is write-reserved.
WriteReservedBy: Gets the name of the user who currently has write permission for the workbook.
XmlMaps: Gets an Microsoft.Office.Interop.Excel.XmlMaps collection that represents the schema maps that have been added to the workbook.
XmlNamespaces: Gets an Microsoft.Office.Interop.Excel.XmlNamespaces collection that represents the XML namespaces contained in the workbook.

-----------
Activate: Activates the first window associated with the workbook. 
NewWindow: Creates a new window. 
Close: Closes the workbook. 

AcceptAllChanges: Accepts all changes in the shared workbook. 
RejectAllChanges: Rejects all changes in the shared workbook. 

AddToFavorites: Adds a shortcut to the workbook to the Favorites folder. 
BreakLink: Converts formulas linked to other Microsoft Office Excel sources or OLE sources to values. 
CanCheckIn: Gets a value that indicates whether Microsoft Office Excel can check in the workbook to a server. 
ChangeFileAccess: Changes the access permissions for the workbook. This might require an updated version to be loaded from the disk. 
ChangeLink: Changes a link from one document to another. 
CheckIn: Gets a workbook from a local computer to a server, and sets the local workbook to read-only so that it cannot be edited locally. Calling this method closes the workbook. 
DeleteNumberFormat: Deletes a custom number format from the workbook. 
Dispose: Overloaded. Releases the unmanaged resources used by the Workbook and optionally releases the managed resources. 
EndReview: Terminates a review of a file that has been sent for review using the SendForReview method. 
ExclusiveAccess: Assigns the current user exclusive access to the workbook that is open as a shared list. 
FollowHyperlink: Displays a cached document, if it has already been downloaded. Otherwise, this method resolves the hyperlink, downloads the target document, and displays the document in the appropriate application. 
HighlightChangesOptions: Controls how changes are shown in a shared workbook. 
LinkInfo: Gets the link date and update status. 
LinkSources: Returns an Array of links in the workbook. 
MergeWorkbook: Merges changes from one workbook into an open shared workbook. 
OpenLinks: Opens the supporting documents for a link or links. 
PivotCaches: Gets a Microsoft.Office.Interop.Excel.PivotCaches collection that represents all the PivotTable caches in the specified workbook. 
PivotTableWizard: Creates a Microsoft.Office.Interop.Excel.PivotTable object. 
Post: Posts the workbook to a public folder. This method works only with a Microsoft Exchange client connected to a Microsoft Exchange server. 
PrintOut: Prints the workbook. 
PrintPreview: Shows a preview of the object as it would look when printed. 

Protect: Protects a workbook so that it cannot be modified. 
Unprotect: Removes protection from the workbook. This method has no effect if the workbook is not protected. 
ProtectSharing: Saves the workbook and protects it for sharing. 
UnprotectSharing: Turns off protection for sharing and saves the workbook. 

PurgeChangeHistoryNow: Removes entries from the change log for the workbook. 
RecheckSmartTags: Causes a foreground smart tag check to occur automatically annotating data that was not annotated before. 
ReferenceEquals : (Inherited from Object.)
RefreshAll: Refreshes all external data ranges and PivotTable reports in the workbook. 
ReloadAs: Reloads a workbook based on an HTML document, using the specified document encoding. 
RemoveCustomization: Removes the Visual Studio Tools for Office customization from the workbook. 
RemoveUser: Disconnects the specified user from the shared workbook. 

ReplyWithChanges: Sends an e-mail message to the author of a workbook that has been sent out for review, notifying him or her that a reviewer has completed review of the workbook.
SendForReview: Sends a workbook in an e-mail message for review to the specified recipients. 
SendMail: Sends the workbook by using the installed mail system.

ResetColors: Resets the color palette to the default colors. 
Route: Routes the workbook, using the workbook's current routing slip. 
RunAutoMacros: Runs the Auto_Open, Auto_Close, Auto_Activate, or Auto_Deactivate macro attached to the workbook. This method is included for backward compatibility. You should use the Open, Close, Activate and Deactivate events instead of these macros. 
Save: Saves changes to the workbook. 
SaveAs: Saves changes to the workbook in a different file. 
SaveAsXMLData: Exports the data that has been mapped to the specified XML schema map to an XML data file. 
SaveCopyAs: Saves a copy of the workbook to a file but does not modify the open workbook in memory. 
SendFaxOverInternet: Sends the active sheet in the workbook as a fax to the specified recipients. 
SetLinkOnData: Sets the name of a procedure that runs whenever a DDE link is updated. 
SetPasswordEncryptionOptions: Sets the options for encrypting the workbook using a password. 
ToggleFormsDesign: Toggles Microsoft Office Excel into and out of design mode. 
UpdateFromFile: Updates a read-only workbook from the saved disk version of the workbook if the disk version is more recent than the copy of the workbook that is loaded in memory. If the disk copy has not changed since the workbook was loaded, the in-memory copy of the workbook is not reloaded. 
UpdateLink: Updates a Microsoft Office Excel, DDE, or OLE link (or links). 
WebPagePreview: Displays a preview of the workbook as it would look if saved as a Web page. 
XmlImport: Imports an XML data file into the current workbook. 
XmlImportXml: Imports an XML data stream that has been previously loaded into memory. 
Dispose: Overloaded. Overridden. Releases the unmanaged resources used by the Workbook and optionally releases the managed resources. 
-------
ActivateEvent: Occurs when the workbook is activated.
AddinInstall: Occurs when the workbook is installed as an add-in.
AddinUninstall: Occurs when the workbook is uninstalled as an add-in.
AfterXmlExport: Occurs after Microsoft Office Excel saves or exports data from the workbook to an XML data file.
AfterXmlImport: Occurs after an existing XML data connection is refreshed or after new XML data is imported into the workbook.
BeforeClose: Occurs before the workbook closes. If the workbook has been changed, this event occurs before the user is asked to save changes.
BeforePrint: Occurs before the workbook (or anything in it) is printed.
BeforeSave: Occurs before the workbook is saved.
BeforeXmlExport: Occurs before Microsoft Office Excel saves or exports data from the workbook to an XML data file.
BeforeXmlImport: Occurs before an existing XML data connection is refreshed or before new XML data is imported into the workbook.
Deactivate: Occurs when the workbook is deactivated.
New: Occurs when a new workbook is created.
NewSheet: Occurs when a new sheet is created in the workbook.
Open: Occurs when the workbook is opened.
PivotTableCloseConnection: Occurs after a PivotTable report closes the connection to its data source.
PivotTableOpenConnection: Occurs after a PivotTable report opens the connection to its data source.
SheetActivate: Occurs when any sheet is activated.
SheetBeforeDoubleClick: Occurs when any worksheet is double-clicked, before the default double-click action.
SheetBeforeRightClick: Occurs when any worksheet is right-clicked, before the default right-click action.
SheetCalculate: Occurs after any worksheet is recalculated or after any changed data is plotted on a chart.
SheetChange: Occurs when cells in any worksheet are changed by the user or by an external link.
SheetDeactivate: Occurs when any sheet is deactivated.
SheetFollowHyperlink: Occurs when you click any hyperlink in a workbook.
SheetPivotTableUpdate: Occurs after the sheet of a PivotTable report has been updated.
SheetSelectionChange: Occurs when the selection changes on any worksheet. Does not occur if the selection is on a chart sheet.
Shutdown: Occurs when the application domain for the assembly is about to unload.
Startup: Occurs after the workbook is running and all the initialization code in the assembly has been run.
SyncEvent: Occurs when the local copy of a worksheet that is part of a Document Workspace is synchronized with the copy on the server.
WindowActivate: Occurs when any workbook window is activated.
WindowDeactivate: Occurs when any workbook window is deactivated.
WindowResize: Occurs when any workbook window is resized.

----------
Dim instance As Range
'Represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range.

AddIndent: Determines if text is automatically indented when the text alignment in a cell is set to equal distribution either horizontally or vertically.
Address: Returns the range reference.
AddressLocal: Returns the range reference for the specified range in the language of the user.

AllowEdit: Determines if the range can be edited on a protected worksheet.

Application: Returns an Application object that represents the Microsoft Excel application.
Worksheet: Returns a Worksheet object that represents the worksheet containing the specified range.

Areas: Returns an Areas collection that represents all the ranges in a multiple-area selection.
Borders: Returns a Borders collection that represents the borders of a style or a range of cells (including a range defined as part of a conditional format).
Cells: Returns a Range object that represents the cells in the specified range.
Characters: Returns a Characters object that represents a range of characters within the object text.

Row: Returns the number of the first row of the first area in the range.
Column: Returns the number of the first column in the first area in the specified range.

Rows: Returns a Range object that represents the rows in the specified range.
Columns: Returns a Range object that represents the columns in the specified range.

RowHeight: Returns the height of all the rows in the range specified, measured in points.
ColumnWidth: Returns or sets the width of all columns in the specified range.

EntireColumn: Returns a Range object that represents the entire column (or columns) that contains the specified range.
EntireRow: Returns a Range object that represents the entire row (or rows) that contains the specified range.

Height: The height of the range.
Width: The width, in points, of the range.

Comment: Returns a Comment object that represents the comment associated with the cell in the upper-left corner of the range.
Count: Returns the number of objects in the collection.
CountLarge: Counts the largest value in a given Range of values. Read-only.
Creator: Returns a 32-bit integer that indicates the application in which this object was created.
CurrentArray: If the specified cell is part of an array, returns a Range object that represents the entire array.
CurrentRegion: Returns a Range object that represents the current region.
Dependents: Returns a Range object that represents the range containing all the dependents of a cell.
DirectDependents: Returns a Range object that represents the range containing all the direct dependents of a cell.
DirectPrecedents: Returns a Range object that represents the range containing all the direct precedents of a cell.
DisplayFormat: Gets a DisplayFormat object that represents the display settings for the specified range.
End: Returns a Range object that represents the cell at the end of the region that contains the source range.

Errors: Allows the user to access error checking options.
Font: Returns a Font object that represents the font of the specified object.
FormatConditions: Returns a FormatConditions collection that represents all the conditional formats for the specified range.
Formula: Returns or sets the object's formula in A1-style notation and in the language of the macro.
FormulaArray: Returns or sets the array formula of a range.
FormulaHidden: Determines if the formula will be hidden when the worksheet is protected.
FormulaLabel: Returns or sets the formula label type for the specified range.
FormulaLocal: Returns or sets the formula for the object, using A1-style references in the language of the user.
FormulaR1C1: Returns or sets the formula for the object, using R1C1-style notation in the language of the macro.
FormulaR1C1Local: Returns or sets the formula for the object, using R1C1-style notation in the language of the user.
HasArray: Determines if the specified cell is part of an array formula.
HasFormula: Determines if all cells in the range contain formulas.
Hidden: Determines if the rows or columns are hidden.
--------
HorizontalAlignment: Returns or sets the horizontal alignment for the specified object.
VerticalAlignment: Returns or sets the vertical alignment of the specified object.
-------
Hyperlinks: Returns a Hyperlinks collection that represents the hyperlinks for the range.
ID: Returns or sets the identifying label for the specified cell when the page is saved as a Web page.
IndentLevel: Returns or sets the indent level for the cell or range.
Interior: Returns an Interior object that represents the interior of the specified object.
Item: Returns a Range object that represents a range at an offset to the specified range.
-------
Top: Returns the distance, in points, from the top edge of row 1 to the top edge of the range.
Left: The distance from the left edge of column A to the left edge of the range.
---------
ListHeaderRows: Returns the number of header rows for the specified range.
ListObject: Returns a ListObject object for the Range object or QueryTable object.
LocationInTable: Returns a constant that describes the part of the PivotTable report that contains the upper-left corner of the specified range.
Locked: Determines if the object is locked.
MDX: Returns the MDX name for the specified Range object. Read-only.
MergeArea: Returns a Range object that represents the merged range containing the specified cell.
MergeCells: Determines if the range or style contains merged cells.
Name: Returns or sets the name of the object.
----------
Previous: Returns a Range object that represents the previous cell.
Next: Returns a Range object that represents the next cell.
--------
NumberFormat: Returns or sets the format code for the object.
NumberFormatLocal: Returns or sets the format code for the object as a string in the language of the user.
Offset: Returns a Range object that represents a range that’s offset from the specified range.
Orientation: Returns the text orientation.
OutlineLevel: Returns or sets the current outline level of the specified row or column.
PageBreak: Returns or sets the location of a page break.
Parent: Returns the parent object for the specified object.
Phonetic: Returns the Phonetic object which contains information about a specific phonetic text string in a cell.
Phonetics: Returns the Phonetics collection of the range.
PivotCell: Returns a PivotCell object that represents a cell in a PivotTable report.
PivotField: Returns a PivotField object that represents the PivotTable field containing the upper-left corner of the specified range.
PivotItem: Returns a PivotItem object that represents the PivotTable item containing the upper-left corner of the specified range.
PivotTable: Returns a PivotTable object that represents the PivotTable report containing the upper-left corner of the specified range, or the PivotTable report associated with the PivotChart report.
Precedents: Returns a Range object that represents all the precedents of a cell.
PrefixCharacter: Returns the prefix character for the cell.
QueryTable: Returns a QueryTable object that represents the query table that intersects the specified Range object.
Range: Returns a Range object that represents a cell or a range of cells.
ReadingOrder: Returns or sets the reading order for the specified object.
Resize: Resizes the specified range.
ServerActions: Specifies the actions that can be performed on the SharePoint server for a Range object.
ShowDetail: Returns True if the outline is expanded for the specified range (so that the detail of the column or row is visible).
ShrinkToFit: Determines if text automatically shrinks to fit in the available column width.
SmartTags: Returns a SmartTags object representing the identifier for the specified cell.
SoundNote: This property should not be used. Sound notes have been removed from Microsoft Excel.
SparklineGroups: Gets a SparklineGroups object that represents an existing group of sparklines from the specified range.
Style: Returns a Style object that represents the style of the specified range.
Summary: Determines if the range is an outlining summary row or column.
Text: Returns or sets the text for the specified object.
UseStandardHeight: Determines if the row height of the Range object equals the standard height of the sheet.
UseStandardWidth: Determines if the column width of the Range object equals the standard width of the sheet.
Validation: Returns the Validation object that represents data validation for the specified range.
Value: Returns or sets the value of the specified range.
Value2: Returns or sets the cell value.

WrapText: Determines if Microsoft Excel wraps the text in the object.
XPath: Returns an XPath object that represents the Xpath of the element mapped to the specified Range object.

---------
Activate: Activates a single cell, which must be inside the current selection.
Select: Selects the object.

AddComment: Adds a comment to the range.
AdvancedFilter: Filters or copies data from a list based on a criteria range.
AllocateChanges: Performs a writeback operation for all edited cells in a range based on an OLAP data source.
ApplyNames: Applies names to the cells in the specified range.
ApplyOutlineStyles: Applies outlining styles to the specified range.
AutoComplete: Returns an AutoComplete match from the list.
AutoFill: Performs an autofill on the cells in the specified range.
AutoFilter: Filters a list using the AutoFilter.
AutoFit: Changes the width of the columns in the range or the height of the rows in the range to achieve the best fit.
AutoFormat: Automatically formats the specified range, using a predefined format.
AutoOutline: Automatically creates an outline for the specified range.
BorderAround: Adds a border to a range and sets the Color, LineStyle, and Weight properties for the new border.
BorderAround2: Reserved for internal use.
Calculate: Calculates a specified range of cells on a worksheet.
CalculateRowMajorOrder: Calculates a specfied range of cells.
CheckSpelling: Checks the spelling of an object.
--------
Clear: Clears the entire object.
ClearComments: Clears all cell comments from the specified range.
ClearContents: Clears the formulas from the range.
ClearFormats: Clears the formatting of the object.
ClearHyperlinks: Removes all hyperlinks from the specified range.
ClearNotes: Clears notes and sound notes from all the cells in the specified range.
ClearOutline: Clears the outline for the specified range.
-------
ColumnDifferences: Returns a Range object that represents all the cells whose contents are different from the comparison cell in each column.
Consolidate: Consolidates data from multiple ranges on multiple worksheets into a single range on a single worksheet.
Copy: Copies the range to the specified range or to the Clipboard.
CopyFromRecordset: Copies the contents of an ADO or DAO Recordset object onto a worksheet, beginning at the upper-left corner of the specified range.
CopyPicture: Copies the selected object to the Clipboard as a picture.
CreateNames: Creates names in the specified range, based on text labels in the sheet.
CreatePublisher: You have requested Help for a keyword used only on the Macintosh. For information about this keyword, consult the language reference Help included with Microsoft Office Macintosh Edition.
Cut: Cuts the object to the Clipboard or pastes it into a specified destination.
DataSeries: Creates a data series in the specified range.
Delete: Deletes the object.
DialogBox: Displays a dialog box defined by a dialog box definition table on a Microsoft Excel 4.0 macro sheet.
Dirty: Designates a range to be recalculated when the next recalculation occurs.
DiscardChanges: Discards all changes in the edited cells of the range.
EditionOptions: You have requested Help for a keyword used only on the Macintosh. For information about this keyword, consult the language reference Help included with Microsoft Office Macintosh Edition.
ExportAsFixedFormat: Exports to a file of the specified format.
------
FillDown: Fills down from the top cell or cells in the specified range to the bottom of the range.
FillLeft: Fills left from the rightmost cell or cells in the specified range.
FillRight: Fills right from the leftmost cell or cells in the specified range.
FillUp: Fills up from the bottom cell or cells in the specified range to the top of the range.
--------
Find: Finds specific information in a range and returns a Range object that represents the first cell where that information is found.
FindNext: Continues a search that was begun with the Find method.
FindPrevious: Continues a search that was begun with the Find method.
--------
FlashFill: TRUE indicates that the Excel Flash Fill feature has been enabled and active.
FunctionWizard: Starts the Function Wizard for the upper-left cell of the range.
GetEnumerator():
GoalSeek: Calculates the values necessary to achieve a specific goal.
--------
Group: When the Range object represents a single cell in a PivotTable field’s data range, the Group method performs numeric or date-based grouping in that field.
Ungroup: Promotes a range in an outline (that is, decreases its outline level).
-------
Insert: Inserts a cell or a range of cells into the worksheet or macro sheet and shifts other cells away to make space.
InsertIndent: Adds an indent to the specified range.
Justify: Rearranges the text in a range so that it fills the range evenly.
ListNames: Pastes a list of all displayed names onto the worksheet, beginning with the first cell in the range.

Merge: Creates a merged cell from the specified Range object.
UnMerge: Separates a merged area into individual cells.

NavigateArrow: Navigates a tracer arrow for the specified range to the precedent, dependent, or error-causing cell or cells.
NoteText: Returns or sets the cell note associated with the cell in the upper-left corner of the range.
Parse: Parses a range of data and breaks it into multiple cells.
PasteSpecial: Pastes a Range from the Clipboard into the specified range.
PrintOut: Prints the object.
PrintOutEx: Reserved for internal use.
PrintPreview: Shows a preview of the object as it would look when printed.
RemoveDuplicates: Removes duplicate values from a range of values.

Replace: Returns a Boolean indicating characters in cells within the specified range.
RowDifferences: Returns a Range object that represents all the cells whose contents are different from those of the comparison cell in each row.
Run: Runs the Microsoft Excel macro at this location.
SetPhonetic: Creates Phonetic objects for all the cells in the specified range.
Show: Scrolls through the contents of the active window to move the range into view.
ShowDependents: Draws tracer arrows to the direct dependents of the range.
ShowErrors: Draws tracer arrows through the precedents tree to the cell that’s the source of the error and returns the range that contains that cell.
ShowPrecedents: Draws tracer arrows to the direct precedents of the range.
Sort: Sorts a PivotTable report, a range, or the active region if the specified range contains only one cell.
SortSpecial: Uses East Asian sorting methods to sort the range or a PivotTable report, or uses the method for the active region if the range contains only one cell. For example, Japanese sorts in the order of the Kana syllabary.
Speak: Causes the cells of the range to be spoken in row order or column order.
SpecialCells: Returns a Range object that represents all the cells that match the specified type and value.
---------
Subtotal: Creates subtotals for the range (or the current region, if the range is a single cell).
RemoveSubtotal: Removes subtotals from a list.
---------
Table: Creates a data table based on input values and formulas that you define on a worksheet.
TextToColumns: Parses a column of cells that contain text into several columns.

--------
Dim a As Range
Dim b As Hyperlinks
b = a.Hyperlinks

Hyperlinks members
Application: Returns an Application object that represents the Microsoft Excel application.
Count: Returns the number of objects in the collection.
Creator: Returns a 32-bit integer that indicates the application in which this object was created.
Item: Returns a single object from a collection.
Parent: Returns the parent object for the specified object.
Add: Adds a hyperlink to the specified range or shape. Returns a Hyperlink object.
Delete: Deletes the object.
GetEnumerator()
--------
Function HyperLinkText(pRange As Range) As String
Dim ST1 As String
Dim ST2 As String
If pRange.Hyperlinks.Count = 0 Then
Exit Function
End If
ST1 = pRange.Hyperlinks(1).Address
ST2 = pRange.Hyperlinks(1).SubAddress
If ST2 <> "" Then
ST1 = "[" & ST1 & "]" & ST2
End If
HyperLinkText = ST1
End Function
-------
WorksheetFunction

Set myRange = Worksheets("Sheet1").Range("A1:C10")
answer = Application.WorksheetFunction.Min(myRange)
MsgBox answer

Sub StartSpelling()
Dim iRow As Integer
On Error GoTo ERRORHANDLER
'Go through all the cells in column A, and perform a spellcheck on the value.
'If the value is spelled incorrectly, write "Wrong" in column B, otherwise write "OK".
For iRow = 1 To WorksheetFunction.CountA(Columns(1))
If Application.CheckSpelling(Cells(iRow, 1).Value, , True) = False Then
         Cells(iRow, 2).Value = "Wrong"
Else
         Cells(iRow, 2).Value = "OK"
End If
Next iRow
Exit Sub
ERRORHANDLER:
    MsgBox "The spell check feature is not installed!"
End Sub

It calculates the average of the values in cell A1 and cell A2 and places the result into cell A3.
Range("A3").Value = Application.WorksheetFunction.Average(Range("A1:A2"))
-------
Range.Formula
Returns or sets a Variant value that represents the object's formula in A1-style notation and in the macro language.

Worksheets("Sheet1").Range("A1").Formula = "=$A$4+$A$10"

Sub InsertTodaysDate()
Sheets("Sheet1").Select
Range("A1").Select
Selection.Formula = "=text(now(),""mmm dd yyyy"")"
Selection.Columns.AutoFit
End Sub
-------
Range.Offset(RowOffset, ColumnOffset)
returns a range that is offset from the specified range.

Worksheets("Sheet1").Activate
Range("A1:D10").Select
ActiveCell.Value = "Monthly Totals"
ActiveCell.Offset(0, 1).Activate

activates the cell three columns to the right of and three rows down from the active cell.
ActiveCell.Offset(rowOffset:=3, columnOffset:=3).Activate
-------
Range.Resize(RowSize, ColumnSize): Returns a Range object that represents the resized range.

Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
 tbl.Columns.Count).Select

Worksheets("Sheet1").Activate
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 1, numColumns + 1).Select
-------
Range.CurrentRegion: a range bounded by any combination of blank rows and blank columns.
For Each c In ActiveCell.CurrentRegion.Cells
  If Abs(c.Value) < 0.01 Then c.Value = 0
Next

Worksheets("Sheet1").Activate
ActiveCell.CurrentRegion.Select
Selection.Style = "Currency"
-------
Worksheets("Sheet1").Range("A1:D4").Copy _
destination:=Worksheets("Sheet2").Range("E5")
-------
Public Sub CopyRows()

Sheets("Sheet1").Select
' Find the last row of data
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

' Loop through each row
For x = 2 To FinalRow
' Decide if to copy based on column D
ThisValue = Cells(x, 4).Value
If ThisValue = "A" Then
            Cells(x, 1).Resize(1, 33).Copy
            Sheets("SheetA").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Sheet1").Select
ElseIf ThisValue = "B" Then
            Cells(x, 1).Resize(1, 33).Copy
            Sheets("SheetB").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Sheet1").Select
End If
Next x
End Sub
-------
Dim NewRange As Range
Set NewRange = Range("A2:A6")

Dim HighestScore As Long
Dim LowestScore As Long
Dim TopScorer As Range
Dim BottomScorer As Range

LowestScore = WorksheetFunction.Min(Range("B1:B5"))
HighestScore = WorksheetFunction.Max(Range("B1:B5"))

Set BottomScorer = Range("B1:B5").Find(What:=LowestScore)
Set TopScorer = Range("B1:B5").Find(What:=HighestScore)

Range("B7").Value = BottomScorer.Offset(, -1).Value
Range("B8").Value = TopScorer.Offset(, -1).Value