Excel Word Search Generator Explained
Vložit
- čas přidán 21. 08. 2024
- Get Word Search Pro for Excel to create amazing KDP puzzle books here: excelmacrofun....
In this video we see how to create a simple word search puzzle in Excel using VBA macros. The approach consists of adding each letter of the selected word to adjacent cells, after randomly setting the word direction and location within the grid. As an example, we create a word search puzzle for sports.
You can read the original post in the blog (Excel Macro Fun) and download the Excel Word Search Generator here: excelmacrofun....
You can download the exact same VBA code used in this tutorial from the following link: drive.google.c...
For more Excel VBA fun applications and games visit Excel Macro Fun: excelmacrofun....
And for other Excel VBA learning resources and applications visit any of the other blogs of the Excel Macro Mania saga:
Excel Macro Class (excelmacroclas...)
Excel Macro Business (excelmacrobusi...)
Excel Macro Sports (excelmacrospor...)
Soundtrack: Track12 (dmusic studio)
Link: soundcloud.com...
This is great - I was messing around with this myself and noticed that all the puzzles i was generating were only going diagonally and nothing across or up and down...ended up changing the line 'Loop Until RowIncr 0 AND ColIncr 0' to use an OR instead of an AND and it works great
Yes, good catch, that's the small mistakes of "live" coding 🙂 But the code is correct in the file available for download in the blog post: excelmacrofun.blogspot.com/2018/12/excel-word-search.html
THANK YOU, you are the best! Help me with my students
When checking booleans you can use the variable itself as the test; instead of using "If CanAddWord = True Then", you can just use "If CanAddWord Then". Anyways, nice work!
Sure thing! Trying to write as clear/simple code as possible for beginners to follow though. Thanks for sharing!
Excellent video! This is my first time ever attempting to use a macro and your video is flawless in my opinion. Im wondering how I can take these steps and use code to create a second word search. So if I create puzzle 1 with 20 words but have 20 additional words to create 2nd puzzles how is best approach to do this? Can I just create another loop within coding or add a second module and copy original code referencing the new words? Any suggestions would be helpful. Thanks so much 🙏
You are brave! This is not an easy one for the first time. If you want to have the 2nd puzzle in another worksheet, you can indeed loop and reuse the same code, it just depends where do you have the list of words, I would then put those in yet another worksheet, and reference that sheet only when reading the words: Sheet("mywords").Range("A1:A20") - do not use CurrentRegion if you have them contiguous. The rest of the code applies to the active sheet, so you just need to select each sheet where the puzzle should go, something like this:
Sheet("Puzzle1").Activate and then run GetWords, which is creating the puzzle in the activesheet.
You can also just say Worksheets.Add and create the puzzle in a newly added sheet.
In such case, you don't really need to add any other module. Modules are used to organize the code and set boundaries - public vs private macros, etc. Note that the code refers to a specific range in the worksheet, if you want to add the second puzzle in another range in the same worksheet, you will need to update values to get RowStart and ColStart, for example, and some other.
Thanks for your help. I’ll work at this ♥️. Have been watching your other videos on sudoku and will be pulling your code on formatting the cells to see if I can make that work also. I’m totally hooked on this now! Great detailed videos on your channel 👍👍
Good video keep up bro
very good job. is there a way to intersect two or more word to find. have you tried incorporating this logic? i have made a similar one earlier but failed at creating intersecting words.
I can think of several ways to do that, but I didn't do it yet myself. I may do it in future and create a better wordsearch generator. The way I would do it is checking if the word to be added has letters in common with the previous word, and then place to word to use that position if it fits the board. For that you need to keep in memory the direction of the previous word to have the opposite or perpendicular direction for the next word. There are a probably a few more things to consider but that should do. Give it a try! But I will keep it in my list for future.
@@ExcelMacroMania that's a good approach to start with. I may have to do the entire thing from scratch. Good luck to you.
Thank you for the video. Can it be used for number search?
Yes, you just add the series of numbers instead of words in the second sheet (Themes), and update the procedure FillUpLetters to add random numbers instead of letters in the remaining cells. In that procedure, letters are added with ChrW from a random numeral. Good luck!
Will this work in other spreadsheet programs, such as OpenOffice, or just Microsoft Excel?
No, is not possible. OpenOffice does not support VBA. On the other hand, you could do it in Google Sheets but using apps script instead of VBA. This other video explains the similarities between the two programming languages: czcams.com/video/qnAboKJafg8/video.htmlsi=JvIauUG_-jdqItgf
@@ExcelMacroMania Thank you!
Thanks for the tutorial. I was able to recreate the VBA, but keep getting an error in sub check add word( at line 46 and 47). Only get 2 or 3 words into the puzzle. I appreciate your help
Lines 46 and 47 in the simplified version available for download via the link in the description belong to the sub AddWord, but just before that it calls CheckAddWord,.. so not sure where you really get the error. But if it's 46/47, that checks the boolean CanAddWord,.. so maybe, you forgot to declare it at module level. You can also check the full version, although is a bit old... published in 2017 or 2018I think, it's here: excelmacrofun.blogspot.com/2018/12/excel-word-search.html
@@ExcelMacroMania thankful
@@ExcelMacroMania Thank you so much. I will give it a try. Really appreciate your help.
Thank you, very helpful, Can it be used for other languages like Hebrew, Greek, Russian, Ukrainian... ???
I am pretty sure it's possible, but depending on the language package. You just need to enter the words for the given language in the "themes"" sheet, and then find the right numbers for CHR that below to the letters in that alphabet and change it in the loop that adds the random letters to empty cells at the end. If you cannot find those CHRs, you can add the letters of that alphabet to other sheet and get random letters from there.
@@ExcelMacroMania Thank you very much for the explanation
Can you give me the numbers for CHR for Russian letters
Because I didn't find her
@@taoufikbezzaz786 Cannot tell, never worked with other non-latin languages. But go to insert symbol in Excel and find the character code there (ASCII) for those letters in your language package. However, not sure if you can use other with CHR after all. What you can do, is add each letter to a different cell, and get the value from that cell instead of using CHR.
@@ExcelMacroMania yes I understand, thank you for the help and explanation!!
How do I update the code so that the solutions only go in certain directions? Say I only want my solutions to go horizontally and vertically, or diagonal forward only. Can I do that?
That's specifically explained in the video from minute 2:45. I replied to a similar question a couple of weeks ago by Entérate here in the comments.
Can I determine the directions that I want the words to go. ( forward, backwards, diagonal) ??
Sure thing. In the procedure "AddWords" there is a section that says "Determine word direction". That assigns 2 random numbers between -1 and +1 (including 0) to the row and column increments when adding each word in the theme. That sets the direction of words being added (For example, if row increment is +1 and column increment is 0, the direction is downwards). You can modify that part of the code to determine the exact direction. Read the post and download the file and code here: excelmacrofun.blogspot.com/2018/12/excel-word-search.html
It is explained in the video from minute 2:45 !!
hi Marco can use this file you create for commercial use
You can use my macros for whatever you want. But it would be nice if you reference my channel and blog in your commercial application 🙂