IFERROR Function with Excel VBA (how to create Excel Addins) [HD]

Hello Excel users

Today I will showing you how to create an Excel AddIn for wrapping up formulas in cells with IFERROR Function, which will sit on your customize tab in your ribbon all the time and by clicking this VBA code you can add iferror function to formulas in your selection, also this is smart VBA code which will only run and give effect if your selection has any formula or it will show message that " No formulas were found in selection"


Link to Original code: http://www.thespreadsheetguru.com/the-code-vault/2014/3/7/wrapping-an-iferror-function-around-your-formulas
Link to code file is here: https://drive.google.com/file/d/0B4rRHhwhOXFZVGlPZVJaVVczVmM/view?usp=sharing
Direct Excel Addin file to be put in location : C:\Users\user\AppData\Roaming\Microsoft\AddIns https://drive.google.com/file/d/0B4rRHhwhOXFZV29uRVNsOFl5dFk/view?usp=sharing

Being my first ever Excel tutorial on this channel, I might have made some mistake in my approach but let me know your thoughts in comments section below, I will be happy to help you if are stuck somewhere in above process

Leave a LIKE share and Subscribe

---

Closed Caption:

Hello youtube, this is Prash from ExAccounting
channel, today I will be showing you a very
useful VBA Code which you can create as button
in your custom ribbon, just like hereI have,
so basically this same sort of vba code which
have made tutorial in first video on this
channel which is rounding up your selected
cell formulas with Roundup function so in
this tutorial i will show you how to wrap
up your cell formulas with IFERROR function
with single click , IFERROR function as you
know it is very useful function and which
relatively new function added in Excel 2010
version, i will give you here a brief intro,
IFERROR function gives you an alternate value
(or whatever value you desire) when there
is an error in any formula you have created.
Suppose there was an error in cell B22 here
in this example, if I add here IFERROR function
and if there is an error I want value to be
0, if I remove Sale figure to nil or delete
it as it is denominator in formula of Gross
Profit % it will give be 0 value, if error
function is most useful when you want to ignore
errors in your formulas and calculations every
now and then, I will be doing a separate tutorial
for IFERROR function, it is very useful function.
I will show you demo of this particular VBA
code (Macro) which is very useful and it is
very smart VBA Code, and I got this got from
a website called Spreadsheetguru.com it has
very well designed code for us to use freely,
you can just copy and paste this code directly
to your VBA directory to use, or you can create
a personal macro workbook or you can create
an excel addin for it so that you can use
it all the time when you open any workbook
or sheet, so I created a button in my custom
ribbon so that I can access the same anytime
I want in my workbook. Credit goes to spreadsheetguru
website who has created the code for us to
use, I will show now how this code works,
so suppose we want to wrap up this particular
formulas in this row, which can create error
if the sale figures not there, because it
is dividing Gross Profit with Sale value,
ROW9 with ROW7 lets us wrap up this row with
iferror, it show you what this function (VBA
code) has so much to offer to us and it is
very flexible if I click one on this button
it will wrap up cell with iferror having value
NIL (nothing/blank) if there is an error in
formula, so lets test that, I will now put
0 in sale and you can see Gross Profit % cells
have no data in it due to iferror function
has nil value selected, now if you click the
button second time it will give value of 0
if there is an error and if you return to
original formula as it was you need to click
the button for third time. if there are already
cells with iferror function it will not wrap
up those cells with iferror gain (iferror(iferror)
so you can toggle click it to get two different
values and
remove iferror. Now I
will show you how to create excel addin and
put in right next our roundup button in custom
ribbon and use it whenever you want we will
be going to Developers tab and click Visual
Basic or ALT + F11 short cut key on your keyboard
we have select workbook 1 which is default
open right now and add new module and now
you can just copy paste the code i showed
you earlier either from website or from notepad
file I have linked in description below, so
this is done and your vba code is ready to
use we will be saving it as Excel Addin file
to appdata folder you can give name of your
choice to addin file, now go to developer
tab and Select Excel addins you will see Wrapupiferror
addin available for you to use you just have
to check it and click OK, now since you have
activated your Addin, now we can bring it
in our custom ribbon bar, for that you have
to go to excel options - customize tool bars
- select custom tab which we have created
already - select new group - on left hand
side window we have to choose Macros section
- in that we will choosing Wrapupiferror macro
- and add
it to custom tab, click OK now as you can
see it has created a button next to roundup
button, i will show you breif demo, i will
select whole table and it will only wrapup
cells with formulas in selection all the formulas
in selection have been wraped up with iferror