• Home
  • Other Stuff
  • Contact
Black Tor

VBA: User Defined Funtions

Introduction

Excel VBA

On this page there are some basic details which are often mistaken or misunderstood. Covered below are variables, sharing routines and functions and passing variables between routines.
There are several points in regard to naming variables which constrain how they can be constructed or their names created, these are:

  1. Length. Names of variables cannot be more than 250 characters.
  2. Start. Variables must start with a letter, other characters are allowed within the variable name but not at the start.
  3. Name. Various words within Excel have been set as key words and to avoid confusion these cannot be used as a variable name. Such as sum, sheet, worksheet, etc.
  4. Spaces. Should your variable name be made of seperate words an underscore or capital letters can be used to distinguish the words, spaces cannot.

Public or Private

A each of these variable types are declared at the top of the module or form. Public variables can be shared between modules, for example defining the last used row within a sheet and then passing the row number to another routine contained within a seperate module to impose formatting on the row. A Private variable acts in a similar way however the secondary routine which intends to use the variable must be contained within the same module. There may be some confusion of the use of Global, this is an older term which can still be used but has been replaced by Public.

Variable Definitions

There are a number of variables that can be used within VBA for defining items that you use in your code. These can help to make your code run more quickly by using the correct type of variable for each item.

  • Boolean: Two possible values either True(-1) or False(0).
  • Integer: Whole numbers between -32,768 and 32,767.
  • Long: Also whole numbers ranging between -2,147,483,648 to 2,147,483,647.
  • Currency: Up to 4 decimal places from -922,337,203,685,477.5808 up to 922,337,203,685,477,5807.
  • Single: Single precision numbers a 64 bit.
  • String: It can include letters, numbers, punctuation and spaces. A string can be an almost unlimted number of characters.
  • Data: Stores dates and times as a real number.
  • Object: A reference to an object.
  • Variant: By default all variables are variants until declared otherwise.

As you explore and use VBA in greater depth you will discover that although these are the main items used it is not complete. Additionally it is possible to declare variables as a Date or Currency.

ByRef or ByVal

The abbreviation used in code stands for 'By Reverence' or 'By Value'. These definitions are used when passing a variable from one procedure to another, this can also be used for objects however the important rule to be remembered is that items passed ByRef can be altered by the sub procedure allowing manipulation of the variable or object when passed back. However ByVal items when returned to the original procedure remain the same as the value when passed. The sub procedure can manipulate the or alter the value only whilst it holds it.

Updating

Working on it!

Currently this site is under re-construction and this page amongst many is constantly being updated with information. Please revisit the site over the next few days and weeks as we add more content.