Paste Function

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

Sorry to repost this but my internet went haywire and couldn't see if I'd
received a reply.
Rob

I notice that in the paste function box that there are over 40 in the user
defined section.
Half of them are duplicates.
1. I presume they are functions from add-ins??
2. Why the duplicates?
3. Can the duplicates be deleted and if so, how?

I would also like to know how best to use a User defined function that I'm
currently using in a workbook (that was created through this NG - thanks
Dave P.). I notice that if I'm debugging a procedure, that that process
will also run that function.
As I've stored the function in the actual wkbk to make sure that the wkbk
always has access to it, is there a better way to do this so that it is
calculated the same way as other built-in functions (or are the built-ins
just doing it behind the scenes).
 
Hi
for your first question:
- yes they are probably from Add-Ins
- Duplicates: maybe you have installed two similar Add-Ins (different
versions). Difficult to tell from this point. you may post some names
of duplicated functions. also check you installed Add-ins to see which
ones are installed

Second question:
- you may store the UDF in your personal.xlt workbook to have access to
it from all workbooks
 
You definitely get duplicates if you have both Analysis
Toolpak and Analysis Toolpak-VBA in use.
 
Don't use either of those! But did make me check what I was using and the
culprits are AutoSafe and autoSafe -VBA
Thank you,
Rob
 
Thanks Frank, but doing that, if I give the file to someone else then they
won't have that function available to them unless I ship the Personal.xls
with it. (And I do presume you mean .xls and not xlt.) Or am I mistaken
here??

In regard to the duplicates, you're right. They are from 2 different
autosafe add-ins.

Rob
 
Hi Rob
yes .xls was what I meant. Another option would be to create a specific
Add-in (though the user has to install this)
 
That's good. Thanks.
Rob

Frank Kabel said:
Hi Rob
yes .xls was what I meant. Another option would be to create a specific
Add-in (though the user has to install this)
Click to expand...
 
Are the duplicates like NETWORKDAYS and networkdays (notice the change in
case)? Both of these are functions in the Analysis toolpak (and analysis
toolpak VBA) addins.

The uppercase version is from the analysis toolpak (used within excel itself).

The lowercase version is from the VBA version (used with your code).

So don't delete them. (If you don't use an addin, just deselect it in
Tools|Addins.)

My take on where to store the UDF.

Is a single UDF used in a single workbook and that workbook doesn't get
propogated to other places?

If yes, I don't see a problem with putting it that workbook.

But if your workbook travels (copies all over the place) that take on a life of
their own, then it really becomes a mess when you have to update 17 copies of
the same macro. (and still not get them all!)

I don't think I'd store a UDF in my Personal.xl* file that was meant for others
to use. I'd have to share my personal.xl* workbook with others and my file
would have to become their own Personal.xl* file.

And if they already have their own versions, they won't be happy to manage the
common names.

If you put the UDF in an addin (a shared drive), you could just have a reference
to your workbook to that addin.

Rename the addin's project name from VBAProject to something unique.

Then have both the addin and your workbook open and with your workbook's project
active in the VBE, tools|references and point at the addin.

Then each time someone opens that workbook, they'll get the addin loaded
automatically.
 
Thankyou for that comprehensive explanation Dave.
Firstly, the duplicates are from 2 add-ins, namely autosafe and
autosafe-VBA.

Secondly, with the UDF, it's the one you created for me called
"MakeAcronym", which BTW works a treat! I'm sure it's because I have a lot
of Worksheet_Change events happening on the one sheet that also has that
function on it. Each time the wkbk calculates, and running some userforms,
that UDF is being recalculated. I notice that if an error occurs in any
procedure, that there is an error in the cells that contain that function.
(I know, make sure all errors are trapped properly.)
I guess there's no satisfactory alternative to stop the function from
recalculating every time is there?
I can't delete the function as the wkbk is slightly volatile, in that
certain cell values can change that that function refers to.

Even if I convert it to an add-in, it's still going to behave the same way??

Anyway, it's not a huge problem. I only notice it calculating when I'm
debugging and it seems to stop the debugging process sometimes. I can just
temporarily halt it.
Is there a simple halt command you can use to disable a complete procedure?

Rob
 
I don't recall the makeAcronym being volatile, but if it recalculates, then
excel thinks it should!

If your worksheet events are changing the cells that the function uses, then it
would recalculate, too.

And I would guess that the same code in an addin would do the same thing.

Time to make sure all errors are trapped someway.

Charles Williams has a lot more notes on calculation secrets at his site:

http://www.decisionmodels.com/calcsecrets.htm
 
My reference to volatile was to the worksheet, Dave, not the function.
I wouldn't have a clue whether your UDF is volatile or not.
If you can't remember the UDF, here it is...

Function MakeAcronym(rng As Range) As String
Dim StrIn As String
Dim StrOut As String
Dim iCtr As Long
Set rng = rng(1)
StrIn = " " & Application.Trim(rng.Value)
StrOut = ""
For iCtr = 1 To Len(StrIn) - 1
If Mid(StrIn, iCtr, 1) = " " Then
StrOut = StrOut & Mid(StrIn, iCtr + 1, 1)
End If
Next iCtr
MakeAcronym = StrOut
End Function

I use this UDF on a few sheets and it seems that excel needs to calculate it
in the course of doing something on the worksheet that has
Worksheet_Change(ByVal Target As Excel.Range)
and
Sub Worksheet_SelectionChange(ByVal Target As Range)
events on it.

Rob
 
From VBA's help for application.volatile:

Marks a user-defined function as volatile. A volatile function must be
recalculated whenever calculation occurs in any cells on the worksheet. A
nonvolatile function is recalculated only when the input variables change. This
method has no effect if it's not inside a user-defined function used to
calculate a worksheet cell.

So I think we're speaking about the same thing!

For some reason, excel wants to recalculate it--even though nothing may have
changed (in my eyes).

I'm not smart enough to the whys and wherefores of excel's calculation routine.
I weaselled out by pointing at Charles Williams' site.

I know I've experienced the same thing and I just let excel be excel.

===
Have you tried turning calculation to manual, then run your code, then set it
back to what it was when you started?



rob said:
My reference to volatile was to the worksheet, Dave, not the function.
I wouldn't have a clue whether your UDF is volatile or not.
If you can't remember the UDF, here it is...

Function MakeAcronym(rng As Range) As String
Dim StrIn As String
Dim StrOut As String
Dim iCtr As Long
Set rng = rng(1)
StrIn = " " & Application.Trim(rng.Value)
StrOut = ""
For iCtr = 1 To Len(StrIn) - 1
If Mid(StrIn, iCtr, 1) = " " Then
StrOut = StrOut & Mid(StrIn, iCtr + 1, 1)
End If
Next iCtr
MakeAcronym = StrOut
End Function

I use this UDF on a few sheets and it seems that excel needs to calculate it
in the course of doing something on the worksheet that has
Worksheet_Change(ByVal Target As Excel.Range)
and
Sub Worksheet_SelectionChange(ByVal Target As Range)
events on it.

Rob
Click to expand...
 
Thanks Dave. I've forgotten exactly why I needed this problem fixed so
won't trouble you more with it.
Rob
 
Back
Top