+ Reply to Thread
Results 1 to 12 of 12

VBA Word. Repeat Character in String a number of times

  1. #1
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    VBA Word. Repeat Character in String a number of times

    Hi
    Within a code I need to add a certain character into an existing string a specific number of times.

    As example:
    Let’s say I want to add a certain amount of 2’s into a string
    The number of 2’s to be added is held in a Number variable, NumberOf2sIWant
    So let’s say I need 4 x 2’s, so NumberOf2sIWant = 4

    So for example, if my existing string start is_...

    Give me some 2s please here:

    _...and the end bit is

    .I thank you kindly.

    _... then I need finally

    Give me some 2s please here: 2222 .I thank you kindly.

    _......

    I have only been able to figure out one way to do it in Excel VBA as in the code below. That seems too complicated to me, and is not much use to me as I need it in VBA Word. Ideally a code that worked in both Excel and Word VBA would be even better.

    Can anyone help?

    Thanks

    Alan

    P.s.

    I guess a workaround is to get VBA Excel code to work in Word so as to use my code.
    I will look into that now, I think it can be done. But maybe that is getting messy and unnecessarily complicated ?
    I know Excel VBA fairly well, but not much Word VBA.


    Code that works in Excel VBA ( It is the Evaluate(" ") Function that does not appear to be available in VBA Word – reasonable I suppose as it is to do with doing spreadsheet things )
    Please Login or Register  to view this content.
    _...........................
    Edit:
    Quote Originally Posted by Doc.AElstein View Post
    ...I guess a workaround is to get VBA Excel code to work in Word so as to use my code. ..
    OK, that was not too difficult, but very messy:
    Please Login or Register  to view this content.
    I would still prefer to do it simpler , if that is possible.., thanks
    Last edited by Doc.AElstein; 02-23-2017 at 01:04 PM.
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: VBA Word. Repeat Character in String a number of times

    how about a simple loop

    Please Login or Register  to view this content.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: VBA Word. Repeat Character in String a number of times

    What about ...
    Please Login or Register  to view this content.
    The String() function exists in all versions of VBA - Excel, Word, Outlook etc and QuickBasic plus GW_BASIC and if I remember correctly the ROM Basic in the original IBM PCs...

    OK - I know I didn't make the full sentence but I think that's well within your abilities...

  4. #4
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Nothing wrong with a simple loop, Matey ... :)

    Quote Originally Posted by kev_ View Post
    how about a simple loop......
    Why indeed not
    Hi kev_
    Thanks very much for the reply, appreciate it
    Your solution is one possibility.
    In fact I tend to do a lot like that as I like to keep it as clear as possible. That would do the job nicely...... Thinking about it .. adapting your code to a Function like this would do nicely what I want to get at the 2’s Bit ( “2222” )

    Please Login or Register  to view this content.
    That Function above is something like what the Excel Rept( ) Function does, like pseudo
    Str2sBit = Rept(“2”, 4)

    I was thinking there must be a VBA Function in Word .. or .. Excel and all ....or.. . like .. and like I was getting too fixed into searching for words like Repeat... ‘cos............now....

  5. #5
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    ‘cos.................now ...... What about ....

    ‘cos.................now
    Quote Originally Posted by cytop View Post
    What about .....
    Hi cytop, ( and kev_ still ) ....

    Thanks cytop, that was what I was missing. What a Tw@ I am for missing that....
    _..................Here we go:-

    Please Login or Register  to view this content.
    I still can’t find that String( , ) anywhere on the internet: all you get is the whole lot of VBA.Strings Functions stuff. That kept throwing me off.
    But with hindsight it looks familiar. Maybe I did use it 25 years ago and forgot !!

    _.......
    Well here is two code lines. The first ( along with a Dim AppExcl As Excel.Application and Set AppExcl = New Excel.Application ) is what I had in a code up until a few minutes ago.
    The second is what I have now

    Please Login or Register  to view this content.



    Thanks both, much appreciated
    Well Solved

    Alan


    _......
    Quote Originally Posted by cytop View Post
    ... I didn't make the full sentence but I think that's well within your abilities...
    .. only just I’m afraid
    Last edited by Doc.AElstein; 02-23-2017 at 08:47 PM.

  6. #6
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: VBA Word. Repeat Character in String a number of times

    Sometimes it's good to search with a little lateral thinking - and No, I didn't search for it.

    VBA is derived from Visual Basic and the documentation for VB is a lot better than for VBA. For example a Google search for 'VB6 string functions' returns this as the first result It's a comprehensive list of all the String functions in VB6 - and they are all in VBA too. There are very few functions from VB6 missing from VBA - mostly the changes are to do with userforms and controls (more restrictive i VBA) and extensions for the various components of the Office suite.

    It even includes the rarely used StrReverse() function which does exactly as it says and is probably something you'll end up using sometime soon now that you know about it

    To back up my 'rarely used' claim:
    • A google search 'strreverse site:www.excelforum.com' gives 53 results.
    • InstrRev gets 410
    • Instr gets 4900

    While we're on the subject of archaic functions and things - I'm sure you've used the MID() function to get a partial string from another string
    Please Login or Register  to view this content.
    The MID function can also be used to replace a section of the string:
    Please Login or Register  to view this content.
    The structure looks weird as the function is usually on the Right Hand Side of an assignment, but it works. The only restriction is the length of the original string will not change.
    Last edited by cytop; 02-23-2017 at 09:55 PM. Reason: Typos - it's late.

  7. #7
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: VBA Word. Repeat Character in String a number of times

    @cytop - thank you for those useful pointers. Will now have a play with some "leftie" statements to see what else can be put on the "wrong" side

  8. #8
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: VBA Word. Repeat Character in String a number of times

    Just had another read of that link and curiously it misses 2 functions added to VB6 at the same time as strReverse:

    STRCOMP() - to compare 2 strings. Can use a Binary or Text comparison to compare 'A' = 'a' (True for Text, False for Binary)
    STRCONV() - converts strings - Upper, Lower, Proper, Unicode and others.

  9. #9
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA Word. Repeat Character in String a number of times

    Quote Originally Posted by kev_ View Post
    .... Will now have a play with some "leftie" statements to see what else can be put on the "wrong" side
    Yeh, I wonder if there are any more useful secrets to be found... Let us know if you find any ....

  10. #10
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Use it to put it in .. Well Bugger me with a Barge Pole, I certainly did not know that

    Thanks cytop for the extra info. When I started a few years ago getting back into computers I did a short course on VB2010 and got a massive amount of books cheap on VB and VB6. They are getting dusty now, – I seemed to have wondered into VBA as it seemed Excel was everywhere that I looked. But I got the strange feeling that some documentation was similar and better for the VB and VB6 stuff. Nice to hear that confirmed by someone that knows
    Quote Originally Posted by cytop View Post
    .. rarely used StrReverse() function which does exactly as it says and is probably something you'll end up using sometime soon now that you know about it....
    I have a feeling I will be using that today! .... and.... I did not know “MID function can also be used to replace a section of the string....”. - I will be using that too – in about a minute - Time for me to tidy this mess up now
    Please Login or Register  to view this content.
    ..interesting... as I wrote that mess above I was thinking “if only I could use the Mid to put a bit in” .... I keep reading on googling “The MID() function is used to extract....”
    _........
    I am quite keen on these string functions, snb pointed me in that direction a few times.

    One small thing I did notice was that using the forth argument in the Replace() Function does not just start the Replace offset from the left but also returns only from that character... Like this_..
    Please Login or Register  to view this content.
    _..returns cdEfghijkl. I found that the documentation did not say that clearly, and I always got the impression that, in that example, for example, I would have got ebcdEfghijkl returned.
    _.........
    I still have not found any documentation on these gems of info you have given, cytop. ***Thanks a lot

    Alan


    *** Edit , just stumbled on a reference to StrReverse, as cyclops said, you tend to find most documantation for Visual basic ( or hidden in an access blog or documantation etc..
    As always once you do know about it, then suddenly yopu find all the documentatin - eareier.. lol
    https://wordribbon.tips.net/T011514_..._a_String.html
    http://www.excelfunctions.net/vba-st...-function.html
    http://www.excelfunctions.net/vba-st...-function.html
    Last edited by Doc.AElstein; 02-28-2017 at 05:50 AM.

  11. #11
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: VBA Word. Repeat Character in String a number of times

    You can also only replace, for example, the first 2 occurrences and make the comparison case insensitive using the Count and Compare parameters... Count is -1 by default (All) and Compare is vbCompareBinary (Case Sensitive).

  12. #12
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Better Compare Case Case Case of me getting confused I think...Case in question..? .. ???

    Quote Originally Posted by The Man View Post
    Just had another read of that link and curiously it misses 2 functions added to VB6 at the same time as strReverse:
    STRCOMP() - to compare 2 strings. Can use a Binary or Text comparison to compare 'A' = 'a' (True for Text, False for Binary)
    STRCONV() - converts strings - Upper, Lower, Proper, Unicode and others
    .
    Another gem of info_....
    _....I saw that recently in a way to do a bit of sorting snbCollectionStuffSortOf_.....
    _... and I expect probably line 80 below is somehow better than line 20.....
    Please Login or Register  to view this content.
    _.....
    Quote Originally Posted by cytop View Post
    You can also only replace, for example, the first 2 occurrences and make the comparison case insensitive using the Count and Compare parameters... Count is -1 by default (All) and Compare is vbCompareBinary (Case Sensitive).
    I often use the count in this and other functions and thingiess,
    but the compare mode sometimes confuses me – I have never really understood what Binary or Text actually mean in this context and how it therefore comes about , for example that Text is case insensitive and that Binary is case sensitive. It is a bit more involved I think, probably involves more deep Innerds understanding about how the computer is actually working, I expect. I remember shg saying something about difference in searching times for the different “linear” or “binary” searching. God knows what he was on about.. , http://www.excelforum.com/excel-prog...ml#post4156897 , ... I have no idea what that is about unfortunately , maybe he did/ does ?? or enjoys obfuscating people.. or me at least.... http://www.excelforum.com/excel-prog...ml#post4157064

    Thanks again for the extra info, looks like a couple of codes I wrote yesterday I will need to tidy up a bit..


    Edit 13th march.. Found another Thread on use of Mid() to "put stuff in"
    https://www.mrexcel.com/forum/excel-...-function.html
    Last edited by Doc.AElstein; 03-13-2017 at 06:36 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Repeat number X amount of times
    By dechesne in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-20-2016, 09:50 AM
  2. Repeat a string X number of times
    By brandonboucher in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-03-2015, 10:30 AM
  3. [SOLVED] Repeat number X times
    By mbrandt5 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-29-2015, 05:56 PM
  4. repeat number 5 times and increment
    By SnowForecast in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-17-2015, 11:42 AM
  5. [SOLVED] How to repeat the same number for 'N" number of times in excel sheet
    By tulasiram in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-12-2013, 03:06 AM
  6. [SOLVED] VBA: repeat a formula a number of times
    By dinahajjar in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-24-2013, 11:03 AM
  7. find how many times a word appears in a string at certain positions within the string
    By jordan2322 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-05-2013, 12:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1