A) If you are going to call this to return the value within another sub-routine or via SQL then you should make this a
Function(). For testing, the
debug.print is ok; however, with a function you could <ctrl><g> then type
?FuntionName(AnyRequiredParameters) and have the result returned in the immediates window.
B) Line 2: not sure that you really need to explicitly declare the fixed length. You're controlling the length with your loop and appending script - unless you are going to alter the string length later, in which case, I would place that in the parameters of the
Function(zDesiredLen AS Long) and then pass that into the function for use in the loop
B2) Line 2 is in conflict with Line 12.
C) You need a holding string variable to hold your returned characters as you build the string.
D) Line 6: see (B)
E) Line 8 - 10: The
Do...Loop here isn't needed. Use the hint I provided in my first post for returning a random number between 1 and a given upper limit.
E1) If you are not going to hard code the reference string, Line12, then you could pass this into the Sub/Func so that you have Function(zDesiredLen AS Long, zRefString as String) then use the len() function to set your upper limit on the random number
E2) you should include Randomize in your code, I'd suggest placing it just before your For..Next loop
F) In
post #3 you stipulate that your first two characters should come from the reference string and then the third from "a-z"
F1) Alter your For..Next loop to only loop twice, building your first two characters from the reference string (or if you desire, you could alter this to loop n times based in the passed in parameter).
F2) Using the hint on Random number generation, set your bottom limit to 65 and the upper limit to 90 (note the hint provided give a result from 1 to upper limit... ) use the Chr() to convert to the String character and append to your current string
So your code could look something like
- Function ExampleAirCode(zDsrdLn as Long, zPick1stN as Long, _
-
zTestStr as String) As String
-
... Declared variables
-
'
-
... validate and pass in zDsrdLn, zPick1stN and zTestStr
-
'
-
... get the length of zTestStr
-
'
-
... For...Next loop based zPick1stN value to return the first N characters from the zTestStr. Use the Length of zTestStr as the upper limit on the rnd() and the Mid() to return from the zTestStr
-
'
-
... now use the rnd() with the lower/upper bounds for 65 thru 95, convert, and append to your built string.
-
... could add a loop here to append more than one!
-
'
-
... now ExampleAirCode = builtstring.
-
'
-
Exit Sub
-
... should have error trapping setup
-
End Sub
That should get you started.
Post back after you have tweeked the code.
You might also want to bookmark
> Before Posting (VBA or SQL) Code for the trouble shooting advice.
Just a to clarify this from
post#2:
- + zRefStr="1the2quick3brow4nfx5jum6psv7er8laz9ydg"
-
+ zUpper=len(zRefStr)
-
+ zPosition=CInt(Int((zUpper * Rnd()) + 1))
-
+ zTmp = zTmp & mid(zRef,zPosition,1)
zrefstr could be set to a passed in parameter or hardcoded as shown above; hence, the
Len() to set the upper limit on the random number generator. In
post#2 I showed the upper limit hardcoded to 38
171
