Thursday, August 27, 2009


Elegance = Functionality / Complexity

Monday, August 17, 2009

Excel VBA convert character in string to position in alpahabet

If for some reason you need to convert a string of characters to their corresponding position in the alphabet, here's a dirty little sample of how to do it. The sub below takes the text in B6 and converts each character to a corresponding alphabetic position (i.e. A = 1, B = 2, etc.). It then goes so far as to add the associated integers and plops the sum in B7.

This was motivated by "attitude" equalling 100, while "hard work" only equals 98.

Have fun.

Sub mySub()

myString = UCase(Range("B6").Text)
myValue = 0

For i = 0 To Len(myString) - 1

myStringChar = Mid(myString, i + 1, 1)

For j = 1 To 26
myChar = Mid(myAlphabet, j, 1)
myVal = Replace(myStringChar, myChar, j)
If IsNumeric(myVal) Then
myValue = myValue + CInt(myVal)
Exit For
End If
Next j
Next i

Range("B7").Value = myValue

End Sub

Tuesday, August 11, 2009

NETSH, DHCP and reservation options

Creating reservations in a Windows DHCP server is not difficult and each reservation can provide unique options to the associated host. Unfortunately, creating each reservation and then modifying the options from default can be time consuming and error prone.

You can use NETSH to display, add, modify and delete DHCP scopes, options, reservations and options unique to the reservations without a GUI.

The reservedoptionvalue is poorly documented but is your key. I would suggest creating a single accurate reservation via the GUI and then using NETSH dump dhcp > filename.txt to verify the command format on your server version.

For reference, or for those that don't have a server to validate against, here are some NETSH commands and associated notes:

# Add Scope on server at

Dhcp Server add scope "Voice" "Voice DHCP"
Dhcp Server Scope set state 1

# Add IP range to the Scope on Server

Dhcp Server Scope Add iprange

# Add router at and TFTP Servers at and to the scope on server

Dhcp Server Scope set optionvalue 3 IPADDRESS ""
Dhcp Server Scope set optionvalue 150 IPADDRESS "" ""

# Add a reservation to the scope with IP and MAC 001122334455
# Add a TFTP Servers at and to the reservation
# Add a Router at to the reservation

Dhcp Server Scope Add reservedip 001122334455 "SEP001122334455" "Test" "DHCP"
Dhcp Server Scope set reservedoptionvalue 150 IPADDRESS "" ""
Dhcp Server Scope set reservedoptionvalue 3 IPADDRESS ""