Posts Tagged 'VBA'

Access VBA – Change the Name of a Field in an Existing Table

I’ve just been importing some records from Excel to Access using VBA.

By default the names of the fields of the imported records are “F1”, “F2”, “F3” etc.

To make things a little clearer I wanted to rename these imported fields to more meaningful names.

Here’s how to do it:

CurrentDb().TableDefs(TableName).Fields("OldName").Name = "NewName"

where:
TableName is the name of the table in which we want to change the names
“OldName” is the old name of the field
“NewName” is the new name we want to use for this field

Excel VBA – PERSONAL.XLS Locked For Editing – Resolution

I’ve just been working on an Excel problem that requires one worksheet to be opened at a given time (using the Windows Task Scheduler) and a macro run.

However, when testing this I came across the “Personal.XLS locked for editing” message.

It seems that this is due to having more than one instance of Excel running at one time.

Dave Peterson at http://www.pcreview.co.uk/forums/thread-2831009.php has suggested a solution to this.

This involves marking the personal.xls file as read-only and this, indeed, seems to stop the error.

If you haven’t come across the personal.xls file before it’s a hidden workbook that opens when you start Excel. Any code in this workbook is available in all workbooks you have open in Excel.

On a Windows XP machine the file is usually located in the Excel startup folder at:

C:\Documents and Settings\[UserName]\Application Data\Microsoft\Excel\XLSTART

If you’re using Vista it’s probably in the folder at:

C:\Users\[UserName]\AppData\Roaming\Microsoft\Excel\XLSTART

where [UserName] is the user name you’re currently logged onto the machine as.

However, the file could be located elsewhere so use Search to find it if it’s not in the folders mentioned above.

Although making the file read-only solves this problem it does raise other issues if you often add code to personal.xls but is a useful workaround in my situation.

Enabling Mouse Scrolling In VBA

I’ve just had to set up a new PC and, for a few minutes there, was confused as to why the scroll wheel did not work in the VBA code window.

Then I remembered that VBA is based on the Microsoft Visual Basic 6 IDE which doesn’t include built-in support for the scroll wheel.

After a little searching I found the following article on the Microsoft site: http://office.microsoft.com/en-us/access/HA101175901033.aspx (opens in new window)

In short, to enable the mouse wheel scrolling behaviour in the VBA window, you must download a package from Microsoft at http://office.microsoft.com/search/redir.aspx?AssetID=XT101665991033&CTT=5&Origin=HA101175901033 (opens in new window) and then do the following:

  • Run the downloaded executable to extract the files
  • Add the included DLL to your machine
  • Register the DLL using RegSvr32
  • Run the included registry file to add the required elements to your machine’s registry (remembering to take a backup of the registry first of course)
  • Add the MouseWheel Fix to your VBA project using the Add-In manager

Now the scroll wheel should work in your VBA code!

Microsoft Access – Handling NULL values with the NZ() function

In Microsoft Access VBA, as in many other implementations, if we try to evaluate an expression containing a NULL value the whole expression will return NULL.

For example:

Dim varMyValue as Variant
varMyValue = NULL
MsgBox IsNull(varMyValue + 2)

This will return TRUE.

However, it is often the case that we would like this expression to “ignore” the NULL value and return the value 2.

We can achieve this using the NZ function.

The syntax of this function is as follow:

Nz(variant, [valueifnull])

Thus we could ignore the NULL value and substitute it with 0.

e.g.

Dim varMyValue as Variant
varMyValue = NULL
MsgBox NZ(varMyValue,0) + 2

Here the NULL value is replaced with the value zero and our message box displays the value 2.

We could also use the NZ function in other ways.

If we have a variable containing a string , for example, the country of a purchaser (e.g. UK, US, PL) and we use this to calculate a postage rate for goods sent to this purchase we could use the NZ function in the following way to replace a NULL value with a suitable message or calculation.

e.g.

strPostage = NZ(strCountry,0) ' if we wanted to set the postage value to zero if it is NULL

or

strPostage = NZ(strCountry,"No Country Code Given") ' to maybe use in a MsgBox if strCountry is NULL

We could also use the NZ function in an Access SQL query.


SELECT [Surname], [FirstName], [DOB], NZ([Address],"No Address Available")

Note, however, that this function is only available within Access and not within any of the rest of the Office suite. Also, other variants of SQL may not support this function.

Microsoft Access – Parse OpenArgs – Seperating Each OpenArg Value

I haven’t used the OpenArgs property in Microsoft Access for ages.

However, today I needed to pass a variable number of values from a form to another form.

In doing so I needed to seperate out each value passed in the OpenArgs property of the calling form which have been seperated using the | character (accessed using shift + \ on my UK keyboard).

So how did I do it?

Here’s the code:

Dim Hold_OpenArgValues() as String
Hold_OpenArgValues()=Split(Me.OpenArgs, "|")

We can then do something with the values returned to the array.

As an example we could write them to the Immediate window using the UBound function to decide how many items have been passed (this was going to be variable remember).

For counter = 0 to UBound(Hold_OpenArgValues)
  Debug.Print "Value " & counter, Hold_OpenArgValues(counter)
Next counter

vbNullString – Empty Cells in Excel VBA

My post on IsNull in Excel VBA has generated some hard thought!

The site Daily Dose of Excel kindly mentioned my post at http://www.dailydoseofexcel.com/archives/2008/06/26/identify-empty-cells-in-vba.

This has lead to some excellent discussion of the use of “empty” cells in Excel within either formula or VBA code.

I’ll repeat here what I posted on that site as a reference for myself and others:

[I was reminded of] some Win32 API programming in VB that I’d done years ago and I came across the following definition of vbNullString (now, I also note, repeated in the Office 2007 help file):

vbNullString – String having value 0 – Not the same as a zero-length string (“”); used for calling external procedures

As a VBA (or VB) string is only a pointer to a character array. The address in memory pointed to by this variable points to the start of the character array.

The array terminates in a 2-byte null terminator and is preceded by 4 bytes showing the length of the string in bytes.

Using “” to declare an empty string results in a character array of size 0. However, this will still contain the 4-byte length data, and the 2-byte terminator.

vbNullString still returns a pointer but one that does not point to any character array resulting in no 4-byte length field or 2-byte terminator.

An article by Microsoft (of which I have a printed copy but now can’t find the URL) on VBA optimization suggests that vbNullString would execute 50% faster than “”.

Basically, using vbNullString seems to be suitable in some case but not in others (e.g. in charts where it returns a value of 0).

I’m sure I’ll be adding more about this soon after I’ve had time to play with it some more.


Blog Stats

  • 221,277 hits

Counter

ClustrMap