Intro Excel VBA For Traders

All,

Welcome to the Intro Excel VBA For Traders thread. This thread is aimed at:

1. Traders who have little programming experience.
2. Traders who would like to learn to develop Excel VBA trading tools.
3. Traders who would like (down the track) to learn a 'lower' level language (eg MQL).

This thread is:

1. A place to learn the basics of programming using Excel VBA.
2. A place to ask questions about the principles outlined here.
3. An organic learning resource.

This thread is not:

1. The place to post specific individual project problem questions - please use platform tech threads mfor that.
2. Going to make you an expert developer in a month.

Thread organisation. It is a good idea to have an overall idea of the approach of the thread. This will be in the following format:

Tutorial Topic Post(s) followed by
Q&A's and Comments (relevant to the topic at hand)

Why ?

Because I enjoy being able to create whatever I want (trading and otherwise) in Excel VBA and enjoy teaching. That plus the fact that I am an active trader means that FF is an ideal place for such a tutorial. Additionally I am a commercial member and offer Excel VBA courses and tools to individuals and companies through my site:

Excel VBA Courses, Tools and Tutorials

Below are the main sections of what we will be covering in the tutorial:

1. Introduction
2. Let's Get Our Feet Wet
3. What's the VBE When It's At Home ?
4. A Variable By Any Other Name
5. Procedures Are More Than Just Plastic Surgery
6. Clench Your Cheeks - Control The Flow
7. Shake-It-All-About - The In and Out of Input and Output
8. They Were Here When We Moved In - Builtin Functions
9. The Scary O Word - Where's Gail ?
10. Pass The Hammer - Time To Build Something

<Placeholder>

In this first article we will discuss the basics of Excel VBA.
VBA is short for Visual Basics for Applications. This is the standard Macro / development language used in most Microsoft Office products. The ‘Application’ can be any one of the Office products pretty much, however, in the main-stay, it is used within Excel, Access and Word (in descending order of usage / popularity if you like).


I’m sure we have all seen the results of a recorded macro in, for example, Excel. This is ‘Excel VBA’. I use the inverted commas as understanding the true meaning of the term VBA can take a little more than just understanding what each of the three words means. A more technically correct definition of what Excel VBA is would be:


A core executable instruction set enriched by the object model of the host application.


The Core Instruction Set = VB
The Host Application = A


You see, VB (in the classic sense of the term - i.e. VB6) and VBA share exactly the same core run-time library. As such, the core language is virtually identical. It should be noted that looking up VB6 solutions is suitable in a lot of cases for the solving of core language problems in VBA.


Let's take a quick look at some code to see the similarities as it is important (we won't worry about what it does as yet - we can get to that later if it's unclear):

Private Const OutlookApp As String = "Outlook.Application"

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Private Function GetOutlookRef(ByRef OutObj As Object) As OutlookRefResult

On Error Resume Next

'Attempt to get a reference to an existing instance of Outlook
Set OutObj = GetObject(, OutlookApp)

On Error GoTo ErrorHandler

If OutObj Is Nothing Then 'No current instance of Outlook

'Open an instance of Outlook
Set OutObj = CreateObject(OutlookApp)
GetOutlookRef = OutlookRefResultCREATEDNEW
Exit Function

Else 'We have got a reference to current Outlook instance

GetOutlookRef = OutlookRefResultREFEXISTING
Exit Function

End If

ErrorHandler:

'Outlook is either not installed on this PC or there was a problem starting an instance of it
GetOutlookRef = OutlookRefResultNOOUTLOOK

End Function
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

The above code is part of an Excel AddIn used by a firm to email team members with an Excel report but this code (exactly the same) could be used in VB6 to the same end.

What happened to this thread. This seems to be inactive. Are there any samples of codes for VBA Traders?