.john
04-09-2014, 01:31 PM
Hi guys & girls,
Hoping you can help. I'm VERY new to VBA and am looking to do some automation tasks with Excel. I currently have a number of text files that contain certain information about a switch - port addresses/macs etc...
Now, I'm looking to parse these files and pull certain bits into seperate worksheets. I'm starting off small though, getting the little bits I think I'll need together before working on the main bit. One of my first problems is reformatting MAC addresses from the "0123.4567.8910" format to "01:23:45:67:89:10" format...
I've put this together and would like your feedback - is it efficient to reformat this the way I'm doing it? remember, I'm only playing at the moment and am a complete novice with vba...
Function reformatMAC()
Dim varMAC As String
Dim killZero As String
Dim insColon As String
varMAC = "0123.4567.8910"
killZero = Replace(varMAC, ".", "")
insColon = Left(killZero, 2) & ":" & Mid(killZero, 3, 2) & ":" & Mid(killZero, 5, 2) & ":" & Mid(killZero, 7, 2) & ":" & Mid(killZero, 9, 2) & ":" & Mid(killZero, 11, 2)
' MSG BOX for testing function results
MsgBox varMAC & vbCrLf & killZero & vbCrLf & insColon
End Function
Any ideas, improvements or comments - negative (but constructive) :) or positive are very much appreciated!
Thanks,
John
Hoping you can help. I'm VERY new to VBA and am looking to do some automation tasks with Excel. I currently have a number of text files that contain certain information about a switch - port addresses/macs etc...
Now, I'm looking to parse these files and pull certain bits into seperate worksheets. I'm starting off small though, getting the little bits I think I'll need together before working on the main bit. One of my first problems is reformatting MAC addresses from the "0123.4567.8910" format to "01:23:45:67:89:10" format...
I've put this together and would like your feedback - is it efficient to reformat this the way I'm doing it? remember, I'm only playing at the moment and am a complete novice with vba...
Function reformatMAC()
Dim varMAC As String
Dim killZero As String
Dim insColon As String
varMAC = "0123.4567.8910"
killZero = Replace(varMAC, ".", "")
insColon = Left(killZero, 2) & ":" & Mid(killZero, 3, 2) & ":" & Mid(killZero, 5, 2) & ":" & Mid(killZero, 7, 2) & ":" & Mid(killZero, 9, 2) & ":" & Mid(killZero, 11, 2)
' MSG BOX for testing function results
MsgBox varMAC & vbCrLf & killZero & vbCrLf & insColon
End Function
Any ideas, improvements or comments - negative (but constructive) :) or positive are very much appreciated!
Thanks,
John