Ms Office Tune Up

The function takes the customer name as an argument in the strName variable. The main Do loop then runs through the characters in strName one letter at a time. Inside the loop, the next character is stored in the strChar variable, and then a Select Case structure checks the value of Asc(strChar), where Asc is a function that returns the character code of the specified character. The function then checks to see whether the Asc result is between 65 and 90 (which means the character is a letter between A and Z) or between 97 and 122 (which means the character is a letter between a and z). If either one is true, the character is appended to the strTemp variable. When strTemp is five characters long (or we hit the end of the customer name), the loop exits. Finally, if strTemp isn’t null, the function uses Randomize and Rnd to generate a random three-digit number that’s added to strTemp., which is then returned as the function result. Figure 1 shows the function in used in a work­sheet.

TIP

After you generate your account numbers, you want them to remain static. Unfortunately, each time you apply the function to a new cell, Excel reruns the function on all the existing cells! This generates new account numbers for each customer, which is not what you want.The solution is to add the following statement near the top of the function (as shown in Listing 5.1):

Application.Volatile False

This tells VBA not to recalculate the function, so your account numbers remain static.

Figure 1 Account numbers cre­ated by the GenerateAccountN umberfunction in Listing 1.

Popularity: 3% [?]

Pages: 1 2