Protect Excel VBA Projects

I have a blog that described how to protect our Excel worksheet.  A few readers asked me how to protect Excel VBA projects.  It is actually quite simple and you are going to see how easy it is.

Assume we are already in Excel VBA Editor.

  • Click Tools
  • Select VBAProject Properties…

image

 

We would see a Project Properties Dialogue.

  • Click the Protection tab
  • Enable Lock project for viewing
  • Enter a password twice.
  • Click the OK button.

image

Now, we can save the file.  Next time when we want to view the VBA code, we would have to enter the password first.

image

Password protection is very simple to setup; however, password protection is not very secure in Excel environment.  If you Google “Remove Excel Password”, you would find many utilities that claim to be able to unlock your password.  Password protection is a good way to protect your Excel investment against our users who accidentally modify our data / code.  But if we really want to protect our intellectual property, then password protection may not be the most suitable approach.  Of course, a strong password always help!

 

Andrew Chan helps you to make better and faster decisions

VBA vs. VS – Final: To Migrate or NOT To Migrate

I hope I didn’t get all of you bored to death with all the technical stuffs in the last 2 weeks.  Don’t worry; this article would be the last one of the series.  I started this series because I know a lot of part time Excel VBA developers; their full time job may be accountants, financial analyst, traders….  They have been using VBA for a long time; and I admit some of them are even better than me.  However, they don’t seem to understand that VBA has its pros and cons and there are other development products which can do better job than VBA.  They just don’t have the time to find out about it.  So I started writing about VBA and its potential replacement, i.e. Visual Studio (VS).

I want to wrap up this series of blog by summarizing if you should use VBA or VS.

To Migrate or NOT To Migrate

Stay with VBA if:

  • You want to use a lot of Excel functions and Add-In.
  • You have deployment constraint within your organization.
  • Your code is tightly integrated with Excel.
  • Your code is simple or for one time use.
  • Your code does not involve a lot of business logic.
  • You code has to run on older version of Excel (2003 and older).

Migrate to Visual Studio if:

  • Performance is an issue.  VS compiler allows better optimization and .Net Framework provides mechanisms to handle parallel and distributed processing
  • Your code involves a lot of business logic.  VB.Net is an object oriented programming language which provides a more effective way to model business logic and inheritance supports robust code reuse.
  • Your business processes requires frequent changes and deployment.  VS has bundled with Team Foundation Server (TFS).  TFS is a comprehensive application lifecycle management (ALM) suite which covers requirement management, change management, project management, configuration management, build management, testing, release management. deployment and issue management.
  • You need easier code maintenance.  VS stores data, code and assembly in separated locations; so it facilities easier code maintenance
  • You want to run your applications on web, cloud or even smart phone.  .Net Framework provides the foundation of multi platform development.
  • You need to integrate with line of business (LOB) systems such as SAP and PeopleSoft.  .Net Framework has built in Web Services support that we can easily integrate your LOB system with Microsoft Office.
  • You need a wide variety of user interfaces.  VS offers Windows Form, Windows Presentation Foundation (WPF) and Silverlight which would cover the needs of all your user experiences.
  • You need to deploy the application to a wide spectrum of users (both onsite and offsite).  Microsoft System Center Configuration Manager can automatically deploy your .Net applications, setup the right permission and even apply future patches.
  • You need to fine tune the application security for different users.  .Net Frameworks allows you to implement a more flexibility security model.

Conclusion

To migrate or not to migrate, it is always a tough decision. The examples I showed you, seemed very simple but in reality the migration is always a huge undertaking in term of both resources and risk.  You should consider their difference with regard to code maintenance, deployment, programming paradigm, performance, and security.  Will your application benefit from Microsoft .NET Framework?  Do your developers know how to do object oriented design (OOD) and understand the difference between VBA and VB.Net programming syntax?  WPF supports more GUI features than VBA UserForms; will your users have better experience with WPF?  It may sounds a stupid question but you may want to confirm with your IT department that you can install .Net Framework to all the computers.

However, the benefits are also significant!  Your application can run 10, 100 or even 200 times faster.  It can be deployed to cloud so you don’t need to buy new hardware or software plus you can add more horse power anytime you want.  You can find out what / who / when / why / how about a particular change.  Object Oriented (OO) is a proven methodology that can reduce development efforts; this is why all the popular programming languages like Java, C#, C++ and Objective C are OO.  There are more benefits about migrating to VS but I have to stop here.

I hope my blogs can help you to make an educated decision on the choice of development tools.  And if you have any further question or comment, please don’t hesitate to contact me at chan_a@algconsultings.com.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

VBA vs. VS – Part 5: First Excel Project

So much about background and theory, let’s start our first Excel project using Visual Studio 2010.

After we started Visual Studio 2010, we can build our first Excel project:

  • Select File, then New and choose Project… image
  • New Project dialogue is displayed, select Excel 2010 Workbook and enter First Excel Project into Name and then click OK button.

image

  • We choose to Create a new document, click OK button

image

  • Visual Studio creates the First Excel Project project, and adds the following files to the project.
  • First Excel Project .xlsx – Represents the Excel workbook in the project. Contains all the worksheets and charts.
  • Sheet1.vb file
  • Sheet2.vb file
  • Sheet3.vb file
  • ThisWorkbook .vb file – Contains the design surface and the code for workbook-level customizations.

image

  • In Solution Explorer, Right Click on Sheet1.vb and select View Code.

image

image

  • Replace the Sheet1_Startup event handler with the following code. When Sheet1 is opened, this code adds Now to first 100,000 cells in Col A.

Private Sub Sheet1_Startup() Handles Me.Startup
    Cells(1, 1) = Now

    For i = 2 To 100000
        Cells(i, 1) = Now
    Next
End Sub

  • When the code is ready, press F5 or click the green arrow button to build and run your project.

image

  • Excel opened First Excel Project Workbook and it would take about 10 seconds to process and added Now to 100,000 cells.

imageimage

Instead of cut and paste my code into the program, if you actually type my code then you would see VS editor is much smarter.  When I tried to type Cells in the editor, it would actually show me the variable name.

And when I finished the For statement, VS editor would automatically add the Next statement.

image

It is a very simple program, so I decided to repeat the simple code in an ordinary Excel 2010 VBA environment.

image

Run the program on VBA.

imageimage

I am actually surprised to see that the same logic run faster in VS environment; I would expect the other way.  So I have to do more study to find out the reasons behind.  This is a very simple demo that you may never find in practical business environment.  So your real experience would definitely be not the same.  However, this demo should give you some idea how Excel (and other Office product) can be automated in VS as well.

There are 3 types of project templates for Excel, Add-in, Template and Workbook.  We were using Excel 2010 Workbook for this demo which is a document-level customizations.  This means the code is associated with a single workbook; we have to open this particular workbook to run the workbook.   If you want the code to be avialable for all Excel workbooks, then we should choose the Add-in template.  Add-ins is associated with  Microsoft Excel, regardless of which documents are open.

Conclusion

I am going to write 1 more article to wrap up my stories, I would discuss the situations that I would recommend my clients to migrate their application from VBA to VS.  VBA is a great development tool and I still use it to check results from the financial models  that I developed using C++.  However, I hope you can now understand that VBA has limits and there are tools on the market that can do a better development job under certain circumstances.  For example, do you want to integrate your line of business (LOB) systems like SAP or PeopleSoft with Microsoft Office suite?  It would be much easier to do it with VS than VBA, you can find some information from Microsoft MSDN: Microsoft Office and LOB integration.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

VBA vs. VS – Part 4: .Net Framework

.Net Framework may be new to a lot of VB6 / VBA developers; however, the idea is not new.  When we used VBA / VB6 to create a simple graphic user interface (GUI), e.g. MsgBox or use Data Access Objects (DAO) to retrieve data from Microsoft Access, we are actually calling a runtime class library that contains millions line of code.  We don’t have to worry about all the Windows / database API and we don’t need to manage the messages flying around domain specific objects.  The VB6 runtime and DAO would take care everything for us and we only need to focus on business logic.  This is the beauty of VB6 / VBA.  Let Microsoft takes care about the technical complexity ! 

This is the same concept behind .Net Framework; the Common Language Runtime (CLR), the runtime environment for .Net Framework is more powerful than VB6 runtime and the Framework Class Library provides a larger range of features.

If you read my blog VBA vs. VS – Part 1: Performance Review, you can see how easy and powerful .Net Framework can be.  Instead of using a regular For statement, I used Parallel.For; Parallel is a new feature of .Net Framework which would make the best use of our multi core CPU.  The performance was improved by 2.5 times.  We would not be able to maximize our hardware investment if we are using VB6 / VBA.

.Net Framework

The .Net Framework consists of 2 main components, Common Language Runtime (CLR) and Framework Class Libraries (FCL).

    Common Language Runtime

    CLR is an abstraction layer over the operating system that all .Net programs are running on.  It takes care all technical details for developers; so we don’t need to worry about:

  • CPU management
  • Memory management
  • Thread management
  • Objects management
  • Exception handling
  • Garbage collection
  • Security

We can use VB.Net, C#, C++ and other .Net compliance languages to develop applications.  CLR makes it easy to design components and applications whose objects interact across different programming languages. Objects written in .Net languages can communicate with each other, and their behaviors can be tightly integrated.

Framework Class Libraries

FCL is a a collection of thousands of reusable classes that encapsulate a large number of common system functionality.  To name a few, it provides simple file I/O, GUI, and database services or we can use something much more powerful and sophisticated such as parallel programming, distributed computing, workflow and cryptographic services. 

As a business developer, even if I have the technical knowledge, it may take me months to build a financial application that can fully utilize my multi cores CPU.  How much effort do we have to spend if we want to migrate the same application to a server farm with hundred or even thousand CPU?

Advantages

.Net Framework has many advantages over VB6 / VBA.

Multi-Language Development Platform

Instead of just using VB, CLR allows us to use the language we prefer.  We can use:

  • Static Language – Visual Basic.Net, C# and C++ .
  • Dynamic Language – Managed JScript, IronRuby, and IronPython.

User Experiences

Developers can use Windows Presentation Foundation (WPF) or Silverlight to develop richer GUI.

Most Windows developers are not graphic designer and graphic designers do not always know how to program.  WPF provides a common file format (XAML) which allows designers to work alongside developers in a workflow that promotes creativity while maintaining full fidelity.  WPF is a powerful framework that we can integrates GUI, documents, 2D and 3D graphics, hardware accelerated effects, and media content. 

Silverlight provides a cross-browser, cross-platform, and cross-device plug-in for advertising and rich interactive applications

Web Applications

Everyone is using the web now!  How can we run our applications on web?

ASP.NET enables developers to create anything from small, personal Web sites through to large, enterprise-class dynamic Web applications. Together with Asynchronous JavaScript and XML (AJAX ), developers can quickly create efficient, interactive, and highly personalized web sites

Distributed Systems

Do our systems need to communicate with our business partners or services providers?  Do we want to build a server farm to run our financial model?

Windows Communication Foundation (WCF) provides a model that developers can rapidly build service-oriented applications that communicate across the web and the enterprise.

Business Processes

We work as a team.  How can we work more effectively together?

Windows Workflow Foundation (WWF) delivers a  model that developers can use to build a business process which enable closer collaboration among business users.

Data Access

We already have DAO to access data from relational database but we now have data from XML and other applications.

ADO.NET provides a rich set of components for creating distributed, data-sharing applications,providing access to relational, XML, and application data.

Conclusion

If you visit Microsoft .NET Framework Class Library, you would see how feature rich it is.  I have used .Net Framework for over 5 years but I seriously doubt if I ever use more than 10% of the functionality.  I can  now develop web sites, distributed systems, and cloud applications.  Accessing data from RDB, XML and other applications is much easier.  I can  do thing that was impossible with VB6 / VBA or at least 10 times harder!

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

VBA vs. VS – Part 3: What is new in VB.Net?

I mentioned in my last blog, VBA vs. VS – Part 2: Programming Paradigm Review that VB.Net is now an object oriented (OO) programming language.  So what OO features have been introduced to VB.Net programming language?

Inheritance

VB.Net supports inheritance; it allows us to derive classes from an existing base class by extending the properties and methods of the base class. They can also override inherited methods with new implementations.

Class Base
    Sub Method1()
        MsgBox("This is a method in the base class.")
    End Sub
    Overridable Sub Method2()
        MsgBox("This is an override method in the base class.")
    End Sub
End Class

Class Override
    Inherits Base
    Public Field2 As Integer
    Overrides Sub Method2()
        MsgBox("This method is overrided in a derived class.")
    End Sub
End Class

Protected Sub Test()
    Dim O1 As New Base
    Dim O2 As New Override
    O1.Method1() 
    O1.Method2() 
    O2.Method1() 
    O2.Method2()
End Sub

Overloading

Overloading is the ability to create several methods with the same name which differ from each other in terms of the type of the input and the type of the output of the function.

Overloads Sub Print(ByVal theChar As Char)
    ‘ Add code that displays Char data.
End Sub
Overloads Sub Print(ByVal theInteger As Integer)
    ‘ Add code that displays Integer data.
End Sub
Overloads Sub Print(ByVal theDouble As Double)
    ‘ Add code that displays Double data.
End Sub

Overriding

Overrides allows derived classes to override member variable or functions that are inherited from parent class. 

Const BonusRate As Decimal = 1.45D
Const CommissionRate As Decimal = 14.75D

Class Commission
    Overridable Function Payment( _
        ByVal FaceAmount As Decimal, _
        ByVal CommissionRate As Decimal) _
        As Decimal

        Payment = FaceAmount * CommissionRate
    End Function
End Class

Class BonusCommission
    Inherits Commission
    Overrides Function Payment( _
        ByVal FaceAmount As Decimal, _
        ByVal CommissionRate As Decimal) _
        As Decimal

        ‘ The following code calls the original method in the base
        ‘ class, and then modifies the returned value.
        Payment = MyBase.Payment(FaceAmount, CommissionRate) * BonusRate
    End Function
End Class

Sub RunCommission()
    Dim CommissionItem As Commission = New Commission
    Dim BonusCommissionItem As New BonusCommission
    Dim FaceAmount As Decimal = 40000D

    MsgBox("Normal pay is: " & _
        CommissionItem.Payment(FaceAmount, CommissionRate))
    MsgBox("Pay with bonus is: " & _
        BonusCommissionItem.Payment(FaceAmount, CommissionRate))
End Sub

Constructors and Destructors

Constructors are functions that control initialization of new instances of a class. Conversely, destructors are methods that free system resources when a class leaves scope or is set to Nothing.  VB.Net supports constructors and destructors using the Sub New and Sub Finalize procedures.

Interfaces

Interfaces determine what properties and methods of the classes can be consumed within the system.  It is a logical view of the classes, which provide no implementations.

Interface IAsset
    Event ComittedChange(ByVal Success As Boolean)
    Property AccountBalance() As String
    Function GetBalance() As Double
End Interface

Class InvestmentAccount
    Implements IAsset

    Public Event ComittedChange(ByVal Success As Boolean) _
       Implements IAsset.ComittedChange

    Private AccountBalanceValue As String

    Public Property AccountBalance() As String _
        Implements IAsset.AccountBalance

        Get
            Return AccountBalanceValue
        End Get
        Set(ByVal value As String)
            AccountBalanceValue = value
            RaiseEvent ComittedChange(True)
        End Set
    End Property

    Private BalanceValue As Double

    Public Function GetBalance() As Double _
        Implements IAsset.GetBalance

        Return BalanceValue
    End Function

    Public Sub New(ByVal AccountBalance As String, ByVal Balance As Integer)
        Me.AccountBalanceValue = AccountBalance
        Me.BalanceValue = Balance
    End Sub
End Class

Delegate

Delegate is a reference to function and it is particular useful if we want to raise events that can call different event handlers under different circumstances.  We can dynamically associate event handlers with events by creating a delegate when we use the AddHandler statement. At run time, the delegate forwards calls to the appropriate event handler.  Delegate is not limited for event handlers and we can use it to call different versions of functions at run time.

Delegate Sub MyFirstDelegate(ByVal x As Integer)

TestClass
Sub TestSub(ByVal x As Integer)
MsgBox("The value of x is: " & CStr(x))
End Sub
End Class

Protected Sub DelegateTest()
Dim T1 As New TestClass
Dim msd As MyFirstDelegate= AddressOf T1.TestSub
msd.Invoke(10)
End Sub

Shared members

Shared members are properties, procedures, and fields that are shared by all instances of a class or structure.

Public Class SharedClass
    Public Shared Count As Integer = 1
    Public Shared Sub ShareMethod()
        MsgBox("Current value of Count: " & Count)
    End Sub

    Public Sub New(ByVal Name As String)
        Me.SerialNumber = Count
        Me.Name = Name
        Count += 1
    End Sub
    Public SerialNumber As Integer
    Public Name As String
    Public Sub InstanceMethod()
        MsgBox("Information in the first object: " & _
            Me.SerialNumber & vbTab & Me.Name)
    End Sub
End Class

Sub TestShared()
    Dim Shared1 As New SharedClass("keyboard")
    Dim Shared2 As New SharedClass("monitor")

    Shared1.InstanceMethod()
    Shared2.InstanceMethod()
    SharedClass.ShareMethod()
End Sub

Conclusion

There are many non OO related enhancements that were added to VB.Net, e.g. structured error handling, multithreading.  However, I am not going to cover them in this article.  So far, I covered most of the OO features in VB.Net and I the examples are not so difficult to follow.  But in case if you have any question, please feel free to ask.

I often tell people that the OO programming is easy to pick up; the challenge is OO design.  How can we design a class library that is simple to maintain and easy to reuse?  It would take us years before we can master the OO design.  But at the end, it is worth the efforts!

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

VBA vs. VS – Part 2: Programming Paradigm Review

Part of this article is also being published in the July issue of Comp Act

When Microsoft upgraded VB6 to VB.Net in 2002, they decided to make VB.Net an object oriented programming language.  It was a hard choice because it created a huge gap for VB6  developers.  Most VB developers were not familiar with object oriented, even though some of them had worked extensively with objects; e.g. Microsoft Excel Objects.  VB6 / VBA only allowed developers to create their own business class.  Why was it so hard for some VB6 / VBA developers to migrate to VB.Net?  And why did Microsoft take such bold move to migrate VB to OO programming language?

VBA is an object based programming language.  They allow us to create classes and instantiate objects.  However, unlike object oriented programming language, they don’t have inheritance and hence without polymorphism.  So what are inheritance and polymorphism?  And why are they so important?  Let’s review some of the basic object oriented concepts first.

Object Oriented Concepts

There are a few object oriented concepts:

  • Abstraction
  • Encapsulation
  • Inheritance
  • Polymorphism

Abstraction

Abstraction is "the mechanism and practice of abstraction reduce and factor out details so that one can focus on a few concepts at a time."  Since abstraction extracts key characteristics of an object and hides other immaterial complexity, our readers should easily understand and visualize what we want to discuss.

"I just bought a new Samsung 46-inch 1080p 120Hz LCD TV," my friend told once told me. Size, number of lines, refresh rate and type are the key characteristics of an HDTV. He can go on to tell me about its physical dimension, weight, power consumption, etc., but most people don’t care, and would prefer being told "I just bought a new TV," abstracting the technical specifications to hide unnecessary details.

Encapsulation

Encapsulation is "the process of compartmentalizing the elements of an abstraction that constitute its structure and behavior; encapsulation serves to separate the contractual interface of an abstraction and its implementation."

Encapsulation allows programmers to use any method without understanding the details of implementation. This can drastically reduce the learning curve or maintenance effort of an financial system.

For example, how many developers understand the implementation of ADO.Net? With ADO.Net, most of us can learn in a couple of hours how to write a simple function to retrieve data.

If our financial system consists of 1,000+ classes, do we want all our developers to understand every single class and every method within? It would take forever to train a new developer. Encapsulation will shorten the learning curve of developers and will provide better system manageability and stability.

Inheritance

Inheritance is "a way to form new classes (instances of which are called objects) using classes that have already been defined. Inheritance is employed to help reuse existing code with little or no modification."

When we derive a new annuity calculator from its base class, we only have to implement the new features. It can save us enormous development, checking and testing time.  We can add new data type and member functions or even override existing functions.

Inheritance also increases overall system stability and reduces quality assurance effort. It allows developers to reuse code, enhance and modify existing class.

Polymorphism

"Polymorphism in the context of object-oriented programming, is the ability of one type, A, to appear as and be used like another type, B."   Inheritance is required in order to achieve polymorphism.

Polymorphism can greatly simplify coding and allow extensibility for future enhancements. With polymorphism, we can call 10 different annuity calculators from a single line of code. When we want to add five more new annuity calculators, we don’t need to modify the calling function.

Benefits of Object Oriented

Since OO promotes code reuse which would result in better reliability, robustness, extensibility and maintainability.  No wonder more and more computer systems have been developed using OO languages.  According to a study by TIOBE, OO programming languages are the most popular for more than 4 years; 55.4% code is based on OO languages.

However, migrating to OO programming language would not automatically grant us all these benefits!  Let’s say we had an old financial system that we developed using VB 6 and it had 20 program files. We decided to migrate to the latest Visual Basic.Net and create 20 classes to store each program files respectively. Moreover, since we couldn’t think of a good name for each class, we simply named them fin01, fin02 … fin20, the same name as each of the program files. We then copied the content of each program file into the corresponding class. By doing this, we created a system that uses OO programming language. But what benefits does our system have from this migration? Not a lot unfortunately!

In order to realize the true benefits of an OO programming language, we must understand the SOLID OO principles first.

SOLID Object Oriented Principles

  • Single Responsibility Principle
  • Open Closed Principle
  • Liskov Substitution Principle
  • Interface Segregation Principle
  • Dependency Inversion Principle

Single Responsibility Principle (SRP)

"There should never be more than one reason for a class to change."–Robert Martin, SRP paper linked from The Principles of OOD.

Simple is beautiful. Each class should have only one responsibility and focus to do one single thing.

Our annuity calculator classes are already very sophisticated. If we also implement policy projection, decrement calculation and cashflow projection within it, then it would be huge and all our developers may always work on this big class together.

SRP would promote the reuse of code, clarity and readability. Our system would also be easier to test, enhance and maintained. Developers would also find less contention for source code files.

Open Closed Principle (OCP)

"Software entities (classes, modules, functions, etc.) should be open for extension, but closed for modification."–Robert Martin paraphrasing Bertrand Meyer, OCP paper linked from The Principles of OOD.

Most of us work on existing systems rather than build new systems from scratch. When we add new features to a system, we often feel more comfortable adding new functions than modifying an existing codebase. Why? We worry that our modifications would accidentally add new bugs to the systems, especially fragile systems. OCP recommends extending existing codebase, not modifying it.

If we already have 10 different annuity calculator classes, adding a new one should not modify any existing code.

Once we have followed SRP to build our system, it would be easier to implement OCP. Systems following OCP are often more stable because existing code does not change, and new changes are isolated. Deployment is also faster because existing features would not be accidentally modified.

Liskov Substitution Principle (LSP)

"Functions that use pointers or references to base classes must be able to use objects of derived classes without knowing it."–Robert Martin, LSP paper linked from The Principles of OOD

This principle is just an extension of the Open Close Principle, and it means that we must make sure that new derived classes are extending the base classes without changing their behavior so that the derived classes must be completely substitutable for their base class. Otherwise the new classes can produce undesirable effects when they are used in existing program modules.

Below is a classic example of LSP:

public class Rectangle
{
protected int _width;
protected int _height;
public int Width
{
get { return _width; }
}
public int Height
{
get { return _height; }
}
public virtual void SetWidth(int width)
{
_width = width;
}
public virtual void SetHeight(int height)
{
_height = height;
}
} public class Square: Rectangle
{
public override void SetWidth(int width)
{
_width = width;
_height = width;
}
public override void SetHeight(int height)
{
_height = height;
_width = height;
}
}
[TestFixture]
public class RectangleTests
{
[Test]
public void AreaOfRectangle()
{
Rectangle r = new Square();
r.SetWidth(5);
r.SetHeight(2);
// Will Fail – r is a square and sets
// width and height equal to each other.
Assert.IsEqual(r.Width * r.Height,10);
}
}

Square class is derived from Rectangle class; so C++ allows a Square object to be cast into a Rectangle object. However, Square class has its own setter functions; so r.SetWidth and r.SetHeight would set width and height equal to each other. What do we expect r.Width * r.Height to be equal to? Is r a Rectangle or Square object?

LSP would make the system easier to test and provide a more stable design.

Interface Segregation Principle (ISP)

"Clients should not be forced to depend upon interfaces that they do not use."–Robert Martin, ISP paper linked from The Principles of OOD

Again, it is another "simple is beautiful" principle. We should have multiple slim interfaces rather than a giant interface. Each interface should serve one purpose only.

If we have both policy month and calendar month projections, put them in two separate interfaces. For example, use IPMCashflowProj and ICMCashflowProj rather than just one interface named ICashflowProj.

With ISP, design would be more stable and flexible; changes are isolated and do not cascade throughout the code.

Dependency Inversion Principle (DIP)

"A. High level modules should not depend upon low level modules. Both should depend upon abstractions.

B. Abstractions should not depend upon details. Details should depend upon abstractions."–Robert Martin, DIP paper linked from The Principles of OOD.

Low-level classes implement basic and primary operations, and high-level classes often encapsulate complex logic and rely on the low-level classes. It would be natural to implement low-level classes first and then to develop the complex high-level classes. This seems logical as the high-level classes consume low-level classes. However, this is not a flexible design. What happens if we need to add or to replace a low-level class?

If our annuity classes (high level) contain cashflow classes (low level) directly, and we want to introduce a new cashflow class, we will have to change the design to make use of the new cashflow class.

In order to avoid such problems, we can introduce an abstraction layer between the high-level classes and the low-level classes. Since the high-level modules contain complex logic, they should not depend on the low-level modules. The new abstraction layer should not be created based on the low-level modules. The low-level classes are created based on the abstraction layer.

Once we implement DIP, our financial system will be significantly easier to extend, and deploying new features will take less time.

Conclusion

Please keep in mind that all of these principles are just guidelines that would make our system more stable, easier to maintain and enhance; but they are not ironclad rules. We must apply our own judgement and experience.

The learning curve from VBA to VB.Net is challenging; however, the benefits can also be enormous and definitely worth the efforts.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

VBA vs. VS – Part 1: Performance Review

I had used VB6 / VBA for many years and I like them a lot!  Like most Windows programmers, I developed my first Windows application using Visual Basic ; I didn’t have to learn or understand Windows API which drastically reduced the learning curve.  However, technology has changed a lot in the last 10 years.  Microsoft stopped support VB6 in March 2008 and as of July 1, 2007, Microsoft no longer offered VBA distribution licenses to new customers. 

Yes, we can still find VBA in Office 2010 but what improvement has been introduced in the latest VBA?  We can now migrate Excel 4 macros to VBA – this is the only enhancement that was mentioned in Microsoft Technet , Changes in Excel 2010.  Does anyone still have any Excel 4 worksheet?

I don’t think Microsoft would get rid of VBA in near future but at the same time, is Microsoft going to invest any significant effort to improve VBA?  I seriously doubt it.  So do you want to use a development tools that didn’t have any major upgrade for the last 10 years?  Or do you prefer to use the latest development tool that allow you to simplify coding, debugging and deployment?  A tool that you can target multiple platforms, e.g. Windows, web, cloud and of course Office environment.

Let’s look at the difference between Visual Studio and VBA by first reviewing their performance.  I developed a very simple financial calculator using Excel VBA.  I ran it.

Option Explicit

Public Sub Main()

Dim startTime As Date

Dim endTime As Date

Dim run As Long
Dim runs As Long

Dim Age As Integer
Dim AgeStart As Integer
Dim AgeLimit As Integer

Dim InterestRate As Double
Dim MeanInterestRate As Double
Dim Variance As Double
Dim Scenarios As Integer
Dim Scenario As Integer

Dim i As Integer
Dim PV As Double

runs = 10000

AgeStart = 20
AgeLimit = 100

Scenarios = 500
MeanInterestRate = 0.05
Variance = 0.05

startTime = Now

For run = 1 To runs

    For Age = AgeStart To AgeLimit
   
        For Scenario = 1 To Scenarios
       
            InterestRate = MeanInterestRate + (0.5 – Rnd()) * Variance
            PV = run * 1000 * (1 – (1 + InterestRate) ^ (Age – 100)) / InterestRate

       
        Next

    Next

Next

endTime = Now

MsgBox (startTime & endTime)

End Sub

image

It took 114 seconds to complete.

I copy and paste the whole program to Visual Studio 2010.  I compiled it and ran it again.

image

It took 58 seconds so it is nearly 2 times faster.

I modified couple lines in Visual Studio and ran it again.

image

Now it only took 23 seconds; nearly 5 times faster than VBA.  What did I change in the program?  Let’s look at it.

Imports System.Threading.Tasks

Module Module1

    Sub Main()
        Dim startTime As Date

        Dim endTime As Date

        Dim runs As Long

        Dim Age As Integer
        Dim AgeStart As Integer
        Dim AgeLimit As Integer

        Dim InterestRate As Double
        Dim MeanInterestRate As Double
        Dim Variance As Double
        Dim Scenarios As Integer
        Dim Scenario As Integer

        Dim i As Integer
        Dim PV As Double

        runs = 10000

        AgeStart = 20
        AgeLimit = 100

        Scenarios = 500
        MeanInterestRate = 0.05
        Variance = 0.05

        startTime = Now

        Parallel.For(0, runs, Sub(run)

                                  For Age = AgeStart To AgeLimit

                                      For Scenario = 1 To Scenarios

                                          InterestRate = MeanInterestRate + (0.5 – Rnd()) * Variance
                                          PV = run * 1000 * (1 – (1 + InterestRate) ^ (Age – 100)) / InterestRate

                                      Next

                                  Next

      End Sub)

        endTime = Now

        MsgBox(startTime & endTime)

    End Sub

End Module

Do you know what the changes are?

The CPU inside my computer is an Intel Q9400 which is a quad core processor.  However, the first 2 runs (VBA and VS 2010) only utilized 1 core.  Visual Studio 2010 supports parallel programming which would automatically make use of all cores on my system.  The code to utilize parallel programming is quite simple;  I just replaced the For statement with Parallel.For, then Visual Studio would execute the program on all cores rather than single core.

Without spending any effort on optimization, the performance gain to migrate from VBA to VS 2010 is already enormous, i.e. nearly 500%.

  Execution Time (Seconds)
Excel VBA 114
Visual Studio 2010 (w/o Parallel Programming) 58
Visual Studio 2010 (with Parallel Programming) 23

Someone may argue my program does not reflect a real financial application because it does not include any database input / output (I/O).  They are right!  My program does not have any I/O and I/O may not have such significant performance gain.  However, today’s financial model may involve hundred to thousand scenarios and each scenario can contain a few million calculations, therefore, the effort to I/O a small piece of data would be very minimal.

The purpose of this demonstration is to illustrate a performance gain can be easily achieved by migrating to Visual Studio 2010.  My next blog would be addressing the different programming methodology between VBA and VS, i.e. Object Based and Object Oriented.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

Happy 17 Birthday Visual Basic for Applications!

Visual Basic for Applications (VBA) is 17 years old!  It was introduced with Excel 5.0 in 1993; it offered Excel great programmability and allowed Excel users to automate repetitive processes and added extra functionality to Excel.

VBA is easy to learn and simple to use, so a lot of power users have used VBA to develop business applications.  I don’t know exactly how many developers have used VBA but VBA is definitely one of the most popular development platforms.  Developers have used it with Excel, Outlook, Visio, Word and even some non-Microsoft products. Excel appears to be the most common one that you would find VBA running on.

Microsoft has added new features to VBA in every new version of Office.  However, the biggest change is the introduction of Visual Studio Tools for Office (VSTO). VSTO allows developers to program Microsoft Office 2003 and later versions of Office. Why has Microsoft offered 2 development platforms? Why is Microsoft so excited about VSTO? This is what I am going to discuss in this blog.

VBA is no doubt a very powerful development tools which allows developers to build business applications but it has limits and issues:

· Performance – VBA compiles code into pseudo code which would slow down the execution.

· Multi-Threaded – Modern CPU can run up to 16 threads but VBA can only support single thread application.

· Object Oriented Programming – VBA is a object based programming language and it does not offer the same level of code reusability and easy maintenance as other object oriented programming languages.

· Document Centric – Most VBA applications store the code and data in the same file. Business users have to update the data frequently so it makes change management and deployment extremely difficult.

· Windows Functionality – VBA only provides a very limited set of Windows functionality so if your programs need to access other Windows core services, then you have to program through Windows API and it would be cumbersome.

· Security – VBA can potentially open the door for malware and other security issues.

· New Technology – VBA does not support new technologies, e.g. Common Language Runtime, .NET Framework, Web Services, Parallel Programming…. etc.; these new technologies can help you to develop more manageable, robust, and scalable business applications.

· Development Environment – anyone know when was the last time VBA development environment got enhanced? Today’s development tools have richer debugging tools, customizable IDE interface, emulators, add-ins and more; so you can build your system quicker.

In simple English, VBA does not support today new technologies, both hardware and software. Although, VBA is still available in Office 2010; Microsoft no longer offers VBA distribution licenses to new customers as of 2007.  I can imagine VBA would have its 20th birthday or even 25th birthday.  However, do you want to continue to use VBA for your development?  I doubt if Microsoft is going to make any significant investment on it.

Microsoft introduced Visual Studio .Net in 2003; an effort tried to unify all Microsoft development environments and bring object oriented to its programming languages, e.g. C# and Visual Basic .Net. Microsoft also introduced .NET Framework and Common Language Runtime which would expedite and optimize the development process and provide access to system functionality.

Visual Studio Tools for Office (VSTO) is a set of project templates available in Visual Studio .Net and allows the Office applications to be written in .Net environments.   So you can now integrate the best from 2 worlds, i.e. Office applications and .NET Framework.

image

Microsoft just released Visual Studio 2010 couple months before Office 2010.  You should really try the development features in both products.

Which one is more suitable for you?  VBA?  VSTO?

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!