To start off with what is VBA? 

VBA is the (free) scripting language for Excel and other Microsoft Office applications.  Entire corporate reporting systems are written in VBA that combine data from various systems, be they relational databases, olap databases, or even spreadsheets.

The easiest way to understand VBA is that it is the Excel Macro language.  Macros are typically recorded key strokes that can save time.  You record the macro once, then you can run the macro and watch it perform all the steps you recorded.  (this is the equivalent of the old lotus /rnc days).

While this is one use, VBA is really much more powerful.  VBA is the programming language for the entire Microsoft Office Suite.  The code you learn by learning Excel VBA can be directly transferred into programming other applications like Word, Access, PowerPoint, and even Outlook.  

Business Analyst:  Why do I care about VBA?

VBA can do many things to make your job easier.  When you combine VBA with Essbase, suddenly you have a very powerful and flexible reporting system. 

Examples where VBA helps out:

-creation of a reporting system that cranks out hundreds of pages of reports and even emails them, at the touch of a button.

-creation of controlled templates to send out to the field for data entry.  Users can either email the workbooks back to corporate then you perform a sannity check and perform an automated lock and send to the essbase server

-the creation of a template system where the field actually inputs data into an excel workbook that then sends the numbers to Essbase and initiates a calc script resulting in consolidated financials. 

-the creation of automated emails from Excel is just 4 commands away.

Information system developer:  Why do I care about VBA?

-by basing your development environment on Excel, you development task can be much faster.  Much of the functionality desired by end users is already built into Excel. You have a head start of designing an information system through the use of Excel.

Tradeoffs:

no compiling of code.  VBA is an interpretive language. 

All code is contained in the .xls, the Excel workbook

Users must have the office application to run the VBA.  Since we're talking mostly about Excel, users must have excel to run the VBA.  This is good and bad.  You can be sure that when you send a .xls to a user, as long as they have the same version (most things are cross compatible, within a version or 2 is usually ok), you can be sure the code will run.  Contrast this with VB, where the code itself is relatively tiny, but the distribution can be quite large.  You also have to worry about versions of dll's and the like.

CIO: Why do I care about VBA?

Through implementation of VBA you can lower your total cost of ownership.

VBA developers should be cheaper.  You can virtually train them your self, just start out with a VB developer.  VBA is a skill that has been taught in schools for the past 10 years.  It is a subset of Visual Basic.  This means that your VB developers would need only a week or so to become proficient in the develoment and maintenance of a VBA reporting system.  The only development environment you need for VBA is a desktop with the appropriate version of the Office application on it.

While VBA itself is not web centric, some of this web stuff is becoming overrated.  I have worked on a handful of large projects where it was thought users were going to enter their budgets entirely in a web interface.  When the usability tests were performed, if given a choice between a spreadsheet based template system and a web form where everything has to be retyped, 100% of the users kick and scream that they will not use the web forms.

Now there are some tools out there that replicate the spreadsheet experience in a browser.  These make it a little less painful for the users to enter their budgets.

CEO: Why do I care about VBA?

If you have a project

If some of your

I have been building information reporting / budgeting systems using VBA, Excel, and Essbase for the past 10 years.

Some pluses:

VBA skillset is not that pricey.  Most every graduate

The beauty is that it is easy to learn, but it can get as complex as you want it to.

The reason VBA is discussed here is that Hyperion has provided a very powerful sprea

  If you have a set of repetitive keystrokes you enter often, you can record the key strokes then replay them by assigning a hot key or running the macro.  From this simplistic example to full blown programming that interfaces with databases and the internet, VBA has many uses.

If you've ever recorded a macro in Excel then you have seen and used VBA.  Another great thing about VBA (for Excel) is that it is stored in the workbook itself which makes distributing your code very easy.

How does VBA help with Essbase?

In Excel to retrieve data from Essbase you perform the Essbase Retrieve operation.  Let's say you have made an Excel workbook that has 25 sheets in it.  Each month you would like to update the entire workbook.  So the manual steps would be to open the workbook, then perform an Essbase Retrieve on each sheet. This would be a little tedious on a monthly or even weekly basis.

Lets look at the code to automate this.

We start out with the command that performs the Essbase Retrieve operation.

x = EssVMenuRetrieve

When you run this code, it is exactly the same as selecting Essbase Retrieve with the mouse.
So, how are we going to loop through all the sheets?

    Num_Sheets = application.Sheets.Count
    For y = 1 To Num_Sheets
        Sheets(y).Select
    Next x
 

application.sheets.count will return the number of sheets in the current workbook.  This value is stored in a variable called Num_Sheets.  Lets say there are 10 sheets in this workbook.

Each sheet in Excel has a sheet number attached to it.  The numbers start with 1 and increase by 1.

Sheets(y).select will make the sheet with that number active (it will actually select the sheet and make it visible).

So, the command sheets(2).select will select the sheet with the index number 2.

for y = 1 to num_sheets

next y

This is a for next loop that will execute the commands within the specified number of times.  Since num_sheets = 10, this loop will execute 10 times.

So we want to get the count of number of sheets in the workbook,

select each sheet

perform a retrieve operation

Redo these steps as many times as there are sheets in the workbook.

You can just copy the following code into a module in Excel.  You can watch the following to see how copying into a module is performed.  1000_cams/VBA Lesson 1000.html

Declare Function EssMenuVRetrieve Lib "ESSEXCLN.XLL" () As Long

Sub loop_all_sheets()
    Num_Sheets = application.Sheets.Count
    ' The number of sheets in the workbook are stored in the Num_Sheets variable
    For y = 1 To Num_Sheets
    'This is a loop that will repeat Num_Sheets times.  If Num_Sheets is 10 then the
    'loop will execute 10 times.
        Sheets(y).Select
        x = EssMenuVRetrieve
    Next x
End Sub