Excel VBA: Code Snippets

V22.0380 - Databases - Spring, 2005
Snippets of VBA code for use in Excel


"Hello, world!"

Sub HelloWorld()
Dim MessageText As String
Range("A1").Select
MessageText = "Hello, World!"
MsgBox MessageText
End Sub


Displaying information about a file:

Sub FileInformation()
Dim Wkbk As String, Pathname As String
Wkbk = ActiveWorkbook.Name
Pathname = ActiveWorkbook.Path
MsgBox "Filename: " & Wkbk & vbCr & "Path: " & Pathname & vbCr & "By Deena "
End Sub


Changing the colors of specific cells based on criteria; using a FOR loop

Sub ColorCells1()
Dim C As Variant

For Each C In Range("A1").CurrentRegion
If C.Value > 100000 Then
With C
.Font.ColorIndex = 3
.Font.Bold = True
.Font.Italic = False
End With
Else
With C
.Font.ColorIndex = 5
.Font.Bold = False
.Font.Italic = True
End With
End If
Next C
End Sub


Sample Input Box:

Dim Message, Title, Default, x
Message = "Enter a value from 1 to 20" ' Set prompt.
Title = "Input First Number" ' Set title.
Default = "5" ' Set default.
x = InputBox(Message, Title, Default)


Stars!!! (adapted from http://www.mindspring.com/~tflynn/excelvba2.html)

Sub ShowStars()
Randomize
StarWidth = 25
StarHeight = 25
For i = 1 To 10
TopPos = Rnd() * (ActiveWindow.UsableHeight - StarHeight)
LeftPos = Rnd() * (ActiveWindow.UsableWidth - StarWidth)
Set NewStar = ActiveSheet.Shapes.AddShape _
(msoShape5pointStar, LeftPos, TopPos, StarWidth, StarHeight)
NewStar.Fill.ForeColor.SchemeColor = Int(Rnd() * 56)
Application.Wait Now + TimeValue("00:00:01")
DoEvents
Next i
Application.Wait Now + TimeValue("00:00:02")

Set myShapes = Worksheets(1).Shapes
For Each shp In myShapes
If Left(shp.Name, 9) = "AutoShape" Then
shp.Delete
Application.Wait Now + TimeValue("00:00:01")
End If
Next
End Sub