Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Mid() statement

  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Mid() statement

    I'm a newby wallowing in the VBA mire. Trying to some in-cell editing in Excel97. According to VBA help (edited), the Mid() statement is:

    Mid(stringvar, start[, length]) = string
    stringvar Required. Name of string variable to modify.
    start Required; Character position in stringvar where the replacement of text begins.
    length Optional; Number of characters to replace.
    string Required. String expression that replaces part of stringvar.


    When:

    strCellCont = "THE CLOWN"
    Mid(strCellCont, 1, 9) = "BOZO"

    strCellCont returns "BOZOCLOWN"; apparently [length] specifies the number of characters to use from string. Since I thought I was replacing 9 characters I was expecting it to return "BOZO".

    VBA Replace() doesn't seem to permit selection of the position within the string. If Mid("yyy",n,n) = "x" isn't the way to replace one substring with a shorter substring, how do I accomplish this?
    -John ... I float in liquid gardens
    UTC -7ąDS

  2. #2
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid() statement

    I assume you are trying to replace a string with a shorter string within another string?

    So, from one newbie to another, does this offer any clues:
    <pre>Sub insrt()
    Dim a, b, c As String
    Dim x, y, z As Long
    a = "I saw THE CLOWN today"
    b = "THE CLOWN"
    c = "BOZO"
    x = InStr(1, a, [img]/forums/images/smilies/cool.gif[/img]
    y = Len(a)
    z = Len([img]/forums/images/smilies/cool.gif[/img]
    a = Left(a, (x - 1)) + c + Right(a, (y - (x + z - 1)))
    End Sub
    </pre>

    ?
    (I'm quite sure there is probably a 10-character bit of code that would replace that long winded method.)

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid() statement

    Leif,

    A caution in yout "Dim" statement- you might not be declaring what you think!

    "x, y, z As Long" is ONLY declaring "z" as long; x and y are being declared as variant.

    The statement:
    x = "A"
    will execute OK

    whereas the statement
    z = "A"
    will give a runtime error.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid() statement

    I KNEW I should not have bothered to put them in in the first place <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.
    But the good thing is I learnt yet something else today - thanks!

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid() statement

    The length in the Mid function appeares to be the maximum length that will be replaced. If the replacement string is less than that length, then the length of the replacement string is the length that will be used. Depending on what you are trying to do, then one of the two methods below might work for you.

    <pre> strCellCont = "THE CLOWN"
    Mid(strCellCont, 1, 9) = "BOZO" & Space(9)
    strCellCont = "THE CLOWN"
    strCellCont = "BOZO" & Right(strCellCont, Len(strCellCont) - 9)
    </pre>

    Legare Coleman

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Mid() statement

    Hi John,
    You could combine the Replace and Mid functions to get:
    strcellcont = Replace(strcellcont, Mid(strcellcont, 1, 9), "BOZO")
    if that helps?
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Mid() statement

    I have Leif's and Legare's approach working, but I'd like to understand yours.

    strCellCont = Replace(strCellCont, Mid(strCellCont, 1, 9), "BOZO")
    gives me a "Sub or Function not defined" error. I was wondering if you are intending the Replace and Mid VBA Methods, or Application.WorkSheetFunction.<Replace and Mid functions>, since the argument syntaxes don't align with what I understand from the Help file ("understanding" which may be flawed).
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Mid() statement

    They are the VBA methods. I used them as follows:
    Sub testreplace()
    Dim strcellcont As String
    strcellcont = "THE CLOWN"
    strcellcont = Replace(strcellcont, Mid(strcellcont, 1, 9), "BOZO")
    MsgBox strcellcont
    End Sub
    without error (messagebox displayed "BOZO" as expected) in Excel 2000 - it is possible that the syntax may have changed since '97 I guess.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Mid() statement

    Thank you for your help. Dropped your code verbatim into a new sub in XL97, it didn't run. Bother!
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Mid() statement

    John,
    If you highlight Replace and press f1, does it have an entry for that function?
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Mid() statement

    Yes:

    expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte)

    expression Required. An expression that returns a Range object.
    What Required String. The string to search for.
    Replacement Required String. The replacement string.
    LookAt Optional Variant. Can be one of the following XlLookAt constants: xlWhole or xlPart.
    SearchOrder Optional Variant. Can be one of the following XlSearchOrder constants: xlByRows or xlByColumns.
    MatchCase Optional Variant. True to make the search case sensitive.
    MatchByte Optional Variant. Used only in Far East versions of Microsoft Excel.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Mid() statement

    Ahh, OK. That's the Range.Replace method rather than the Replace function that's a member of VBA.Strings, which possibly doesn't exist in Excel 97. I'll do some digging and see if I can confirm whether or not it did exist.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid() statement

    I'll confirm that.

    The replace function is new with Office 2000 and VBA 6. And long overdue too.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Mid() statement

    Bother, again. But thank you for your help and research. Maybe now I have justification to upgrade to O2k.
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid() statement

    You could have your own replace function in an add-in in the startup folder, which would act just the same as the VBA replace in Office 2000:

    <pre>Option Explicit

    Function Replace(Source As String, Searchfor As String, ReplaceBy As String)

    Dim lngPos As String

    lngPos = InStr(LCase$(Source), LCase$(Searchfor))

    If lngPos = 0 Then
    Replace = Source
    Else
    Replace = Left(Source, lngPos - 1) + ReplaceBy + Mid$(Source, lngPos + Len(Searchfor))
    End If
    End Function

    Sub a()
    Debug.Print Replace("I saw THE Clown today", "the clown", "Bozo")
    Debug.Print Replace("I saw THE Clown today", "I", "Bozo")
    Debug.Print Replace("I saw THE Clown today", "today", "yesterday")
    Debug.Print Replace("I saw THE Clown today", "Not Found", "yesterday")
    End Sub
    </pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Page 1 of 2 12 LastLast

Posting Permissions

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