Excel add-ins allow you to use some new features and properties that are not inherent in the default program.
Add-on extension
Files containing such add-ons have the xla extension in versions prior to 2007 or xlam, starting with it, respectively. You can save your own files in this format, which will be used as Excel add-ins. To do this, when saving a book, select “File Type” xlam in the “Save” dialog box.
Types of add-ons
Excel add-ins can be divided into three types:
Installation
A standard set is received with the Excel program. Additional add-ons can be obtained from the Microsoft Office Products site. Before downloading to spreadsheets, these extension applications must be installed. They can be installed in the Library directory (folder) or one of its attachments located on the MS Office installation path or in the user folder \ Application Data \ Microsoft \ AddIns.
In addition, Excel add-ins can be installed in other ways defined by the Administrator.
Loading and unloading
Downloading is done after the add-in is installed. After completing this operation, all the features provided by this set of macros become available in spreadsheets. They are added to the corresponding menus along with the commands that are embedded in the code for this Excel add-in.
Too many macros loaded can slow down computer performance due to memory overload. Therefore, those add-ons that are not used or are rarely used must be unloaded. Unloading removes commands and the ability to run a set of macros in spreadsheets, but does not uninstall the add-in itself along the installation path, so if necessary it can be downloaded again. After unloading, the availability of the macro set remains until Excel is restarted.
Standard add-ins coming with spreadsheets
These macro sets include:
- An “analysis package” with which Excel is able to perform statistical, engineering or financial functions, some of which are available through standard formulas, but providing a different output format.
- A “VBA analysis package” that allows programmers to publish the features provided by the previous add-in.
- "Summation Wizard." This add-in is "Amount" in Excel, which automatically sums up the values that are defined in the condition. Since 2010 version is not supported.
- The “Euro conversion” invokes the EUROCONVERT function, which translates the entered amounts into Euros.
- “Internet Assistant” - enables programmers to post Microsoft spreadsheet data on the global network using the syntax of the add-in in question.
- "Substitution Wizard." This macro allows you to create a formula with which you can search for data in a list by a different value from this list. Since 2010 version is not supported.
- “Search for a solution” - implements what is given in its name, according to scripts, by which we understand the input values that are substituted in Excel. Decisions are selected "what-if", analyzing cells of search and restrictions.
VBA-Excel Add-in
As after loading any other set of macros, new commands or tabs will appear on the ribbon or in the spreadsheet menu. Using this add-in, Excel adds new functions and commands, which is typical for other similar macros, but, in addition, the use of this mini-application will reduce the time spent on performing the same actions, automating this type of work. This add-in cannot be called just a macro, it is a complex of the latter, which is called a set. The program includes more than 25 macros and 80 functions, among which there are such as DATAISSTROKI, SURNAME and others.
Installation is carried out by including in the parameters of the macros, as well as by ticking the checkbox next to the item "Trust access to the object model of VBA projects." Downloading is done in the same way as other add-ons. During the download, a registration window will appear, which can be filled with any data, and it is better to note "I am an Excel guru, I am not interested."
Plex
Excel Plex Add-in allows you to enter complex formulas. By entering it once, this form can be saved as a template and used in the future. The structure already contains more than 50 built-in formulas and various functions.
This add-in has a form for entering time in a given cell.
In addition, this set of macros allows you to backup books with the addition of the date and time of the backup.
Also, using this add-in, you can get the rate of one of 17 currencies taking into account the refinancing rate for any past date. Data is taken automatically from the Central Banks of Russia, Ukraine or Belarus.
The add-in allows you to generate random numbers in a selected range of cells, which can be either fractional or integer, repeating and non-repeating, in a numerical format or in a date and time format.
Using this set of macros, you can create a Dynamic Named Range. It in the image and likeness of tables in Word is compressed and expanded when entering data. Besides the fact that this range can be used in everyday work of Excel, with its help you can create dynamic charts and pivot tables.
You can assign your own hot keys to any command.
This add-in has the useful property of adding the functions of a given set of macros to the book, after which they can be used on computers where Plex was not installed.
Using this add-in allows you to extract unique elements from a list by a given column. Recoverable items can be selected or a new list can be formed from them.
In addition, you can change the size of the range of cells, the type of links in formulas, use various tools in your work to speed up work with pivot tables. Plex has a built-in calendar, with which you can instantly enter the date in the cell, a formula converter to values, which can be done both in the entire book, and in the selected fragment.
This add-in is equipped with a sheet manager, which allows you to hide and show sheets, sort and protect them, and also perform other actions with them.
With its help, you can round numbers, rearrange values in reverse order with automatic replacement of formulas. When using Plex, it is possible to combine cells with saving text when using the delimiter character.
This add-in is equipped with a text formatting tool that allows you to convert the Cyrillic alphabet to Latin, change case and perform other operations.
Using this set of macros, you can select rows based on a given cell.
When you enter the formulas, arrows are displayed showing the dependencies. A book and a range of cells can be cleared of excess at any given time.
Plex allows you to swap columns and rows, build mini-histograms. This add-in allows mailing to various email addresses. Large tables can be distributed over several pages according to specified conditions. The book’s external links may be torn or open. Some sheets from various files can be copied to one. You can create a drop-down list in a cell. Plex allows you to sort lines by fill color or randomly. Book sheets can be saved as separate files.
In addition, you can compare ranges, copy formulas without shifting the range.
Only the main features of this add-in are listed here, and there are many more.
Macro “Amount in words”
When filling out receipts, in many cases it is necessary to put down the amount not only in numbers, but also in words. There is an additional MS Excel add-in that allows you to carry out the last action. If you need to automatically download it, you must place the downloaded sumprop.xls file in the XLStart folder located in the program installation folder.
The “Amount in words” add-in in Excel allows you to enter in writing both monetary amounts when using the “SumPrescription” function and regular numbers when using “NumberPrescript”.
Finally
There are various add-ons for Microsoft Excel. Their purpose is to facilitate various operations in this program, add missing functions, and reduce the time spent on performing routine actions. This article covers only a few add-ons. In fact, there are many.