[VBA] Replace User-defined function's result with its value

[robs23]

Hi Guys,

This is my first post here so I'll start with 'Hello' to everyone!
I created a couple of custom functions and store them in xlam file saved as Excel's add-in. It lets me use all of custom functions in every workbook on my computer - that's cool. The problem is, however, when I send those workbooks to someone via mail. Instead of correct result of custom function they would display error because xlam add-in with function's definition is not present on remote computer. I'm looking for a way to replace all the cells that contains custom function with their value. It would be great if this could be done from within the fuction - e.g. the code in function would replace cell with value. Unfortunately this brings circular reference warning. It makes sense - the function can't replace itself with value during runtime. What else can I do? I want it to be extra-simple as there are is few inexperienced users using my functions that would find it difficult to e.g. fire a replacing macro at end of the work.

I'll be grateful for help.

Robert

 

[UWMmakow]

I think you'd want a macro that runs on Workbook_Open and checks for X add-in, and if it isn't present, then replace each formula with .value=.value

[FONT=courier new][COLOR=#00007F]If[/COLOR][COLOR=#333333] AddIns("Add-In Title Here").Installed Then[/COLOR]
    For Each cell in Sheets("SheetNameHere").Range("RangeWithFormulaHere")
         cell.Value = cell.Value
    End With
End If[/FONT]

You could use multiple of these If statements if you have multiple add-ins/ranges of formulas.

 

[robs23]

Hi UWMmakow,

Very good idea, thanks. Didn't you meant workbook_close event, though? If that's so, the problem is I would have to have such close event in every workbook that makes use of my xlam add-in. That's impossible. I thought about placing such Workbook_close event within xlam itself, but then it gets triggered only if you close the last workbook. In other words, if you have 2 workbooks currently open, closing the first wouldn't trigger the event because xlam won't be closed as long as at least 1 workbook is open. Is there any workaround?

Robert

 

[RoryA]

Have you tried using the Edit-Links, Break Link function?

 

[robs23]

Hi RoryA,

I don't know exactly this edit-links function but I think it is designed to fix not-working links. So e.g. if I sent an excel file to someone and he found some links didn't work, he would be able to trigger this function in order to fix them. I think that I wouldn't work in my scenario, as file recipients don't usually have my xclam file present on their machine and hence wouldn't be able to fix the link to function's definition.
If I got it wrong, please amplify what you meant.

Robert

 

[RoryA]

If you break the link Excel will replace the formulas with values and you can then send out the file.

 

[robs23]

Have checked it and works as decribed, thank you for tip. The downside is that the user would have to remember to break links at the end of preparing file that makes use of custom functions. I'd like to avoid such necessity or at least be able to display prompt at workbook closure if user would like to convert links. I know I'm pretty much demanding, sorry for that.

Robert