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:

Originally Posted by Doc.AElstein

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

how about a simple loop

Please Login or Register  to view this content.

Originally Posted by kev_

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

‘cos.................now

Originally Posted by cytop

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


_......

Originally Posted by cytop

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

.. only just I’m afraid

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.

@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

Originally Posted by kev_

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

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

Originally Posted by cytop

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

Originally Posted by The Man

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.

_.....

Originally Posted by cytop

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