Foreversoft.ru

IT Справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel add in

Excel add in

An add-in is a program that can be attached to Excel to give it additional functionality.
Once an add-in has been installed its functionality is available from any workbook.
An Excel add-in has the file extension («.xlam») and is a workbook that Excel can open automatically when it starts up.
Add-ins provide an excellent way of increasing the functionality of Excel and are ideal for distributing user defined worksheet functions.
Any workbooks that have been converted to add-ins will have their worksheets hidden.
Add-ins are specific to a particular application. Excel add-ins are specific to Excel and cannot be used with any other application.

What can an Excel Add-in do ?

Add-ins are used to distribute macros and provide an excellent way of increasing the power and functionality of Excel.
You can include anything in an add-in that can be written in code.
Some of the most common uses are listed below:
1) User defined (or custom) worksheet functions can be written to help simplify your formulas.
2) Toolbars and the Menu bar can be customised. Commands can be removed and new ones added.
3) Shortcut Menus can be customised. Commands can be removed and new ones added.
4) Databases can be connected to and data can be extracted or imported.

Add-in Facts

None of the worksheets within an add-in are visible when the add-in is loaded. Although they can be used for reference within the VBA code.
When changes are made to an add-in you will not be prompted to save your changes when Excel closes.
Any public macros in your code modules will not appear automatically in the (Tools > Macro > Macros) dialog box. They can however be typed in manually and run.
Any function can be referenced (i.e. as a worksheet function) without the need to prefix the workbook name.
Any VBA code added to an add-in will be visible unless a password is added to the VBE project.
It is not possible to bypass the automatic Workbook_Open() event by holding down the Shift key.
The IsAddin workbook property that can be visible when you select the ThisWorkbook object in the VBE is set to True.

Advantages of using an Add-in

Your custom worksheet functions will not have to be preceded by the name of the workbook when you use them in your formulas.
An add-in is easier to distribute and share than a workbook.
Add-ins can be placed in the Excel start up directory meaning that they are loaded automatically when Excel is opened.
If you are the only user of an Excel add-in then the VBA code can be easily edited and saved without the need to open or save extra workbooks.
The Visual Basic code contained in an add-in can be compiled before the workbook is saved. Code in a normal workbook is not compiled until the code is run for the first time. Therefore Visual Basic code contained in an add-in can run slightly faster.
Any event handling procedures cannot be bypassed by using the Shift key. This ensures that an add-in is always initialised.
An add-in is almost invisible to the user, especially if you add a command to an existing drop-down menu. This may be more appropriate for inexperienced users that could get confused by having to open up additional workbooks.

Options

(Advanced tab, At start-up, open all files in ) — This can be used as an alternative folder to the xlstart folder.
(Advanced, General, Show add-in user interface errors ) — Error messages will be displayed when Excel starts if there are any errors.

Disabled Items

The disabled items are stored in registry also HKEY_CURRENT_USERSoftwareMicrosoftOffice12.0WordResiliencyDisabledItems

Opening Add-ins

When you open a workbook you have the option to hold down the Shift key to prevent any startup macros from running.
This Shift however does not work when you open add-ins

Saving your changes

When you make changes to a workbook and try and close it you are prompted to save your changes.
If you make any changes to the VBA code in an Excel add-in, you will not be prompted to save these changes when Excel closes.
You must always manually save your changes first before closing Excel.

Important

Be aware that the more add-ins you have installed, the longer Excel will take to open. It is worth selecting (Tools > Add-ins) and clearing the check boxes for any add-ins that you do not use.
Any add-ins placed in your xlstart folders will be automatically loaded when Excel is opened.
The majority of add-ins will probably be protected which means that if you try to view the project in the Visual Basic Editor you will be prompted for a password.
You may need to run your setup installation again if you can’t find a particular add-in in your (Tools > Add-ins) dialog box.
When searching for installed add-ins also check the 2 XL Start folders: machine folder and personal folder.

Руководство по созданию Excel add-in для начинающих

Недавно мне пришлось по долгу службы писать небольшой Excel add-in. К слову, до этого мой опыт работы с MS Office из-под .NET сводился к простеньким утилитам, генерировавшим отчеты в Excel при помощи Office Primary Interop Assemblies. А уж когда контора купила лицензии на Aspose.NET, взаимодействие с офисом через COM было забыто аки страшный сон. Так вот, начав разбираться в теме, я был неприятно удивлен отсутствием вменяемых русскоязычных туториалов для начинающих. Теперь хочу заполнить этот пробел. В качестве примера мы создадим add-in, который по нажатию кнопки генерирует на текущем листе список дней текущего месяца и выделяет выходные дни. Заинтересовавшихся прошу под кат!

Вместо предисловия

Заранее хочу оговориться, что сам я не профессионал в области разработки под MS Office, и здесь возможно найдутся люди, которые раскритикуют мое решение в пух и прах. Что ж, я буду рад любой конструктивной критике. Так же эта статья не является исчерпывающим руководством по написанию Excel Add-ins, не ставит цели подробно описать архитектуру Excel Add-ins или содержимое пространства имен Microsoft.Office.Core. Это вводная статья, которая, как я надеюсь, поможет новичку сориентироваться в новой для него теме и проведет через процесс создания add-in’а, который делает что-то осмысленное. Итак, приступим!

Читать еще:  Слишком много форматов ячеек excel

Готовим солюшен

Откроем студию и создадим новый проект типа Excel 2010 Add-in. Назовем его SampleAddIn:

Если у вас все получилось правильно (а что собственно здесь могло получиться неправильно. ), вы увидите солюшен со следующей структурой:

Единственный файл, который сгененрировала для нас студия – это ThisAddIn.cs:

Здесь все просто – мы всего лишь регистрируем обработчики событий запуска и завершения нашего add-in’а. Сейчас, естественно, наш Add-in ничего не делает, но вы уже можете его запустить как обычный проект, по нажатию F5. Что особенно приятно, на этом этапе можно не заморачиваться развертыванием, т.к. студия сама запускает Excel с нужными настройками безопасности и подсовывает ему наш Add-in. Если все получилось, то при запуске Excel вы увидите такую надпись:

Рисуем интерфейс

Добавим в солюшен элемент типа Ribbon и назовем его SampleRibbon

В дизайнере добавим туда кнопку:

И зададим названия для вкладки, группы и кнопки:

А так же создадим обработчик нажатия кнопки:

Попробуем запустить наш проект. Если вы все сделали правильно, то в Excel’е появилась новая вкладка с кнопкой, по нажатию на которую появляется окно с сообщением.

Ковыряем чуть глубже

На данный момент мы столкнулись с двумя проблемами: во-первых, если в классе ThisAddIn есть поле Application, дающее доступ к запущенному экземпляру Excel, то в классе Ribbon ничего подобного нет, а следовательно непонятно, как именно взаимодействовать с листами Excel’я. Во-вторых, как мы все знаем, смешение логики отображения и бизнес-логики является отличной заготовкой для хорошей порции спагетти в вашем коде. Поэтому изменим код, так чтобы избавиться от этих двух проблем, а заодно разберемся в «магии», которая встроила наш Ribbon в Excel без каких-либо действий с нашей стороны.

За создание пользовательских лент в Add-in’е отвечает метод CreateRibbonExtensibilityObject из класса AddInBase, от которого на самом деле унаследован наш класс ThisAddIn. Слава богу, этот метод был предусмотрительно сделан виртуальным, так что перегрузить его не составит проблем. Для начала добавим в класс ThisAddIn код:

И запустим проект. Вуаля! Вкладка исчезла. Теперь изменим класс SampleRibbon:

И класс ThisAddIn:

Запустим проект. Если вы все сделали правильно, то вкладка снова появилась, а по нажатию кнопки, как и раньше выводится окно с сообщением.

Работаем с ячейками

Теперь пришло время для того, ради чего собственно и был затеян весь это сыр-бор – генерации таблицы. Для начала создадим пару вспомогательных методов для оформления ячеек:

Этот метод выделяет границу ячейки жирным. Кстати, обратите внимание на тип аргумента border – dynamic – начиная с .NET 4.0 это очень удобный способ работы с COM-объектами! Если вдруг вы не в курсе, рекомендую ознакомиться, например, с этой статьей. Для нашего удобства создадим еще пару методов:

Теперь напишем код для получения списка дней месяца:

И сведем это все воедино:

Если вы нигде не ошиблись, то запустив проект, и нажав на кнопку на нашей вкладке, вы увидите нечто подобное:

50 Best Excel Add-Ins That Will Make Your Life Easier

Want to get certified?

Go from novice to Excel ninja with bite-sized training courses

Most Excel users don’t realize there’s a whole world of spreadsheet add-ins just itching to make their jobs — and lives — so much easier.

There are add-ins to boost your productivity, and even add snazzy visualizations to make your spreadsheets stand out from the snoozefests you’re used to. Others can help you run your business more effectively, study smarter, and save time analyzing your data.

To help you navigate all these awesome options, today we’ll be exploring the top 50 (mostly free) Excel add-ins together, broken down into 8 categories:

You can quickly jump around to the add-ins you specifically need, but we highly encourage you to read our guide in its entirety so you can discover all the add-ins you’ve been missing out on.

Want to learn more?

Take your Excel skills to the next level with our comprehensive (and free) ebook!

Boost productivity

To start, let’s jump right in with the top 5 Excel add-ins to make you more productive.

  1. If you’re looking to up your productivity game, become more organized, and crush all your business goals this year, the Power User Excel add-in has your name on it.
  2. Keep in mind, Power User is better suited for freelancers in the marketing and consulting spaces. For those in finance, Macabacus has features designed with your specific business needs in mind.
  3. Duplicate Finder and Deleter can help you save a ton of time by finding all your duplicate spreadsheets and automatically deleting them for you. This frees up both time for you and space on your drive.
  4. With this mail add-in, you can choose to send bits and pieces of your spreadsheets or the entire shebang via email.
  5. ASAP Utilities helps you accomplish more with Excel by giving you time-saving shortcuts. According to their site, the average user saves an average of 30 hours per year!

ASAP Utilities add-in

Visually jazz up your spreadsheets

Keeping your charts organized and easy to understand will also help you shave time off your busy day. Here are the best Excel add-ins to up your spreadsheet game.

  1. Labeling your charts is one of the best ways to make your data easy to understand. Use this chart labeler Excel add-in and your charts will be way less confusing.
  2. Insert a map right into your spreadsheets to plot locations and visualize data with this Bing Maps add-in.
  3. And if you’re looking to add images instead, this Pickit Free Images add-in can help you. This add-in came in second place at the 2017 Office App awards in the Best Overall App category.
  4. For readers needing to add v >Excel Colorizer add-in

Create unique charts not found in Excel

Ever want to take your Excel charts to the next level? Say hello to the add-ins that make those boring standard charts a relic of the past.

    Radial charts help present data in a visually interesting way. If you’re already using a pie chart style, cons >Ultimate Dashboard Tools

Study more efficiently and give better presentations

The next set of add-ins will help you study more efficiently and give your presentations the boost they need.

    If staring at a blank page makes you zone out and off the screen, cons >GIGRAPH Network Visualization add-in

Manage and simplify finances

The next bunch of Excel add-ins makes the chore of managing your finances less overwhelming.

  1. If simplifying finance spreadsheets is what you’re after, this financial reporting add-in wants to help. It even lets you keep track of your spending thanks to real-time updates.
  2. For readers who need to manage their checkbooks, skip the old fashioned method and use Checkbook assistant instead.
  3. You can also build and test your trading strategies before spending a dime on actual trades with this Excel add-in.
  4. And if you’re looking to create trade systems or conduct a technical analysis, this Analyzer XL add-in promises to help you do just that.
  5. The Analystix financial analysis add-in simplifies calculations such as weighted average cost of capital (WACC) and compound annual growth rate (CAGR).
  6. To optimize your portfolio without the help of a professional, grab this add-in.
  7. You can also use this one to download price data on different securities.
  8. Applying for and understanding how a loan works can be tricky. Luckily, this Loan Assistant add-in should simplify the process for you.
  9. The same confusion can happen any time you try to convert different currencies. But with the help of this currency conversion add-in, all the hard work is done for you so you can enjoy your trip.

Professor Excel Currency Converter add-in

Optimize marketing and branding

Hoping to improve your marketing and branding efforts? You don’t want to miss this next group of Excel add-ins.

  1. It’s a pain to keep setting up your branding colors every time you need to create or use a spreadsheet. This add-in saves your company’s colors so you don’t have to waste time doing this task repeatedly.
  2. SEO Gadget is an Excel add-in that helps you organize all your SEO data in one central location so you can instantly see how your site is performing. If you’re looking to up your SEO game, check out these actionable SEO tips for startups.
  3. And if you need SEO tools to actually get the job done, this add-in is for you.
  4. Use the Excellent Analytics add-in to import your Google Analytics marketing data into a spreadsheet.
  5. You can even create QR Barcodes in Excel thanks to this add-in.

SEO Tools for Excel add-in

Analyze data more efficiently

If you’re also looking to analyze your business as a whole (and not just your marketing efforts), the next set of add-ins has you covered.

  1. The Analysis ToolPak was designed with engineers in mind. It will help you develop statistical analyses that go well beyond the basics.
  2. This Solver add-in helps you uncover solutions that fall within the constraints of the problem at hand.
  3. If you’re simply trying to analyze your data, enable the PowerPivot add-in built into Excel 2016 and 2013. Or install this version if you’re using Excel 2010.
  4. The Spreadsheet Studio add-in has a set of tools that allow you to better analyze the structure and formulas in your workbooks.
  5. Forecasting is also made simple with the help of the data mining Forecast Wizard add-in.
  6. PowerApps helps you connect the dots between data points and gives you the ability to share these findings with your team.
  7. Another similar tool lets your team datamine more efficiently. It helps everyone recognize patterns within tasks so the job takes less time.
  8. This risk analysis add-in uses Monte Carlo simulation to help you assess your risks.

@Risk add-in

Miscellaneous Excel add-ins

Curious about all the other add-ins we haven’t categorized yet? The next add-ins will help you in a variety of different ways.

  1. If your chess club needs help ranking its members, use this free Excel add-in to do just that.
  2. And if you’re looking to create a place to keep all of your favorite directories and workbooks, use this Bookmarks add-in.
  3. Looking for a way to hunt down economic information quickly and efficiently? Use the FRED Excel add-in to significantly cut down your research time.
  4. Work with multiple documents within a single window with the Office Tabs add-in.
  5. If you need to break down units of time into easier-to-understand smaller ones, reach for the Time Dimension add-in.
  6. And if you’re ever struggling to come up with a bunch of random numbers, this tool can help you waste less time.

Random Generator add-in

Start using Excel add-ins today

By taking advantage of one (or more) of these Excel add-ins, you’ll not only save a ton of time in your super hectic day, you and your spreadsheets will become ultra-effective, mega efficient all stars.

Whether you’re looking to create a better presentation or study smarter for finals, this list has every add-in you could possibly need. To up the ante further, check out our compilation of the best Excel templates.

So feel free to share this resource with everyone you know struggling with their spreadsheets and they’ll thank you for improving their productivity as well!

Wanna take your spreadsheet game to the next level? Try our advanced excel curriculum and put your skills to the test!

Crafting fluff-free content is Devan’s jam. When she’s not writing for GoSkills, you’ll find her outside reading, soaking up the sun, or hiking her next adventure.

Add-Ins in excel

Excel Add-Ins (2007, 2010, 2013, 2016)

Add-ins are different extensions of excel which when enabled they activate when the excel is started and a user can use its functions, excel has various different add-ins and they are in the options section of the file tab, the first box shows the enabled add-ins in the system and if the user wants to enable more add-ins we need to click on manage add-ins.

Add-In looks like a new word if you do not have any idea of additional excel features. Excel Add-in is nothing but additional features and options added to your existing Microsoft Excel. Adding additional functions to you will help you in terms of new features advantages. I would say Add-in is a kind of custom function that increases the power of Excel.

Some of the Add-ins are readily available in excel but are hidden in default excel. Some of the most important add-ins are Solver, Data Analysis (Analysis Tool pack), Analysis Tool Pack VBA.

If the add-ins are already unhidden in your excel then you must see all these add-ins in your Data tab.

How to Install Excel Add-Ins?

If your excel is not showing these options, follow the below steps to add add-ins.

  • Step 1: Click on the FILE tab that is located at the topmost left corner of the excel.

  • Step 2: After clicking on this FILE tab select Options.

  • Step 3: Once you click on Options excel will open a separate window. From this window, select Add-Ins.

  • Step 4: Find Mange: drop-down list at the bottom of the window and select Add-ins and click on Go…

  • Step 5: After clicking on Go it will show you below the dialogue box. You can select all of the add-ins you want. I have selected all 4.

  • Step 6: Now you can see SOLVER and Data Analysis options under the Data tab in the ribbon.

Types of Add-Ins

#1 – Inbuilt

These are inbuilt add-ins and you can unhide them by following the above steps.

#2 – Downloadable

We can download many Add-INS from Microsoft’s website www.office.com

#3 – Custom

These Add-Ins are built by individuals who regularly work on Excel Macros. Some of them are free and some of them are involved cost to use them. These are designed to support the basic functionality of the excel. We will look at how to create custom functions using VBA Add-ins later in the post.

Data Analysis Tool Add-in

Under this tool pack, we can any kind of Data Analysis.

If you click on this Data Analysis you will see many types of analysis under this.

Usually, we create Add-Ins by using VBA macros.

How to Create Custom Functions in Excel and Install as an Excel Add-In

Ok in this article I will show you a simple custom function that we can create and add that as an Add-In to our all excel files.

Example #1 – How to Extract Comments from the Cells

In this example, I will show you how to extract comments from the cells.

  • Step 1: Open the new workbook.
  • Step 2: Press ALT + F11 (shortcut keys in excel to access Visual Basic Editor)

  • Step 3: Go to Insert and insert New Module.

  • Step 4: Once you have inserted the Module apply the below code to the module.

Function TakeOutComment(CommentCell As Range) As String

  • Step 5: Once the code is entered save the file as Excel Add-in

  • Step 6: Now open the file that has comments.
  • Step 7: Go to File > Options > Add-Ins > Excel Add-in > Go and click on Browse option.

  • Step 8: Select the Add-in file that you have saved.

  • Step 9: Click Ok. You can see the new Add-in as per your workbook name. (I have named as Excel Add-In)

  • Step 10: Right now you will not see this add-in. However, you can apply it as an excel formula and extract comments.
  • Step 11: Now go to the Comment listed sheet. I have created 3 comments for myself. You can also create your own data.

  • Step 12: Go to cell B1 and enter equals and start typing our Function name i.e TakeOutComment.

  • Step 13: Select the cell A1 as the reference it will extract the comment from that cell.

In the cells A2 & A3 there are no comments that are why formula returned the value as #VALUE!

Example #2 – How to Hide Worksheets in Excel?

In this example, I will show you how to hide worksheets in excel except for the active sheet and add that as an Add-In to the excel.

  • Step 1: Open a new workbook.
  • Step 2: Go to the Visual Basic window and insert a new Module.

  • Step 3: Copy and paste the below to the module.

Note: There are two macros here. Copy and paste both of them.

Sub Hide_All_Worksheets_()
Dim As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
If Ws.Name <> ActiveSheet.Name Then

Ws.Visible = xlSheetVeryHidden
End If
Next Ws

  • Step 4: Save this workbook as Excel Add-in.
  • Step 5: Add this Add-In to the new workbook. Go to File > Options > Add-in > Go > Browse.

I have saved the file in the name of hiding All Worksheets.

  • Step 6: Click on OK. You can see the new Add-in as per your workbook name. (I have named as hiding All Worksheets)

  • Step 7: Now Right-click on the ribbon and select Customize Quick Access Toolbar

  • Step 8: Click on Quick Access Toolbar and select Macro from the first drop down and choose the macro name, then Click on Add Button and Click on OK.

  • Step 9: Now you can see the little icon on your toolbar.

If you click on that icon it will hide all the worksheets except the one you are in right now.

Example #3 – How to Unhide those Hidden Sheets?

In this example, I will show you how to unhide those hidden sheets. Follow the same procedure and copy-paste the below code.

Sub UnHide_All_HiddenSheets_()
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
Ws.Visible = xlSheetVisible
Next Ws

Save the file as Excel Add-in and this add-in to this sheet.

Now you can see another icon.

If you click on this icon, it will Unhide all the hidden sheets.

Things to Remember

  • We need to save the file in the extension of Excel.
  • We can add any Add-Ins by browsing them under add-in section
  • We can uninstall any add-in at any point in time.
  • If you search in google you will get many add-ins

Recommended Articles

This has been a step by step Guide to Create, Install and Use Add-Ins in Excel. Here we discuss types of excel add-ins and how to create a custom function and install that as an Add-In, along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

Ссылка на основную публикацию
Adblock
detector