PDA

View Full Version : [SOLVED] Is this efficient?



.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

mancubus
04-09-2014, 01:49 PM
welcome to VBAX.

"google is your best friend."
:)



Function reformatMAC(varMAC As String) As String
reformatMAC = Format(Replace(varMAC, ".", ""), "00\:00\:00\:00\:00\:00")
End Function


Sub test()
Dim varMAC As String

varMAC = "0123.4567.8910"
MsgBox reformatMAC(varMAC)
End Sub

.john
04-09-2014, 01:59 PM
mancubus,

Thanks! I hope to make the most of these forums and hopefully be able to contribute my own useful tidbits here and there in time.

I didn't want to use google for my first attempt though :) I wanted to see how far I could get with just the IDE, but you're very much right!

The method you've shown me is a lot more efficient than my crazy code lol

Thanks again!

John

mancubus
04-09-2014, 02:43 PM
you are welcome.

pls mark the thread as solved from "thread tools" dropdown (above your first message, on the right) if you are sorted.

.john
04-09-2014, 03:05 PM
All sorted! Thanks!