Consulting

Results 1 to 5 of 5

Thread: Is this efficient?

  1. #1
    VBAX Newbie
    Joined
    Apr 2014
    Posts
    3
    Location

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

    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

  2. #2
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    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
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  3. #3
    VBAX Newbie
    Joined
    Apr 2014
    Posts
    3
    Location
    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

  4. #4
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    you are welcome.

    pls mark the thread as solved from "thread tools" dropdown (above your first message, on the right) if you are sorted.
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  5. #5
    VBAX Newbie
    Joined
    Apr 2014
    Posts
    3
    Location
    All sorted! Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •