User Defined Function

[Guest]

I put some vb code to a add-ins file that create markup i.e

Function markup(principal, rate, days)
markup = principal * rate / 365 * days

End Function

it works fine in new sheets but whenever I try to replace the formulas in
existing sheets it display #name? error.
can someone help me what I'm doing wrong or is it a formatting problem

Thanks in advance

 

[Bob Phillips]

Did you install the add-in or just load it like a spreadsheet?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Its already installed as a personal.xla in my add-ins lists

 

[Bernard Liengme]

Not sure what you mean by 'to an add-in'
Where did you actually place the code? If it is Book1 then to use it in
Book2 you need Book1 open and refer to =Book1.xls!markup(a,b,c)
Why not save the macro in Personal.XLS. This file is saved in XLSTART
folder; it automatically opens when Excel starts but is hidden. Then you use
=Personal.XLS!markup(a,b,c)
best wishes

 

[Dave Peterson]

Any chance that those existing workbooks have a name called Markup?

Insert|Name|define to check

or Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager:
NameManager.Zip from http://www.oaltd.co.uk/mvp

 

[Guest]

Thanks Dave, but "the internet explorer was not able to open this internet
site" when I download "namemanager.zip" file anyway can you further define me
where & why should I define name; this is a small formula that I want to put
in various sheets that's why I put it on add-ins named personal.xla so that I
can use it as =markup(principal,rate,days) or =markup(500000,5%,79). thats it.

 

[Dave Peterson]

I didn't want you to define a name. I wanted you to check to see if there was a
defined name already there that was causing confusion.

And one more guess. Do you have any modules in the project named Markup? If
you do, try renaming it to Mod_Markup.

 

[Guest]

I checked again there is no define name or any module & one thing more, the
same problem I'm facing with other functions like

Function adminsc(gp)

If (gp * 0.05) >= 2000 Then
adminsc = 2000
Else
adminsc = gp * 0.05
End If

End Function

hope it clarifies, Thanks for taking interest.

 

[Dave Peterson]

It hasn't clarified the issue for me.

I'd go back through the suggestions and try them one more time.

And maybe you could explain your original post one more time:

====
it works fine in new sheets but whenever I try to replace the formulas in
existing sheets it display #name? error.
can someone help me what I'm doing wrong or is it a formatting problem
====


Does this mean it works in new sheets in existing workbooks?

What are you trying to replace the formulas with?

What was the existing formula and what are you replacing them with?

I checked again there is no define name or any module & one thing more, the
same problem I'm facing with other functions like

Function adminsc(gp)

If (gp * 0.05) >= 2000 Then
adminsc = 2000
Else
adminsc = gp * 0.05
End If

End Function

hope it clarifies, Thanks for taking interest.

 

[MrShorty]

I don't know if this will help, but I've seen similar behavior when I'v
moved an add-in to a different folder on my hard-drive. When you buil
a spreadsheet referencing a function in an add-in, even though you onl
see "=markup(...)" in the cell, Excell remembers that that formula wa
called from a given add-in in a given location. You may have an add-i
installed with a function called "markup", but if it isn't the same fil
in the same location as when the spreadsheet was first built, Excel wil
return an error

 

[Guest]

Ok dave I further explain, this function of vb code I wrote in VB editor
(Insert module then write code then save as add-ins and named it to
personal.xla in Add-ins directory),now when I create a new worksheet file and
use it this formula it work fine but when I use this formula in my an
existing worksheet it display #name? error, I didn't understand same formula
works in new file but don't work in old file. I don't know what I'm doing
wrong as I'm new to vb code & I learned this function from MS help in custom
user defined functions.

 

[Guest]

you may be right as what I understand but the same problem I'm facing with
another function as I mention in my previous post and pasting below; do you
mean to say that I should save add-ins in same location where my document is?
but it works fine in new worksheet whenever I save.

Function adminsc(gp)

If (gp * 0.05) >= 2000 Then
adminsc = 2000
Else
adminsc = gp * 0.05
End If

End Function

 

[Dave Peterson]

Try this:

Open both the toublesome workbook and addin with the current UDF.

Make the troublesome workbook active.
Make sure you get that =name? error when you try the UDF.
Select A1 (any old cell will do)
Now insert|name|define
Create a name called: markup
(make sure you click add in that dialog)

Now close that dialog.
then delete the markup name
(Insert|name|define|select the name and click delete)

Now back to the bad formula and hit F2, followed by enter.

If you have lots of these to fix...
edit|replace
what: =
with: =
replace all

======
Do the same for the other problem UDF names, too.

Ok dave I further explain, this function of vb code I wrote in VB editor
(Insert module then write code then save as add-ins and named it to
personal.xla in Add-ins directory),now when I create a new worksheet file and
use it this formula it work fine but when I use this formula in my an
existing worksheet it display #name? error, I didn't understand same formula
works in new file but don't work in old file. I don't know what I'm doing
wrong as I'm new to vb code & I learned this function from MS help in custom
user defined functions.

 

[Guest]

Thanks Dave, You're doing great job it works fine! can you tell me one more
thing, how can I protect these add-ins codes, I tried VBA Project properties
but it can't save password. anyway thankyou again & thankyou all.

 

[Dave Peterson]

You should be able to password protect that project.

But you do have to close and reopen the addin to see it take effect.

Thanks Dave, You're doing great job it works fine! can you tell me one more
thing, how can I protect these add-ins codes, I tried VBA Project properties
but it can't save password. anyway thankyou again & thankyou all.