Learning How To Program With Visual Basic
Some Background of Visual Basic
Visual Basic with Integrated Development Environment (IDE) has been the most popular programming environment for more than a decade. It means you can design your application's interface, write the code, test the code, compile the code, and create an installation package all from within Visual Basic's IDE. You don't have to write code to design your applications interface. Instead you use your mouse pointer to drag objects such as buttons, text boxes, and scrollbars from the toolbox to your form, and then use your mouse to place and size the objects.
Visual Basic 6 and earlier version is an event-based programming language (determined by user actions such as mouse clicks, key presses, or messages from other programs) and associated Integrated Development Environment or IDE (consists of a source code editor, a compiler, interpreter, build automation tools, and debugger) from Microsoft for its COM programming model. Component Object Model (COM) encompasses the OLE, OLE Automation, ActiveX, COM+ and DCOM technologies. Visual Basic was derived from BASIC and enables the Rapid Application Development (RAD) of graphical user interface (GUI) applications, access to databases using DAO, RDO, or ADO, and creation of ActiveX Controls and Objects.
Offices today have choices to migrate from Visual Basic 6 application to Visual Basic .NET. or keep the VB6 code, and interoperate with VB.NET to easily add any functionality from the .NET Framework. Visual Basic 6 applications are essentially COM applications, whereas Visual Basic .NET applications are not COM-based. The two are built on incompatible technologies. However, many organizations today still have a significant Microsoft Visual Basic 6 code base and continue using VB6 application. This website will only focus on VB6 and earlier. As of November 2007, there are notably six versions of Visual Basic .NET Frameworks.
- Visual Basic .NET (VB 7) - had many disadvantages, including a runtime that was ten times as large to package as the VB6 runtime and an increased memory
- Visual Basic .NET 2003 (VB 7.1) - improvements were made to the performance and reliability of the .NET IDE and runtime
- Visual Basic 2005 (VB 8.0) - Microsoft decided to drop the .NET portion of the title
- Visual Basic 2005 Express - as part of the Visual Studio product range, Microsoft has created it for hobbyists and novices, and it is available for free
- Visual Basic 2008 (VB 9.0) - it was released together with the Microsoft .NET Framework 3.5 on November 19, 2007
- Visual Basic VBx (VB 10.0) - in the development stage, will offer support for the Dynamic Language Runtime
The following example
demonstrates a difference between VB6 and VB.NET. Both examples unload
the active window.
' Classic VB Example
Private Sub cmdClose_Click()
Unload Me
End Sub
' a VB.NET
example. Note the 'cmd' prefix being replaced with the 'btn' prefix
Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles btnClose.Click
Me.Close()
End Sub
It is important
to know some significant differences between VB6,VB Express and VB.NET.
An integer inVB 6 will occupy 16 bits of memory (equivalent to 2 bytes)
and can only store numbers in the range from -32,768 through 32,767. In
all VB.NET languages, an integer will occupy 32 bits of memory or 4
bytes, and can store numbers from -2,147,483,648 through 2,147,483,647.
In addition, there are now at least three kinds of integers in .NET
(short, integer, and long) and better still, Byte data type can technically also store
an integer.
VB Express gives you a broad range of data types that you can use in
your program to store not only numbers, but characters as Text, dates,
true or false as Boolean or floating point numbers. There is also an
Object data type that you can store anything in and a User-Defined data
type.
VB.NET does not support Variant data type that is popular in VB 6 and
earlier versions, which made it easy to just start using a variable
without having to declare what data type for the variables. It turns out
that this carefree way of declaring variables as Variant data type can
result in unintentionally storing the wrong data type in the variables
and causing serious bugs. Moreover it also make the code run slower.
In VB.NET, you can use the Object data type in a similar way but it's
strongly discouraged as performance can be even worse
(Object is provided for an entirely different reason).
Get started with VBA Programming for Excel
Visual Basic Editor (VBE) is the tool used to create, modify,
and maintain VBA procedures and modules in Excel application. VBA gives you the ability to
modify workbooks and worksheets, allows you to modify VB components and code modules. This
page applies only to Excel97 and above. It does not apply to Excel95 or previous versions.
Before using these procedures, you'll need to set a reference in VBA to the VBA Extensibility
library. In the VBA editor, go to the Tools menu, choose the References item, and put a check
next to "Microsoft Visual Basic For Applications Extensibility" library.
[see sample].
This enables VBA to find the definitions of these objects. If you are using Excel97, this
library will appear in the References list without a version number: "Microsoft Visual Basic
For Applications Extensibility". If you are using Excel 2000 or later, it will appear with a
version number "Microsoft Visual Basic For Applications Extensibility 5.3".
It is very important that you reference the proper library. If
you reference the wrong library, you will receive"Type Mismatch" errors. If you don't
reference the extensibility library at all, you will receive "User Defined Type Not Defined
Error" messages.
In Microsoft Office XP, when reference features of the Microsoft
Visual Basic for Applications Extensibility 5.3 object model, you may receive an error message that
programmatic access to the Visual Basic project is not trusted. To prevent this message from appearing,
select from Excel menu Macro/Tools/Security and on the Trusted Sources tab, select the
Trust access to Visual Basic Project box. By enabling this, macros in any worksheets can access
the core Microsoft Visual Basic objects, methods, and properties, which represents a possible security
hazard. The recommendation to select the "Trust access to Visual Basic Project" box only
for the duration of a macro that accesses the Visual Basic object model. The "Trust access to
Visual Basic Project" box should be cleared after the macro has finished running.
Right-click on Excel Sheet1, select View Code or simply press
Alt-F11
to open the VBE. The screen in the top-left corner is the
Project Explorer.
In it you can see the currently loaded workbook (Book1) and the three worksheets. As you delete and add
workbooks or worksheets, the tree-like structure of the workbook and worksheet objects will reflect exactly
the changes that you have made. You can also add other objects such as UserForms, Modules and Class Modules.
Double-click ThisWorkBook opens the Code Window for your
currently active Workbook object. Click on the drop-down list (General) in the top-left corner of the code
window and select another choice (Workbook). Now you should see the Workbook_Open event in the
code window, Private Sub Workbook_Open() and End Sub.
This simply means you can program an event to happen as it is at the point when you choose File>Open
and load it in. Click on the drop-down list (Open) in the top-right corner, you notice that there
are other Workbook events you can choose from to write code.[see sample].
The Edit toolbar has three commands that offer pop-up help to VBA novices in writing code - Quick Info, List
Properties/Methods and List Constants. You use Quick Info command to get a
pop-up reminder of the syntax by right-click the term and select Quick Info from the shortcut menu. Alternatively,
position your cursor on the term, choose Edit-->Quick Info.
List
Properties/Methods command will automatically pop up a list box containing methods and properties when you
type a period "." or "," so that you
can easily complete the expression. Such automatic display of a pop-up list at current insertion point as you are
typing, is possible only if you had enabled the "Auto
List Members" in the VBE Options menu. Alternatively, you can display it through the Edit toolbar.
Remember the "Auto Syntax Check" in the VBE Options menu
should always be checked as
the checker will automatically examine the lines for any
irregular syntax and try to fix it, automatically remove unnecessary spaces, changing
cases and even add missing closing quotation marks and parentheses. The "Auto
Quick Info" feature should also always be marked checked so that you
would be able to see a pop-up reminder of the proper syntax for a
function. The
List Constants command
displays a pop-up list box containing constants for a property you have just typed. This feature saves you time
especially when you have to remember complex constant names.
Below the Project Explorer is the Properties Window
where you can use to view and modify the properties of an object such as a project, module, class module, user form,
or a control (eg. a button or check box in a dialog box). The drop-down list allows you to pick the object to view
or modify. The Alphabetic page on the left displays properties for ThisWorkBook and the Categorized page on the right
displays a list of properties for a user
form.
VBE has other windows which it doesn't display by default - the Object Browser and the Immediate Window.
The Object Browser
provides information about all built-in objects and custom objects that you created - Properties, Methods, Events,
Constants, Classes. Use the drop-down list to choose the object libraries you want to view or alternatively, you could
stay with default <All Libraries>.
Properties : attributes of objects
Methods : actions you perform on objects
Events
: examples such as opening or closing of a workbook
Constants : named items that remain a constant value while a procedure is
running
Classes : formal definition of objects
We use the small Immediate
Window as a virtual test pad to enter lines of code we want to test without entering them in the module macro itself.
After you have typed your lines of code in the
Immediate Window,
press the Enter key, VBE will execute that code. To display it, press Ctrl-G or just access through the Edit toolbar.
Some Common Questions From VBA Novices:
What is a Function: Excel has built-in functions like IF, AND, OR, COUNT, SUM which you use
in your worksheet. Functions can invoke other functions to return a value.
What is a User Defined Function (UDF): You can make up your own functions or use functions
made up by someone else, either or both would be UDF, because they are not Excel standard built-in functions. Macros begin
with Sub, and User Defined Functions begin with Function. Another distinction is that you will not find
UDF in the macro list (Alt+F8),
but you will find them in the Function Wizard list from the address bar button fx,
or from the menu Insert --> Function... [see
sample]
What is Personal.xls - it is the file where you store your own macros and User Defined Functions, that you want
to be available from any workbook [see
sample]. For macros you only want available in a single workbook you would install macros in that workbook.
Personal.xls is stored in your XLSTART directory, so you won't want to have other workbooks in that directory.
What is a Macro:
A stored series of commands and functions that can be run to perform a specific task or tasks. In Excel a macro could
be a complete program. Macros can be run from a toolbar button, or menu, from a shape in the workbook, or from another
macro. If you want to program something, or issue a series of commands, you can create a macro. Recording a macro in Excel
creates a series of actions in VBA, to duplicate what you enter but it does not record individual keystrokes. Macros are
sometimes made into Add-Ins [see
Record and Run Macro]
What is a Module: In a VBA project, you can think of a
Module as a specialized sheet that serves as container for Visual Basic code. Your VB codes
will reside in one or more modules. Let's address how you insert a module. In VBE, choose Macro from the
Tools
menu, and the select
Visual Basic Editor. Easier yet, right-click on any worksheet tab, select View Code [see
sample].
Alternatively, use the shortcut key combination of Alt+F11 to invoke VBE [see
sample
].
From the VBE, choose Insert, then Module from the menu. In the upper-left corner of the
VBE is a the Project Window, also called the Project Explorer. It features a tree-like
hierarchical list of the items that make up your project. You will see a new folder under your current project that now has
the module you have inserted. Alternatively, you can right-click on your current project in the Project Window and choose
Insert, then Module from the shortcut menu [see
sample].
Learning All The Basics In The Module
Modules are VBE window code sheets. They are not fired off directly by events such as worksheet being added or workbook being closed. They are a means of creating procedures, rather than running in an object. There are four basic things you will enter into a module:
- declarations
- subroutines
- functions
-
remarks
* Note: There are also variations on types of modules -- those being standard modules and class modules. For the sake of simplicity, we're addressing standard modules unless otherwise specified. Class modules have some similar characteristics, but the details about class modules' special purpose warrant separate treatment.
A procedure is a named unit of code that contains a sequence of statements to be executed as a group. There are two types of procedures: subroutine and function. You can call a code procedure in many ways:
-
Using a custom menu command [see sample] or a custom toolbar command [see sample]
-
Insert a VBA control such as command button or combo box and assign the procedure to it. [see sample]
-
In Excel menu, choose Tools/Macro/Macros, select the macro name from the list and click Run. [see sample]
-
Run the code from a UserForm [see sample]
-
Call your code from another macro [see sample]
-
Write your code as a function and call it directly by inserting the function into cell [see sample or download]
-
Click directly on your code and press F5
A subroutine (also called sub-procedure) is a self-contained unit of code that performs a set of actions or calculations or a combination of the two. It can be called by different routines anywhere within the program as many times as needed. It doesn’t return a value. The macro you record using the Macro Recorder is an example. You call a subroutine by inserting a Call instruction into the code. Note that functions don’t appear in Macros dialog box.
A function is like a subroutine except that it returns a value. Functions start with Function (instead of Sub) and end with End Function (instead of End Sub). Generally, functions should be called by using a variable to accept the return value [see sample1, sample2]. VBAcomes with many built-in functions but you can also create your user-defined functions (UDF).
A statement is a unit of code that describes an action, defines an action, defines an item, or gives the value of a variable. You can put more than one statement on a line by separating them with colons. You can also beak a line of code onto a second line or a subsequent line by using an underscore (_). VBA reads all the lines as a single line of code. If you need to break a line that involves a long string in quotes, break the string into shorter strings and concatenate them using & operator. [see sample]
A constant is in effect, a named variable that keeps a constant value when a program is executing. However, you can not assign a new value to it from within your code as you can with a variable. The constant’s value doesn’t change at different times of program execution. The example below sets up a constant called My_Path and it always has the value C:\Data\. There are also predefined constants which you can view using the Object Browser.
Const My_Path = “C:\Data\”
A variable is a location in memory set aside for storing a piece of information that can be changed while a procedure is running. It may just be a single number or a piece of text that the program needs to hold and refer to while it is running. A variable has a name (the word that you use to refer to the value that the variable contains). A variable also has a data type (which determines the kind of data that the variable can store). A variable can represent an array if it has to store an indexed set of closely related data items. VBA uses several types of variables as indicated below. Try this [example] variable in the Immediate window and press Enter.
-
String (stores text characters like "William")
-
Integer (stores whole numbers in the range of -32,768 to 32,767)
-
Long (stores larger integers than Integer range, -2,147,438,648 to 2,147,438,647)
-
Single (stores numbers with decimals)
-
Double (stores numbers with decimals where you want more accuracy than with Single)
-
Boolean (variable can be True or False)
-
Object stores object
-
Variant (stores any type of data and you let VBA decides how to deal with the variable)
Below table summarizes the data types, amount of memory space each one uses, and their conversion functions.
Function Name Description
Example
Equivalent To |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Assigning
Values to variable
You use assignment statements to perform calculations and assign the
result to a variable, as the following example shows.
v = 5
v = v + 1
Note that the equal sign (=) is an assignment operator, not an
equality operator. Another way of saying is that the value is being
assigned to the variable. The first statement assigns the value 5 to
the variable v. The second statement increments the variable by 1,
and the variable v now holds the value of 6.
You can declare variables either explicitly or implicitly. An implicit declaration occurs when you tell VBA to store data in a variable that you have not explicitly declared. VBA then stores the data in a Variant variable. For example you create this statement tempVal = 2 and a variable will automatically be created for tempVal as a variant (default type) and it will have the value of 2.
An explicit declaration sets the variable’s name, its type, before you use it. To avoid the problem of misnaming variables, you can stipulate that VBA always generates an error message whenever it encounters a variable not declared. Set the VBE up to require variables to be declared explicitly, so that you must declare each variable formally before you use it. Choose Tool/Options/ and select Require Variable Declaration check box on the Editor page [see sample]. This makes VBE automatically enter the Option Explicit statement for all modules and user forms you create from now on [see sample].
Coding is much faster using implicit because you do not have to
initially define your variables before you use them. You simply make
variables statements and VBA will take care of the rest. However,
implicit declarations can make it difficult for someone else to
understand your code. Option Explicit is usually the preferred
practice and helps prevent runtime errors. Option Explicit must be
placed in the declarations section of every module you want it to apply
unless you define the variable as a global variable.
Variables: Local-level, Module-level and Global-level
If you define a variable within a procedure, only code within that procedure can access that variable. You call it local-level variable. However, you will often need variables that can be used by several procedures, from other modules or the whole application. For these reasons, you can declare a variable at local, module or global level.
A local variable (sometimes called procedure-level) uses Dim, Static, or ReDim (arrays only) to declare the variable. Local variable declared with the Dim statement (short for Dimension) remains in existence only as long as the procedure is executing. Dim statement creates a space in memory for a variable. When it stops running, the values are not preserved and the memory is released. Local variable declared with the Static statement remains in existence for the lifetime of the application. Dim A(5) creates an array with 6 elements (0-5), so there are altogether six A variables. Dim weeklyInput declares a 2-dimensional array (4 rows indexed 1 to 4, by 5 columns indexed 1 to 5). Dim weeklyOutput declares a 3-dimensional array (the first dimension has 4 elements indexed 0 to 3, within that, the second dimension has 12 elements indexed 1 to 12, and within that, the third dimension has 5 elements indexed 2 to 6) ReDim A(10) makes it an 11-element array but loses all the data in it. ReDim Preserve A(15) makes a 16-element array but keeps all existing data. In array, all subscripts start at 0 by default. See statement examples below.
Dim
myVarl
Static myVar1
Dim A(5)
Dim weeklyInput(1 To 4, 1 To 5)
As Single
Dim weeklyOutput(3,
1 To 12, 2 To 6) As Single
ReDim A(10)
ReDim Preserve A(15)
A module-level is declared for a particular module. It is available to all procedures within that module but not to the rest of the application. An alternative to the keyword Dim is the keywaord Private. See statement examples below.
Dim tempVal
Private tempVal
A global-level variable (sometimes called public variable) is declared with the Global statement, and it can be accessed by any code within the application. See example below. See example below. Module and global variables hold their values while the application is running until the user closes the application.
Global tempVal
You should only use local variables to hold values that are being used during that procedure. If you expect to access them from other modules, they need to be global variables. However, you can use Static statement (see below) to preserve a local variable.
Static Sub Static_Statement_Demo()Here is another example that demonstrates differences between a local variable, module-level and global variable.
Scope of Subroutines
The default is all subs have Public scope unless otherwise specified. This means that one sub can call another sub inside a program. It is not necessary to precede the sub with keyword Public. If you want a particular sub to be callable only by subs within its module, you must precede Sub with the keyword Private as in below example. Then any sub within the same module can call Test_1, but subs outside of its module have no access to sub Test_1.
Private Sub Test_1()
Passing Arguments
In a simple example below, suppose a “Main” sub calls “DisplayName” sub
to display customers’ names in message box. How do we get the customers’
names from the Main sub to the DisplayName sub? There are two ways of
doing this – by using module-level variables and by passing arguments.
Module-level variables
method
Suppose you define a range called 'PersonNames' that contains the last
names and the first names of 7 persons. You want to display each
person’s full name in a message box. First, you declare module-level
variables LastName and FirstName in the first line. Then the Main sub
loops through the rows of the named range, stores the last and first
name of the persons in the LastName and FirstName variables, and call
the DisplayName sub to display the persons' full names. (You can also
download
the worksheet).
Option Explicit
Dim LastName As String, FirstName As String
Sub Main()
Dim i As Integer
For i = 1 To 7
LastName = Range("PersonNames").Cells(i, 1)
FirstName = Range("PersonNames").Cells(i, 2)
Call DisplayName
Next
End Sub
Sub DisplayName()
Dim Person As String
Person = FirstName & " " & LastName
MsgBox "The person's name is " & Person
End Sub
Passing by Arguments
method
I refer the Main sub as the calling sub and the DisplayName sub
as called sub. Note that the variables LastName and FirstName are
no longer declared as module-level variable before the sub. They are now
declared as local-level variables in the Main sub, and are passed to
DisplayName sub as arguments.
In programming, passing by arguments (or called parameters) means
sending
information back to those DisplayName sub variables. To pass argument, type (Call), then name
of the called sub (DisplayName), follow by name of the variables being
passed, separated by commas and within parenthesis as in Call
DisplayName(LastName, FirstName).
You probably notice that the arguments in the called sub are LName and FName, and are not the same as the name of the variables passed to it as in LastName and FirstName. This is technically legal. The variables being passed from the calling sub and the arguments in the called sub do need to have the same names, but they must only match in the number, type and order. (You can also download the example worksheet here).
Option Explicit
Sub Main()
Dim i As Integer
Dim LastName As String, FirstName As String
For i = 1 To 7
LastName = Range("PlannerNames").Cells(i, 1)
FirstName = Range("PlannerNames").Cells(i, 2)
Call DisplayName(LastName, FirstName)
Next
End Sub
Sub DisplayName(LName As String, FName As String)
Dim Planner As String
Planner = FName & " " & LName
MsgBox "The planner's full name is " & Planner
End Sub
Passing by reference (ByRef)
and by value (ByVal)
When you pass a variable such as FirstName from one sub to another, the
default method is by Reference. This means that the variables in the
calling sub and called sub shares the same memory location, so that any
changes made to the FName in the called sub will be reflected in the
calling sub. In another word, the value of variable FirstName in the
calling sub will reflect the same value as FName in the called sub
DisplayName when control passes back to the calling sub.
Sub DisplayName(ByRef LastName As String, ByRef FirstName As String)
To
pass the variable by Value, we mean pass a copy of FirstName to the
called sub, so that any changes made to the FName in the called sub will
not be reflected in the calling sub.
Sub DisplayName(ByVal FirstName as String, ByVal FirstName as String)
The two examples below shall give you a better understanding of passing
variables by reference and by value. The showMyName procedure
receives a reference to the variables LastName and FirstName
from the calling sub 'calling_test1'. After this procedure executes, the
variable
LastName
changes its name to
'poh beng' from 'pb' because the 1st argument in the called sub was
passed by Reference. Because the 2nd argument
FirstName
in the showMyName procedure was passed by Value, the modified
string 'william' in the variable FirstName will not be reflected
in the calling sub after control is passed back to it. In stead of
displaying 'poh beng william', the second message box will display 'poh
beng bill'
Private Sub showMyName(ByRef LastName As
String, ByVal FirstName As String)
LastName = "poh beng"
FirstName = "william"
End Sub
Sub calling_test1()
Dim LastName As String, FirstName As String
LastName = "pb"
FirstName = "bill"
MsgBox LastName & " " & FirstName
' message reads as 'pb bill'
Call showMyName(LastName, FirstName)
MsgBox LastName & " " & FirstName
'
message reads as 'poh beng bill'
End Sub
This following second example shows passing the variables by Reference.
The calling sub creates two variables - loginName and empID
- that stores the String and Variant data. The first message box
displays the original string text as were created in the calling sub.
Then the calling sub procedure calls the DisplayMyName procedure
and pass to it the string data stored in the variables loginName
and empID. The DisplayMyName procedure stores the content
of the loginName and empID variables in LastName
and FirstName variables, runs the Replace method to replace the
string 'pb' with 'poh beng' and the string '9112 ?' with '91128'. Then
it returns the replaced strings back to the calling sub, which then run
the second message box to read the altered strings in the loginName
and empID variables, joined with an & operator to read as 'poh
beng william, ID 91128'
Private Static Sub DisplayMyName(loginName As String, empID
As Variant)
loginName = Replace(loginName, "pb", "poh beng")
empID = Replace(empID, " ?", "8")
End Sub
Sub calling_test2()
Dim loginName As String
Dim empID As Variant
loginName = "pb william"
empID = "9112 ?"
MsgBox loginName & ", ID " & empID
' message reads as
'pb william, ID 9112 ?'
Call DisplayMyName(loginName,
empID)
MsgBox (loginName) & ", ID " &
empID
'message
reads as 'poh beng william, ID 91128'
End Sub
Note
that array arguments and user-defined type arguments cannot be passed
ByVal. Also, using ByVal or ByRef doesn't have any effect when the
argument is a literal constant, but only when it's a variable. Most of
our procedures will use the default ByRef argument passing. You can use
ByVal when you want to ensure that code in the procedure cannot change
the variable that was passed as an argument.
Passing Arrays
Consider this simple example, the calling sub stores 7 names in an array
size of 7 (assume this array is populated with names in your worksheet
range). It passes the Names array to the UpperBoundNames as in line 8.
Note that there are no parenthesis to Names array in line 8 – this is
because in line 4 it has already been declared as an array. On the other
hand, line 1 of the UpperBoundNames has parenthesis next to the Names
argument, indicating that it is expecting an array. (You can also
download
the worksheet).
Option Explicit
Sub Calling()
Dim i As Integer
Dim Names(7) As String
For i = 1 To 7
Names(i) = Range("PersonNames").Cells(i, 1)
Next
Call UpperBoundNames (Names)
' line 8
End Sub
Sub UpperBoundNames(Names() As String)
Dim NNames As Integer
NNames = UBound(Names)
MsgBox "position of UBound(Names) is : " & NNames
End Sub
Writing your
first subroutine Double-click
ThisWorkBook in the Project Explorer. Click (General) from the drop-down
list in the top-left corner of the window and select Workbook [see
sample]. It will show the code
for the
Workbook
Open event,
Private Sub Workbook_Open() and End Sub. This occurs at
the point when you choose File/Open, select the file and load it. If you
click the drop-down list, you notice there are other
Workbook events
with predefined codes available. Try out this simple
example,
save your workbook, then reopen it. You will see your first subroutine
done, with a display message showing what you wanted to see.
Now a second example, from ThisWorkBook, select
NewSheet
event (instead of Open event). It will show Private Sub
Workbook_NewSheet(ByVal Sh As Object) and End Sub. Insert
Module 1 and add in the following code. This subroutine has an argument
parameter for a variable called VAR. You are going to call the
subroutine (display_it) from another procedure (NewSheet event) and pass
a variable (VAR) across.
Sub display_it(VAR)
Next, on Workbook_NewSheet event, add the following code. In Excel
worksheet menu, choose Insert/Worksheet, you will see a message box
showing the workbook full path address, cell A1 is populated with the
full path address and a message box showing as what you had just typed
“My second piece of Subroutine !” The Call command call your subroutine
and pass the required parameters to it (here it is the string called “My
second piece of Subroutine" that is passed into the variable called VAR)
Option Explicit
Download
this second example here
MsgBox VAR
End Sub
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim x As String
x = ActiveWorkbook.FullName
MsgBox x
Cells(1, 1) = x
‘ position of row 1 and column 1
Call display_it("My second piece of Subroutine")
End Sub