Converting Access VBA to VB.NET – General Principals (21st February 2010)

support@ascassociates.biz , Sales Enquiries

Converting Access VBA to VB.NET – General Principals (21st February 2010)

In the first article in this series we looked at how Access Global Variables convert to .net and how to construct .net classes to support Access style programming in .net.

This article discusses general issues relating to converting the VBA Code in an Access application to code that can form part of your WebForm application in .net. We know that it is possible to convert VBA to VB.NET, but there are a number of issues which you need to consider that will affect the degree of success in doing this.

There is a difference between pasting a few VBA routines into a .net webform application and having that work (which is relatively easy) when compared to taking a large Access application with several thousand lines of program code and converting that to .net

Pre-requisites

There are several things you can check in your VBA code which will make the code conversion run smoothly.

Option Explicit, very old versions of Access did not include the now default the Option Explicit setting in code modules, without which variables can be used without having them declared. It is essential that in all modules have this default setting, and you have compiled your VBA code to check that all variables are declared. Broken VBA code will not be easy to convert.
Function return types, Access allows you to omit the return type from a function, this means the function will return a Variant data type, the equivalent in .net is an Object data type. Check that all your functions specify a return type for a cleaner code conversion.
Function and Subroutine parameter types. Ensure that all your parameters on functions and subroutines have explicit data types, such as (MyValue as String) rather than having (MyValue) which will be assumed to be a Variant and convert to an Object data type.
Ensure that where you use Optional parameters in function and Sub routines that you provide a default value; this is mandatory in .net so during the conversion it will lead to problems to be resolved or speculative defaults being assigned to parameters.
The Eval function has no equivalence in .net, so any code using this should be re-written without using Eval before conversion.
Constants can be defined without specifying a data type, once converted to .net they will need a data type, during conversion a default type could be estimated on the basis of the nature of the constant value, but having this pre-defined in your code is advantageous.
You have a constants file for VBA and DAO constants for example Public Const adOpenForwardOnly As Int32 = 0
Convert explicit date arithmetic to use functions like DateAdd
For any functions or subroutines which receive parameters which they change, ensure that you have the ByRef keyword (see later in this article for an explanation of this).

Pre-Conversion checklist

All code modules contain Option Explicit
All functions have an explicit return data type
All subroutine and function parameters have data types specified for parameters
All optional parameters in functions and subroutines have default values
Use of Eval function has been re-written
Constants have explicit data types
You have files with the VBA and DAO constants defined with values
Convert any explicit date arithmetic to use build in functions
Check when ByRef needs to be explicitly stated

Designs which exploit the Access Environment

Access developers can exploit many special built in features in the Access environment, whilst many of these can be replicated through supporting classes there a number of design techniques employed by Access developers which will be more difficult to replicate in .net.

An example of more complex code which can be easily dummied-out is the references to the CurrentDB.Properties collection; this contains setting which control how an Access application behaves and in this case providing a class which patches up the code with a ‘do nothing’ action is appropriate. Code such as this should eventually be removed from the .net conversion as it is no longer applicable to the new environment.

An example that is much more difficult to resolve is a reference being made to another form which is also expected to be open using for example the statement Forms!SalesOrderForm!OrderId to refer to a control called OrderId which is on another form called SalesOrderForm.

Classes and Modules

The places in which code is kept in Access is either in Modules or in Form/Report Modules (Access also supports class modules which are not discussed here). The Modules code is normally public and available to all parts of an application, whereas the Form/Report modules contain Form/Report specific code, which is normally written in response to Events as a user interacts with the Form.

In the present article we are discussing only the general Modules and not those that are specific to individual forms and reports responding to events (this will be discussed in a future article).

The general modules in Access can be either of type Module or Class Module. Both of which are converted into .net classes. Whilst it will seem obvious that a Class Module in Access maps to a Class in VB.NET, a standard VBA Module (example called CodeExamples) maps to Class also called CodeExamples; in this case the class contains the Module of the same name:-.

Imports Microsoft.VisualBasic

Public Module CodeExamples
Public Function…………………
End Module

So whilst Access keeps all these Functions and Sub Routines in a module, in .net this module simply gets placed inside a class with the same name as the module.

Language Syntax Compatibility

There are a number of specific differences between the language syntax, such as the declaration of global variables, use of functions like Eval and ability to perform direct addition on date arithmetic (not allowed in .net). So there will be specific cases of language syntax which are either not supported or require a more complex level of translation.

Object Compatibility

In Access we can for example define a variable of type Control, when this is translated to .net it keeps the data type control, but this would become a web UI control, and this will have different properties to what we mean by a control in Access. The best solution here would be to define our own control class which wraps around the web UI class and renders unsupported properties inactive.

Form References

In Access we can get a variable which is a pointer to an object form or report which is open and then manipulate the controls directly on that form, or call public functions or sub routines on the form.

Dim frm as Form
Set frm = Forms!MyForm
Frm.ctrlname.Visible = False

This presents a challenge to convert!

The problem here is that .net is strongly typed, meaning that things have to be explicitly defined so they can be checked and compiled at an early point in time. But because in the above code we do not define Form as a particular type of Form it can’t be checked. We could even have used Forms("AFormName")

Because of the sophistication of what can be achieved in Access in respect of making these references you should plan to re-write this functionality.

Data Manipulation

The use of domain functions, recordsets, querydefs and the ways in which SQL can be executed requires either a supporting class to mimic the behaviour in Access or needs extensive re-working to fit the appropriate .net data management syntax. Having supporting classes which enable you to even continue writing in a VBA style is something we have developed.

ByRef and ByValue

Most Access developers are not explicit when passing a parameter that it is passed by value (a copy is made of the parameter) or by reference (a reference to the original variable is being passed). This is because VBA doesn’t force you to be explicit with this.

Shown below is a short testing script which you can run in Access.

Sub ChangesTheParameter(aparam As String)
   aparam = "a new value"
End Sub

Sub ChangesTheParameter2(ByRef aparam As String)
   aparam = "a new value"
End Sub

Sub DoesNotChangesTheParameter(ByVal aparam As String)
   aparam = "a new value"
End Sub

Sub ParameterTesing()
Dim aparam As String
aparam = "test example"
ChangesTheParameter aparam
Debug.Print aparam
aparam = "test example"
DoesNotChangesTheParameter aparam
Debug.Print aparam
End Sub

This gives the following result in Access:-

Below you can see the same result when executed in debug mode in .net (the parameter is passed by value so does not get changed). NOTE we are using our own special classes to support the use of Debug.Print in .net

When we paste the code into .net, it adds the missing parameter ByVal statement as shown below:-

   Sub DoesNotChangesTheParameter(ByVal aparam As String)
   aparam = "a new value"
   End Sub

So you will need to check that if you have any functions or subroutines that change the values in parameters they are passed, that you have the ByRef keyword in the parameter declaration.

There is an odd case in VBA where if you have a subroutine that receives a single parameter VBA allows the following syntax.

Sub MySub (aValue as String)
……
End Sub

Normally you would call this as follows:-

MySub myStringVariable

But you can also call it as shown below, where VBA thinks the braces ( ) mean some sort of calculation and passes the value ByVal instead of ByRef.

MySub (myStringVariable)

The above would need changed to the following either after conversion or as part of a conversion process.

MySub myStringVariable

This last example may seem like a bit of a paranoid case, but software development in Access supports a vast number of styles (which is why it has been so successful) and when converting to the much more stringent .net environment anomalies like these can occur.

Summary

We believe that it is not just possible, but essential to be able to convert very large VBA programs to .NET. Our philosophy is to offer productivity tools to assist with the conversion, and provide FREE articles discussing the technical issue.

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz

support@ascassociates.biz , Sales Enquiries

sales@ascassociates.biz