|
=VBA Examples=
Some nontrivial VBA Examples (untilto therebe is a separate VBA categorymoved).
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...
==[[Letter frequency]]==
<lang>
Public Sub LetterFrequency(fname)
'count number of letters in text file (ASCII-coded)
'note: we count all characters but print only the letter frequencies
Dim Freqs(255) As Long
Dim abyte As Byte
Dim ascal as Byte 'ascii code for lowercase a
Dim ascau as Byte 'ascii code for uppercase a
'try to open the file
On Error GoTo CantOpen
Open fname For Input As #1
On Error GoTo 0
'initialize
For i = 0 To 255
Freqs(i) = 0
Next i
'process file byte-per-byte
While Not EOF(1)
abyte = Asc(Input(1, #1))
Freqs(abyte) = Freqs(abyte) + 1
Wend
Close #1
'add lower and upper case together and print result
Debug.Print "Frequencies:"
ascal = Asc("a")
ascau = Asc("A")
For i = 0 To 25
Debug.Print Chr$(ascal + i), Freqs(ascal + i) + Freqs(ascau + i)
Next i
Exit Sub
CantOpen:
Debug.Print "can't find or read the file "; fname
Close
End Sub
</lang>
Output:
<pre>
LetterFrequency "d:\largetext.txt"
Frequencies:
a 24102
b 4985
c 4551
d 19127
e 61276
f 2734
g 10661
h 8243
i 21589
j 4904
k 7186
l 12026
m 7454
n 31963
o 19021
p 4960
q 37
r 21166
s 13403
t 21090
u 6117
v 8612
w 5017
x 168
y 299
z 4159
</pre>
==[[Horner's rule for polynomial evaluation]]==
Note: this function Horner gets its coefficients in a ParamArray which has no specified length. This array collect all arguments after the first one. This means you must specify x first, then the coefficients.
<lang>
Public Function Horner(x, ParamArray coeff())
Dim result As Double
Dim ncoeff As Integer
result = 0
ncoeff = UBound(coeff())
For i = ncoeff To 0 Step -1
result = (result * x) + coeff(i)
Next i
Horner = result
End Function
</lang>
Output:
<pre>
print Horner(3, -19, 7, -4, 6)
128
</pre>
==[[Floyd-Warshall algorithm]]==
|