User:Klever
| My Favorite Languages | |
| Language | Proficiency |
| Visual Basic | Active (in VB for Applications) |
| BASIC | Somewhat Rusty |
| Fortran | Stuck in Fortran 77, WATFOR, WATFIV etc. |
| Pascal | Rusty |
| PHP | Learning |
| MATLAB | Learning |
| JavaScript | Semi-Active |
| SQL | Semi-Active |
| APL | is way back |
VBA Examples
Some nontrivial VBA Examples (until there is a separate VBA category).
In MS Office program (Word, Excel, Access...): open the Visual Basic window. Paste the code in a module. Execute it by typing a suitable command in the Immediate Window. Output will be directed to the Immediate Window unless stated otherwise...
Reverse a string
Non-recursive version
<lang> Public Function Reverse(aString as String) as String ' returns the reversed string dim L as integer 'length of string dim newString as string
newString = "" L = len(aString) for i = L to 1 step -1
newString = newString & mid$(aString, i, 1)
next Reverse = newString End Function </lang>
Recursive version
<lang> Public Function RReverse(aString As String) As String 'returns the reversed string 'do it recursively: cut the sring in two, reverse these fragments and put them back together in reverse order Dim L As Integer 'length of string Dim M As Integer 'cut point
L = Len(aString) If L <= 1 Then 'no need to reverse
RReverse = aString
Else
M = Int(L / 2) RReverse = RReverse(Right$(aString, L - M)) & RReverse(Left$(aString, M))
End If End Function </lang>
Example dialogue
print Reverse("Public Function Reverse(aString As String) As String")
gnirtS sA )gnirtS sA gnirtSa(esreveR noitcnuF cilbuP
print RReverse("Sunday Monday Tuesday Wednesday Thursday Friday Saturday Love")
evoL yadrutaS yadirF yadsruhT yadsendeW yadseuT yadnoM yadnuS
print RReverse(Reverse("I know what you did last summer"))
I know what you did last summer
Ordered words
<lang> Public Sub orderedwords(fname As String)
' find ordered words in dict file that have the longest word length ' fname is the name of the input file ' the words are printed in the immediate window ' this subroutine uses boolean function IsOrdered
Dim word As String 'word to be tested Dim l As Integer 'length of word Dim wordlength As Integer 'current longest word length Dim orderedword() As String 'dynamic array holding the ordered words with the current longest word length Dim wordsfound As Integer 'length of the array orderedword()
On Error GoTo NotFound 'catch incorrect/missing file name Open fname For Input As #1 On Error GoTo 0
'initialize wordsfound = 0 wordlength = 0
'process file line per line While Not EOF(1)
Line Input #1, word
If IsOrdered(word) Then 'found one, is it equal to or longer than current word length?
l = Len(word)
If l >= wordlength Then 'yes, so add to list or start a new list
If l > wordlength Then 'it's longer, we must start a new list
wordsfound = 1
wordlength = l
Else 'equal length, increase the list size
wordsfound = wordsfound + 1
End If
'add the word to the list
ReDim Preserve orderedword(wordsfound)
orderedword(wordsfound) = word
End If
End If
Wend Close #1
'print the list Debug.Print "Found"; wordsfound; "ordered words of length"; wordlength For i = 1 To wordsfound
Debug.Print orderedword(i)
Next Exit Sub
NotFound:
debug.print "Error: Cannot find or open file """ & fname & """!"
End Sub
Public Function IsOrdered(someWord As String) As Boolean 'true if letters in word are in ascending (ascii) sequence
Dim l As Integer 'length of someWord Dim wordLcase As String 'the word in lower case Dim ascStart As Integer 'ascii code of first char Dim asc2 As Integer 'ascii code of next char
wordLcase = LCase(someWord) 'convert to lower case l = Len(someWord) IsOrdered = True If l > 0 Then 'this skips empty string - it is considered ordered...
ascStart = Asc(Left$(wordLcase, 1))
For i = 2 To l
asc2 = Asc(Mid$(wordLcase, i, 1))
If asc2 < ascStart Then 'failure!
IsOrdered = False
Exit Function
End If
ascStart = asc2
Next i
End If End Function </lang>
Results:
OrderedWords("unixdict.txt")
Found 16 ordered words of length 6
abbott
accent
accept
access
accost
almost
bellow
billow
biopsy
chilly
choosy
choppy
effort
floppy
glossy
knotty
Hailstone sequence
<lang> Public Function Hailstone(aNumber As Long, Optional Printit As Boolean = False) As Long 'return length of Hailstone sequence for aNumber 'if optional argument Printit is true, print the sequence in the Immediate window Dim nSteps As Long Const NumbersPerLine = 10 'when printing, start a new line after this much numbers
nSteps = 1 If Printit Then Debug.Print aNumber, While aNumber <> 1
If aNumber Mod 2 = 0 Then aNumber = aNumber / 2 Else aNumber = 3 * aNumber + 1 nSteps = nSteps + 1 If Printit Then Debug.Print aNumber, If Printit And (nSteps Mod NumbersPerLine = 0) Then Debug.Print
Wend If Printit Then Debug.Print "(Length:"; nSteps; ")" Hailstone = nSteps End Function
Public Sub HailstoneTest() Dim theNumber As Long Dim theSequenceLength As Long Dim SeqLength As Long
'find and print the Hailstone sequence for 27 (note: the whole sequence, not just the first four and last four items!) Debug.Print "Hailstone sequence for 27:" theNumber = Hailstone(27, True)
'find the longest Hailstone sequence for numbers less than 100000. theSequenceLength = 0 For i = 2 To 99999
SeqLength = Hailstone(CLng(i)) If SeqLength > theSequenceLength Then theNumber = i theSequenceLength = SeqLength End If
Next i Debug.Print theNumber; "has the longest sequence ("; theSequenceLength; ")." End Sub </lang>
Output:
HailstoneTest Hailstone sequence for 27: 27 82 41 124 62 31 94 47 142 71 214 107 322 161 484 242 121 364 182 91 274 137 412 206 103 310 155 466 233 700 350 175 526 263 790 395 1186 593 1780 890 445 1336 668 334 167 502 251 754 377 1132 566 283 850 425 1276 638 319 958 479 1438 719 2158 1079 3238 1619 4858 2429 7288 3644 1822 911 2734 1367 4102 2051 6154 3077 9232 4616 2308 1154 577 1732 866 433 1300 650 325 976 488 244 122 61 184 92 46 23 70 35 106 53 160 80 40 20 10 5 16 8 4 2 1 (Length: 112 ) 77031 has the longest sequence ( 351 ).