Is this efficient?

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...

Code:

---

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

welcome to VBAX.

"google is your best friend."
:)

Code:

---

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

---

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

you are welcome.

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

All sorted! Thanks!