How to get an Acronym using Excel VBA?
The purpose of the following example of Excel VBA macro is to get the acronym or an abbreviation of each of the first letter of any given words.
Function Acronym(Words As Variant) As String
Dim aWord() As String, ix As Integer
aWord = Split(Words.Value, " ")
For ix = 0 To UBound(aWord)
Acronym = Acronym & UCase(Left(aWord(ix), 1))
Next ix
End FunctionUsing Split function and space as delimiter, we create an array of word. Then using simple for … to loop, we do looping of each word and take the first letter of each word in array using Left function.
By using iteration and array like this, we will get the acronym of the word in question.
We can directly use the function in Excel formula bar with a reference to the cell that contains the collection of words we want to make an acronym from it as input.
For the details information of each function in use, Split/Ucase/Left, select each function and push F1 while in the Visual Basic Editor.
Related Entries
Auto Expanding VLOOKUP Table Array
Ok, this time I want to talk about how to create a vlookup table array that automatically expanding in accordance with the numbers of available data. As we know, the basic form of Excel VLOOKUP to look for A1 value in range B1:C15 column no 2 is like this: =VLOOKUP(A1,B1:C15,2) With parameters such as the value we looking...
Getting Cell Reference With Input Box
We can get an input from user using Input Box, but we also can use the Input Box to get the address of cell reference in Microsoft Excel. Using simple Excel VBA procedure below we can ask user to select range of cells (as copy input) and where they want it paste.Sub CopyPaste()Dim InputCells As Excel.Range Dim...
Check If Excel Workbook is Already Open or Not
In my case, I work a lot with transferring data from current Excel Workbook to another Excel Workbook, and to be able to do that, of course I need to make sure whether the destination Workbook is already open or not. The following excel vba function assigned to check whether a workbook we need is open...
Sum Unique/Distinct Values in Excel
Usually in the sequential database system, we can SUM only unique values in table column by adding all the values from SELECT DISTINCT query result only. Based on the same principle, we can also create a simple VBA function in Microsoft Excel by adding the values only available in a collection of unique values we have...
Excel Extract Cell Comments
Excel VBA/Macro example below will extract comment from every cells with comment, and put the summary in the cell selected by user using Excel input box (read more about how to get cell reference using input box here). Sub CreateCommentsSummary()Dim rgComments As Range, rgCell As Range, rgOutput As Range, iRow As Integer,...
Valuable Resources
- Microsoft Excel 2003/2007 Video Tutorials
Step-by-step video guide to mastering Charts, PivotTable, Data Analysis and Macro programming in Microsoft Excel in 5 hours.
- 101 Secrets of Microsoft Excel
Discover 101 of Excels little-known secrets that have been hiding right under your nose.
- Top Tips & Tricks to Get You Started in Microsoft Excel 2007
This is an ebook by Carol Bratt's, a Microsoft Certified Professional who has plenty of experience explaining things in simple English.
- Microsoft Office 2003/XP/2007 Training Videos
Discover an easy to use, hands-on interactive course that will teach you how to unleash the true power of Microsoft Office 2003/XP/2007.
If you found this page useful, please consider bookmark it using social media or add a link to this page.
