Basic Question - Sharing Functions

[Stacko]

Can you give me some pointers to sharing code?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Let me first say, I’m interested in Excel and its programming capabilities and not VBA.
<o></o>
Taking a very simple example, let’s say I have a function Add2 that I wish to make available to who ever wants to call it. So I create Add2.xls which has A1 : 0, A2 : 0 and A3 : =IF(A1=0,"Error",(IF(A2=0,"Error",(A1+A2)))) .
<o></o>
I can’t think how to ‘call’ Add2 from my application. I can get the value from A3 but I can’t put my values into A1 and A2. I assume therefore I have to copy Add2 into my application. However when I do that, it retains references to Add2 rather than my spreadsheet.
<o></o>
I know this is a very basic question but I haven’t seen anything that talks about code re-use unless it is either VBA or someone trying to extend Excel in some hypothetical way.

 

[wigi]

For these kind of things, you do need VBA.

For instance, a so-called User Defined Function will be a function like Add2, but it is stored in a module in VBA. That module should be shared to users, either in an Excel file, an Excel add-in or even other ways.

 

[Stacko]

The trouble with VBA is people just resort to it all the time because they can't figure out how to achieve things functionally. I don't know if it's still true but when relational databases came out, for 20 years you'd see programmers religiously pulling out data, putting it into matrices and then looping through in Cobol. They didn't get SQL.

Solution Number 1 is the bedrock of Excel: intelligent-copy-and-paste. Every time someone changes Add2, they send an email to every other programmer to tell them to re-copy Add2 into their sheets. Can we think of an improvement upon that?

 

[wigi]

Can we think of an improvement upon that?

Yes, Excel add-ins, stored with an extension .xla or .xlam, for instance.

 

[Stacko]

Looking at Excel 2003 Bible, it says add-ins are useless unless they contain VBA. It's only the VBA that you can use.

Is that not accurate?

 

[wigi]

I do not know what you have against the use of VBA code, but I'd advise you to Google for for instance "excel com addins". Might be interesting reading.

 

[shg]

Code and VBA are effectively synonymous in Excel.

Perhaps you're talking about sharing named formulas, which is possible, but each user would need to adjust references.

 

[Stacko]

wigi

Surely COM add-ins are .NET?

I'm trying to avoid VBA because it is a whole different ballgame. It excludes 90% of the world's programmers who know how to do spreadsheets but don't know traditional imperative programming languages. It also falls outside the dependency graph of Excel so it can't be split across multiple cores like the native Excel functions and operators.

shg

A lot of operations can be carried out without VBA - operators, functions, filters, sorts, pivots etc. These are code in that you write them your self or configure them in the case of filters etc. The issue is where you decide you can't use combinations of in-built operations and resort to VBA. If you look on this forum, you will regularly see people writing lengthy VBA procedures that someone else points out can be done with a handful of in-built functions strung together.

I'm just trying to see how far you can push Excel before falling back into procedural coding habits.