Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MID Formula in VBA (XP)

    I would like to be able to create 16 variables from a line of text which is comma delimited ie Var01 to Var16.

    The line of text looks like: BUnit, Acct, Dept, Descr, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

    The months represent dollar amounts for the respective month. I'm not interested in parsing the line however I get stuck at the forth comma and eventually the MID formula in code looks quite lengthy.

    Any assistance would be appreciated.
    Thanks,
    John

  2. #2
    New Lounger
    Join Date
    Apr 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MID Formula in VBA (XP)

    To do this in VBA, you can use Split to break the line at the commas, after removing the extra spaces with Replace ...

    Var = Split(Replace(LineOfText," ",""),",")

    This will give you an array ...

    Var(00) = "BUnit"
    Var(01) = "Acct"
    etc.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: MID Formula in VBA (XP)

    I do not think there is a "Split" function in VBA

    Try this function:
    <pre>Function ParseString(stext As String)
    Dim iCount As Integer
    Dim WF As WorksheetFunction
    Dim sArray() As String
    Dim x As Integer
    Dim iFind As Integer
    stext = stext & ","

    Set WF = Application.WorksheetFunction
    iCount = Len(stext) - Len(WF.Substitute(stext, ",", ""))
    ReDim sArray(1 To iCount)

    For x = 1 To iCount
    iFind = InStr(stext, ",")
    sArray(x) = Trim(Left(stext, iFind - 1))
    stext = Mid(stext, iFind + 1)
    Next
    ParseString = sArray
    Set WF = Nothing
    End Function</pre>


    Call it using something like:
    <pre> Dim sArray
    Dim stext As String
    stext = "BUnit, Acct, Dept, Descr, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec"
    sArray = ParseString(stext)</pre>


    To create an array of the parse text

    Steve

  4. #4
    New Lounger
    Join Date
    Apr 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MID Formula in VBA (XP)

    Split (and Replace) were new in VBA 6 (with Office 2K).

    As the questioner specified XP in the title, it should be just fine. I haven't checked your code; I assume it's good for anyone running 97.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: MID Formula in VBA (XP)

    Thanks for the clarification.

    Steve

Posting Permissions

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