Stir fry a VBA with Excel application quickly

This is bound to bring a sense of deja-vu to you. How often have you come across a situation where your boss comes all excited about a new idea? So he says

Boss: “I have a new idea. Can you quickly come up with a prototype?”
You: “Sure” As you say this your mind is quickly assessing various tools, IDEs, languages. You are wondering whether you should go with Java or Ruby on Rails, Python-Django etc.

Then your boss drops the bombshell

Boss: “And by the way I need the Proof of Concept (PoC) yesterday!”
You are deflated as you try to pick your crumpled physical remains from the floor.

For all these situations VBA with Excel is good choice to quickly put a prototype together. If you know any other language VBscript should be a breeze. In fact this is a very useful skill to know whether you are a seasoned programmer, a marketing or sales veteran or a novice programmer. VBA is quite useful for prototyping applications with a fairly simple and straightforward GUI.

You could be building a prototype to dimension a Core Network – determining the number of SS7 links, the traffic on various links or you may want to prepare a rudimentary tax calculator. You may want to process the sales in a quarter and display it in a pretty way. For many purposes VBA fits the bill quite well particularly when you need to hit the road right way.

Here is a highly condensed version of VBscript

Variables: Declared/defined with Dim sValue. There is no explicit typing
Global Variables: Add the Dim aValue at the top.
Branching:

If cond1 then Else if cond2 Else Endif.
Select case var1 case “a” case “b” End Select

There is also the Go To
Loops:

Do {while|until} condition statements Loop
Do statementsLoop{while|until} condition

For I = 1 to 10 step 2 statement Next
For each element in group statements Next
Procedure: Sub proc (a,b) statements End Sub. Procedure called with Call proc(x,y)
Function: Function test (a,b) statements End function. Invoked with test(x,y)
Output: MsgBox “Hello world”

You could write to Excel sheet with
Cells (3, 1) = value where row 3, column 1 would be populated with value
Writing to File:
slogfile = “logfile.txt”
Set objFs = CreateObject(“scripting.FileSystemObject”)
Set objFile = objFs.CreateTextFile(slogfile)
objFile.writeLine “Total slot = ” & totalSlots
objFile.Close

With this you should be good to get started on some basic application.
Create a new Excel sheet. Click Tools->Macro->Visual basic Editor. Then click Insert->userform
You should see something like this

Assume that you want to include some VBscripts to perform some common tasks that you often do.
You could add components from the VB Toolbox. I created something like this.

When you click on the component it will take you to the code where you can write the procedure you want.
To populate a combo box you will have to add the following code for e.g.

Private Sub UserForm_Activate()
With ComboBox1
ComboBox1.AddItem “Physical Memory Properties”
ComboBox1.AddItem “Enumerate Port”
ComboBox1.AddItem “Basic Computer Information”
ComboBox1.AddItem “Inventory Information”
End With
End Sub

When the 1^st item is clicked it will call the phy_sys_prop procedure
Private Sub ComboBox1_Click()
Dim x
Select Case ComboBox1.Text
Case “Physical Memory Properties”
Call phy_mem_prop
End Select

We can have multiple forms/tabs as shown with radio-buttons, text boxes, spin buttons, list boxes etc.
To execute the code click the green > at the top

This is what the output will look like. It also populates the Excel sheet.. This code was taken from Microsoft’s Technet Script Center Repository

VBA with Excel is definitely useful to know.
The code for the form is shown below
Private Sub UserForm_Activate()
With ComboBox1
ComboBox1.AddItem “Physical Memory Properties”
ComboBox1.AddItem “Enumerate Port”
ComboBox1.AddItem “Basic Computer Information”
ComboBox1.AddItem “Inventory Information”
End With
End Sub

Private Sub phy_mem_prop()
Dim strComputer, i, objWMIService, strMemory, colItems
Dim strCapacity, objItem, installedModules, totalSlots
Dim strCapacityGB
Dim r As Range
Set r = Range(“A2”)
strComputer = InputBox(“Enter PC Name or IP:”, “PC Name”)
strMemory = “”
i = 1

Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\cimv2”)
Set colItems = objWMIService.ExecQuery(“Select * from Win32_PhysicalMemory”)
For Each objItem In colItems
strCapacity = objItem.Capacity
If strMemory <> “” Then strMemory = strMemory & vbCrLf
strMemory = strMemory & “Bank” & i & ” : ” & (objItem.Capacity / 1048576) & ” Mb”
i = i + 1
Next
installedModules = i – 1

Set colItems = objWMIService.ExecQuery(“Select * from Win32_PhysicalMemoryArray”)
For Each objItem In colItems
totalSlots = objItem.MemoryDevices
strCapacity = (objItem.MaxCapacity / 1024)
strCapacityGB = strCapacity / 1024
Next
MsgBox “Total Slots: ” & totalSlots & vbCrLf & _
“Free Slots: ” & (totalSlots – installedModules) & vbCrLf & _
vbCrLf & “Installed Modules:” & vbCrLf & strMemory & vbCrLf & vbCrLf & _
“Maximum Capacity for ” & strComputer & “: ” & strCapacityGB & ” GB”, vbOKOnly + vbInformation, “PC Memory Information”
Cells(2, 1) = “Total Slots”
Cells(2, 2) = “Free Slots”
Cells(2, 3) = “Installed Modules”
Cells(2, 4) = “Maximum Capacity for”
Cells(3, 1) = totalSlots
Cells(3, 2) = totalSlots – installedModules
Cells(3, 3) = strMemory
Cells(3, 4) = strCapacityGB
slogfile = “logfile.txt”
Set objFs = CreateObject(“scripting.FileSystemObject”)
Set objFile = objFs.CreateTextFile(slogfile)
objFile.writeLine “Total slot = ” & totalSlots & _
“Free Slots = ” & totalSlots – installedModules & _
“Installed Modules = ” & strMemory + _
“Max capacity = ” & strCapacityGB
objFile.Close
Set objFile = Nothing
Set objFs = Nothing
End Sub