Creating Excel XLL based user-defined functions (UDF) in C# or VB.NET

See how to create custom UDFs: Excel Automation Add-ins and XLL add-ins in Visual Studio using C# or VB.NET. Find more info at http://add-in-express.com/excel

---

Closed Caption:

user-defined functions or Utes our
custom functions in Excel
that allowed the end-user utilizing
within their formulas inside at the
Excel workbooks
without express you have two ways of
creating Utes
the first history an Excel automation at
in in the second is via
what's known as an excel at it now Excel
add-ins
are calm based attitudes and they work
with Excel 2002 through 2010
and Excel wells our deals that Excel can
opened directly in their support it with
Excel 2000 to 2010
now big note here is that these version
numbers are
what at an express support from building
these two types
%uh solutions with at an express
whichever direction you choose you can
still do things like
creating custom functions that are not
include with Excel and you can do things
like
implement new versions a built-in
functions an extended should maybe have
your own
little wrinkle or tweet to it that you
need for your business rules
you can also do things like connect data
from your legacy systems are your lineup
this business systems and somehow
include them in great demand XLT a just
a custom function you can also do things
like
include them in your application
specific data flows are all workflows
but this video I want to highlight the
ex lol project type: and go over the
main
reasons as to why you choose it over the
Excel automation at it
to create an ex lol
project for building user-defined
functions in the following situations:
you want to deal well versus a combat in
one of the reasons for that is speak
you need to support maybe Excel 2000
other support
all the way to 2010 is there and is
available for you as well
and also the need for speed in terms of
your computational power recalculations
especially financial complex math
calculations
you wanna make you serve both functions
and procedures something that's not
available in automation at its
also to you might wanna feel too high to
function or procedure by setting
the eighty Excel function to Scripture
dot is hidden equal to troop something
that you're not able to do within our
mission at it not
another nice features that you have
access a low-level Excel features
via the 80 X X lol module .co worksheet
function
again something that's not available
thought mission at it
and if you need to sell modification
that's possible with excel at it more
information is obviously available indie
developer guides
available online had an express to come
the cars have
descriptions it's not really important
for the Excel Lt have a description but
you really want your functions as well
as their arguments to have a description
now the difference here with automation
add-ins is that
the automation adding can have a
description but then its functions in
arguments cannot sell
of these are the things that you need
most value most in your add an annex lol
at it is the way to go what will do next
is I'm going to fade to black and when I
come back I'll be in Visual Studio
and I'll walk you through the process of
creating a very simple yet somewhat
handy
excel at it
are as promised here I am in Visual
Studio 2010
and will start out by doing the new
project button on the command bar
and in in the New Project dialog coach
the other project types
under the extensibility node and I will
select the a DX
X L L at and project which will create
and isolated Excel add-in for Microsoft
Excel 2000 and higher
so I'll just call this my Excel
at in say okay
now in the new Microsoft Excel excel
addin
I'm just going to select Visual Basic in
a leafy interop assemblies
as microsoft office 2000 and higher
click Next
for the strong name are discrete
generate new select
finish in the wizard will go ahead and
create the project force inside official
studio
2010 in open up the XLL module
that BP designer where we can begin to
build a project
the Excel well muscled designers where
we can
configure a lot of the key things about
are excel addin one is I'm sure to click
and
on the designer make it active role
notice over here in properties
didn't name the designers X lol module
but in the added name I could give this
whatever name I want it defaults to that
name
up the project my ex lol added but I
could choose to name it
something different if I like but I'm
I'm gonna go ahead and just leave this
is a fault but that's their we could do
things like
specify if we were to display the alert
specifies fuller should be shown when
the ad is being
and installed in Excel is running back
is that the false what not
load at startup specifies that the adage
should be loaded at the Excel startup
which we wanna leave
as true for what we wanna do too add
are custom user-defined function is move
over to the designer and I will right
click
and we will add will choose the at Excel
function category which will at this
a BX Excel function category one object
to the designer
the subject just specifies
the function that would listen cited the
insert function category
at dialog box it's inside an excel in
our show this later with demo it
and what we can do is go in the
Properties window for this object
and we can specify the name of our
Category for which are functions that
will define
will reside in here I'll just say these
are my
company functions
now we can go to the function to
scriptures
you know hit this ellipsis button and
that will open up
the eighty XXL function category
Controls collection
dialog box office allows us to do it
started to find the functions
that your side inside this category in
this demo we're gonna at one function
and we can
we can come over here to the members are
received in my company functions
category
I will select at Excel function
from the out right click
can do the context in here but also have
the choice in this dialogue has a
to park it hit the Save button there and
I now have a
function description for descriptor
added and allow me to add a description
to function choose a function name which
is
a drop-down available functions that
I've for
code have actually written which at we
haven't done yet
and then and then we have other
properties that weekend specify
are desired values for I'm gonna go
ahead and say okay
at the moment and just leave that step
down plank so that we can now
move over to the code window and I'll
just do
view code and we'll move down here to
you the code window
where we will to find are you tee eff
before actually read it looks to expand
this section here says define your UPS
in this section
was just a region that's been created
force by
the at an express framework minimize
that so we can see that more
we just see that this is a container for
user-defined functions
in this is where we need to put put our
functions and we have
this read-only property love underscore
module which is the
XLL add-ins XLL module object
next if we expand the sample function we
can see
just a good idea of what a sample
function should look like
for see that this is a shared function
all functions are or
and procedures they would need to be
shared public shared
functions and procedures we see that we
need to pass
an argument they will pass is not
checked in in this case if it's a
function this was a string
and it's just too good if statement that
that
exemplifies our illustrates how each of
the different
argument types that are supported can be
handled inside of an art
insider net now I want to do
is I'll just go underneath this sample
function
and will create a new folks this
function is going to return
just boilerplate text that allowing in
now use the program it or to pass what
text that one
so this will be public
shared function
call this my
company
boilerplate text and term serv
the transmitter will do 5 I'll I'll say
desired Texas parameter name as an
object
and more return a string
what this functional do is check type
have the desired text
argument and if it's a string
Kanwal do what we want for single douche
store
the text
from the past desired tax
and then we'll do a slight case
if we say my company name if that's the
past
value there or turn
it's too hard coded value
at an express we save my street
them overturned
1,2,3,4,5 Main Street
if we save my address
federal return
Dallas Texas Sep 5
2001
and it will do que celles that's just
good practice so
depending on what I pass here in the
desired Texas is what all
return as the value my string now
let's finish our statement so if we
don't
if value is is not a string
then in fact actually let me change this
right here on the case
else to say turn
and text
now let's go handle what happens if if
text is not passed so we can do
say if module if it's in the function
wizard Sophie if the function was urs
open side effect
Excel we can determine that hearing code
they will say
turn parameter must be a string
otherwise what we want to do is for just
return
at an express
miso
PDX Excel error
specify a.m.
Excel hair bow has
as here tight and I don't do it that is
the end of our function
and now we can go to
go back over here to the ex lol module
we can
where we can go back to our function
category in the function descriptors
and pickin you can check it out again by
selecting our function
in now go ahead and first of
selector function name which is the mic
boilerplate
text now to say this inserts
company boilerplate
text
asleep to now for is hidden I wanna go
ahead
and leave it has fault cuz I wanted to
show in
T insert function wizard also to
is macro that's fine is thread-safe
false
and for its fall to like it specify this
to be true
and what would happen isn't on the
worksheet recalculate it would
recalculate this function but I'll I
wanna leave that for false
because we don't want to recalculate
every time and now what I need to do
is come to the us like the
my company boilerplate tax and then I
will say
at parameter because we need to
map the parameter the function back to
you
this parameter descriptor right here
in this this works the same way add a
description here
desired
boilerplate text and in the tram her
name
I can select in its hard in Pitt Rd
knows base of have
where to find my i Function I'll say
desired text
and I'll say okay now we have a fully
functional X lol
added so let's build the solution and
next
will register the ATF's Project on my
system and this will allow me to demo
okay
I'll open up Excel 2010 2 demo and I'll
go to the formulas
into insert function from the ribbon in
the Select
category dropdown I can move
now to the my company functions which is
the name in the category that
I defined and we have my boilerplate
my company boilerplate text we can see
also to the in search company
boilerplate Texas description I gave
everything's working exactly as
configured and I'll say
okay and we can start out by saying my
knee
company name
and we'll see as well full support for
the insert function wizard that the four
wheeler
result at RT now it's going to be at an
express and we can see
here equal field at express Accenture I
could do
say I say changed to my company names or
something like that that's not supported
me see that
the preview knows its Rd evaluated the
formula noses canopy and
textiles where the back and say okay and
then as well
I can type in my company boilerplate
text
and do my street
will finish it off with
my address ever get perfect
created this is a short demo in a pretty
simple scenario but it does highlight
just how easy it is create user-defined
function
with at an express that
creates an X lol added it's really easy
to integrate inside insert function
and taking this to the next level for
your own business rules
is really easy at an express does it all
for you you can just worry about your
code in your business rules