#visual basic for applications

Solvecraft, LLC specializes in getting the most out of Microsoft Excel, Google Sheets, and related processes for your team. We develop advanced formulas, templates, data organization & cleaning plans, plus macros/VBA to automate processes and improve your workflow.

Solvecraft also supports organizations with process documentation & improvement, Microsoft Word, task management, and form/survey testing.

Solvecraft, LLC specializes in getting the most out of Microsoft Excel, Google Sheets, and related processes for your team. We develop advanced formulas, templates, data organization & cleaning plans, plus macros/VBA to automate processes and improve your workflow.

Solvecraft also supports organizations with process documentation & improvement, Microsoft Word, task management, and form/survey testing.

Advanced Excel Techniques: Macros, VBA, and Automation

Играем в… MS Excel

Пользователь посвященного разработке компьютерных игр веб-сайта Gamasutra написал программное обеспечение для трехмерной игры по мотивам популярного шутера DOOM. (more…)

New Post has been published on http://analystcave.com/vba-like-operator/

VBA Like operator - using wildcards in conditional statements

The VBA Like operator is something so useful I am often surprised how rarely it is used in Excel and Access VBA. I often tend to see the Like operator as the last resort before using Regular Expressions in VBA. It replaces greatly the VBA InStr function when needing to check if a certain substring is present within a certain string. So let’s get right to it!

Similar posts worth reading:

Manipulating strings in VBA

Using Regex in VBA

VBA Like operator

The VBA Like operator is a boolean operator that return True if a string is matched against a certain string pattern.

"Dog and Cat" Like "*Dog*" 'Result: True "Dog and Cat" Like "*Cow*" 'Result: False

VBA Like allows you also to use the following wildcards to replace certain strings or characters:

* – matches any number of characters

? – matches any 1 character

[ ] – matches any 1 character specified between the brackets

- – matches any range of characters e.g. [a-z] matches any non-capital 1 letter of the alphabet

# – matches any digit character

And that is basically it. Easy right? Let’s see some examples…

VBA Like operator examples

Let us now look at a couple of examples that greatly show all the possibilities of the VBA Like operator:

Matching against letters

If "Animal" Like "[A-Z]*" then Debug.Print "Match: String starting with Capital letter!" End If

Matching against numbers

If "My house number is 22" Like "*##" then Debug.Print "Match: String contains a 2 digit number" End If

Matching a phone number with either dashes or dots

"123-345-678" Like "###[-.]###[-.]###"

Matching a certain string within another string

"fewfwfewfwefdogfefweff" Like "*dog*"

As you can see in the first row we are using the Like Operator similarly as we use other compare operators (=, >, <, <>). The Like operator let’s you validate if a string on the left side of the operator matches the Like expression on the right.

Conculsions on the VBA Like operator

My 2 main takeaways are:

Use the VBA Like instead of the InStr function to check if a string contains a substring

Consider using the VBA Like before resorting to VBA Regular Expressions

Be sure to master string manipulation

Related Post

VBA: GetAttr function

Purchase History

Optimize Excel formulas with the Excel Optimizer!

VBA: Day function

Like VBA? You will love HTA! (HTA example using VB...

New Post has been published on http://analystcave.com/word-translator-addin-excel-translator-addin/

Word Translator AddIn and Excel Translator AddIn

Working in multinational organisations many of us struggle with ad-hoc translations of Word documents or Excel spreadsheets. Translations are a boring task. Usually what most of us have mastered is a simple process of copying texts from documents to Google Translate and back. If you have my VBA Time Saver AddIn you probably already have a neat VBA function for translations which you can add as a shortcut. However, on a daily basis if you often switch between languages you might want an AddIn instead. Thankfully I am introducing the Word Translator AddIn and the Excel Translator AddIn!

Word Translator AddIn: Translate text in Word

The Word Translator AddIn appears as an additional Ribbon tab

The Word Translator AddIn is very simple to use. First set the from and to languages. Now all you need to do is select any text and hit the Translate button (or CTRL+SHIFT+T shortcut)!

The AddIn is provided with a list of supported languages. Making it easy to simply select the language you want to translate from and to in Word. Hitting the translate button with result in the text being translated and replaced, however the formatting might change – so watch out!

The Undo functionality does not work when running a VBA macro in Word. A translated text can’t be undone. Undo will however work properly in other cases of course

Install Word Translator AddIn

Download the Word Translator AddIn

Download the Word Translator AddIn from here:

Word Translator AddIn0 downloads

Copy the AddIn to your Word Startup folder

Copy the dotm template document to your Word Startup folder. In most Windows systems it should be similar to:

C:\Users\[user.name]\AppData\Roaming\Microsoft\Word\STARTUP

Remember to replace

[user.name]

with your username!

Excel Translator AddIn: Translate text in Excel

The Excel Translator AddIn appears as an additional Ribbon tab

The Excel Translator AddIn is similarly simple to use. First set the from and to languages. Now all you need to do is select any amount of Excel ranges and hit the Translate button (or CTRL+SHIFT+T shortcut)!

The Undo functionality does not work when running a VBA macro in Excel. A translated text can’t be undone. Undo will however work properly in other cases of course

Install Excel Translator AddIn

Download the Excel Translator AddIn

Download the Excel Translator AddIn from here:

Excel Translator AddIn0 downloads

Copy the AddIn to your Microsoft AddIns folder

Copy the xlam AddIn Workbook to your Microsoft AddIns folder. In most Windows systems it should be similar to:

C:\Users\[user.name]\AppData\Roaming\Microsoft\AddIns Remember to replace [user.name]

with your username!

How Word and Excel translation works

The Word and Excel Translator AddIns are proudly powered by the Google Translate engine. If you want to learn more read my post here. Do read more on how to use Google Translate responsibly.

Summary

Translations in Word and Excel can be a dull task. With the use of the above AddIns I was however able to translate my documents in 50% less time, giving me more time to check the quality of the translations and make minor language fixes.

Issues

Don’t post issues/error as comments below! Please post them on this dedicated forum.

The AddIns are powered by Google Translate functionality and require a working direct Internet Connection. If you get errors, most likely you are working from behind a proxy server and need to connect directly to the Internet.

Related Post

VBA ActiveWorkbook vs ThisWorkbook

Word VBA Tutorial

VBA: LTrim function

VBA String Functions

Data Analysis Excel Tools