Elegance

Elegance = Functionality / Complexity

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)
myAlphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
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

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 10.1.0.0 on server at 10.1.1.1

Dhcp Server 10.1.1.1 add scope 10.1.0.0 255.255.0.0 "Voice" "Voice DHCP"
Dhcp Server 10.1.1.1 Scope 10.1.0.0 set state 1

# Add IP range to the Scope 10.1.0.0 on Server 10.1.1.1

Dhcp Server 10.1.1.1 Scope 10.1.0.0 Add iprange 10.1.1.100 10.1.10.254

# Add router at 10.1.254.254 and TFTP Servers at 10.1.1.2 and 10.1.1.1 to the 10.1.0.0 scope on server 10.1.1.1

Dhcp Server 10.1.1.1 Scope 10.1.0.0 set optionvalue 3 IPADDRESS "10.1.254.254"
Dhcp Server 10.1.1.1 Scope 10.1.0.0 set optionvalue 150 IPADDRESS "10.1.1.2" "10.1.1.1"

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

Dhcp Server 10.1.1.1 Scope 10.1.0.0 Add reservedip 10.1.9.1 001122334455 "SEP001122334455" "Test" "DHCP"
Dhcp Server 10.1.1.1 Scope 10.1.0.0 set reservedoptionvalue 10.1.9.1 150 IPADDRESS "10.1.1.12" "10.180.1.11"
Dhcp Server 10.1.1.1 Scope 10.1.0.0 set reservedoptionvalue 10.1.9.1 3 IPADDRESS "10.1.254.254"