VBA: Scramble the letters within a word

**BigBas** · 04-16-2008, 09:58 AM

So, I'm drawing a blank this morning! I know I have done this before, but I can't remember how, nor can I find the file.

I would like an event to scramble the letters within a word. The word can have any number of letters. I appreciate any help.

**dominicb** · 04-16-2008, 10:58 AM

Good afternoon BigBas

There are probably a number of ways to attack this. I just generated a random number between 1 and the number of letters in the word. If that number was already picked, do it again. You then have all the numbers out of sequence, then just use the mid statement to string the word back together - scambled.

This is the code I came up with. Just stick your word in A1 and the scrambled word is returned in B1 :

Please Login or Register  to view this content.

HTH

DominicB

**BigBas** · 04-16-2008, 02:41 PM

Dominic:

Thanks for the code. Unfortunately, it doesn't work. I don't get an error or anything, nothing happens. Any ideas?

**dominicb** · 04-16-2008, 05:10 PM

Hi BigBas

Originally Posted by BigBas

Thanks for the code. Unfortunately, it doesn't work. I don't get an error or anything, nothing happens. Any ideas?

Works fine for me. Have a look at the attached workbook.

HTH

DominicB

**BigBas** · 04-17-2008, 09:23 AM

Well, I'll be darned. It does work. For some reason, the destination column in my test file was hidden, so I looked like a dolt. Fortunately it does work.

In case anyone else ever needs the code, I did make one SMALL change to clear the contents of the destination cell before re-scrambling. That small change was necessary to prevent the scrambled word from just adding on to the previous scrambled word. Final code:

Please Login or Register  to view this content.

**geomo** · 01-17-2013, 08:30 AM

Hi guys, anyone have vba code scrambles or VBAsafe..??? I needed it to descript my VBA code.

Thank you

**arlu1201** · 01-17-2013, 09:29 AM

geomo,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

**Dragonphantom** · 01-21-2016, 10:23 AM

I know I'm late to the game here, but I figured if anyone else came across this such as myself and still couldn't come up with the code needed to do the job I thought I would post what I have found and how I made it better. First off here was the cleanest code I have found that did the scrambling of a word: http://www.bettersolutions.com/excel...B620911611.htm Thank You Very Much FYI from here I was still perturbed that I could run the code and it could still give me the same output as input ie. short words 2-4 letters in length were most common. So I expounded upon this code and wanted to share it for others to use as needed.

Please Login or Register  to view this content.

Also I didn't like the spacing of the letters that excel has and with no other way to offset spacing I created this:

Please Login or Register  to view this content.

To use the AddSpaces() function in the Scramble() function just change the last line by commenting the Scramble = scrambleWord and uncommenting Scramble = AddSpaces(scrambleWord). Commenting is using the ' in front of text lines.

If this is your first time using functions ensure you go to Excel Options-Customize Ribbon-ensure Developer Main Tab is ticked
Then you can click on the developer tab hit the Visual Basic button and copy and paste the code above in the Module1
From there lets assume your word list is in column A1:A10
You can use the function in any other column but for trial and error sake just try it in column B1:B10 =Scramble(A1) and copy down

Hope this helps someone else!