-
Notifications
You must be signed in to change notification settings - Fork 23
/
talk.html
294 lines (294 loc) · 27.1 KB
/
talk.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" lang="" xml:lang="">
<head>
<meta charset="utf-8" />
<meta name="generator" content="pandoc" />
<meta name="viewport" content="width=device-width, initial-scale=1.0, user-scalable=yes" />
<meta name="author" content="Keith A. Lewis" />
<title>Embed C++ in Excel</title>
<style type="text/css">
code{white-space: pre-wrap;}
span.smallcaps{font-variant: small-caps;}
span.underline{text-decoration: underline;}
div.column{display: inline-block; vertical-align: top; width: 50%;}
</style>
<style type="text/css">
a.sourceLine { display: inline-block; line-height: 1.25; }
a.sourceLine { pointer-events: none; color: inherit; text-decoration: inherit; }
a.sourceLine:empty { height: 1.2em; }
.sourceCode { overflow: visible; }
code.sourceCode { white-space: pre; position: relative; }
div.sourceCode { margin: 1em 0; }
pre.sourceCode { margin: 0; }
@media screen {
div.sourceCode { overflow: auto; }
}
@media print {
code.sourceCode { white-space: pre-wrap; }
a.sourceLine { text-indent: -1em; padding-left: 1em; }
}
pre.numberSource a.sourceLine
{ position: relative; left: -4em; }
pre.numberSource a.sourceLine::before
{ content: attr(title);
position: relative; left: -1em; text-align: right; vertical-align: baseline;
border: none; pointer-events: all; display: inline-block;
-webkit-touch-callout: none; -webkit-user-select: none;
-khtml-user-select: none; -moz-user-select: none;
-ms-user-select: none; user-select: none;
padding: 0 4px; width: 4em;
color: #aaaaaa;
}
pre.numberSource { margin-left: 3em; border-left: 1px solid #aaaaaa; padding-left: 4px; }
div.sourceCode
{ }
@media screen {
a.sourceLine::before { text-decoration: underline; }
}
code span.al { color: #ff0000; font-weight: bold; } /* Alert */
code span.an { color: #60a0b0; font-weight: bold; font-style: italic; } /* Annotation */
code span.at { color: #7d9029; } /* Attribute */
code span.bn { color: #40a070; } /* BaseN */
code span.bu { } /* BuiltIn */
code span.cf { color: #007020; font-weight: bold; } /* ControlFlow */
code span.ch { color: #4070a0; } /* Char */
code span.cn { color: #880000; } /* Constant */
code span.co { color: #60a0b0; font-style: italic; } /* Comment */
code span.cv { color: #60a0b0; font-weight: bold; font-style: italic; } /* CommentVar */
code span.do { color: #ba2121; font-style: italic; } /* Documentation */
code span.dt { color: #902000; } /* DataType */
code span.dv { color: #40a070; } /* DecVal */
code span.er { color: #ff0000; font-weight: bold; } /* Error */
code span.ex { } /* Extension */
code span.fl { color: #40a070; } /* Float */
code span.fu { color: #06287e; } /* Function */
code span.im { } /* Import */
code span.in { color: #60a0b0; font-weight: bold; font-style: italic; } /* Information */
code span.kw { color: #007020; font-weight: bold; } /* Keyword */
code span.op { color: #666666; } /* Operator */
code span.ot { color: #007020; } /* Other */
code span.pp { color: #bc7a00; } /* Preprocessor */
code span.sc { color: #4070a0; } /* SpecialChar */
code span.ss { color: #bb6688; } /* SpecialString */
code span.st { color: #4070a0; } /* String */
code span.va { color: #19177c; } /* Variable */
code span.vs { color: #4070a0; } /* VerbatimString */
code span.wa { color: #60a0b0; font-weight: bold; font-style: italic; } /* Warning */
</style>
</head>
<body>
<header id="title-block-header">
<h1 class="title">Embed C++ in Excel</h1>
<p class="author">Keith A. Lewis</p>
</header>
<h1 id="rationale">Rationale</h1>
<p>C++ is an algorithmic language. You see the code but not the data.</p>
<p>Excel is purely functional. You see the data but not the code.</p>
<p>C++ and Excel are complementary.</p>
<p>The <a href="https://github.com/xlladdins/xll">xll library</a> makes C++ code accessible to anyone using Excel on Windows.</p>
<h1 id="xlladdins.com"><a href="https://xlladdins.com">xlladdins.com</a></h1>
<ul>
<li>Call C++/C/Fortran from Excel</li>
<li>Copy, paste, insert graphs: a debugger on steroids</li>
<li>Use UTF-8 strings</li>
<li>Embed objects and use single inheritance</li>
<li>Plug in 3rd party libraries</li>
<li>Integrate with Excel help documentation</li>
</ul>
<h1 id="addin">AddIn</h1>
<p>Specify the information Excel needs to call your function.</p>
<div class="sourceCode" id="cb1"><pre class="sourceCode c++"><code class="sourceCode cpp"><a class="sourceLine" id="cb1-1" title="1"><span class="pp">#include </span><span class="im">"xll/xll.h"</span></a>
<a class="sourceLine" id="cb1-2" title="2"><span class="kw">using</span> <span class="kw">namespace</span> xll;</a>
<a class="sourceLine" id="cb1-3" title="3"></a>
<a class="sourceLine" id="cb1-4" title="4">AddIn xai_tgamma(</a>
<a class="sourceLine" id="cb1-5" title="5"> Function(XLL_DOUBLE, <span class="st">"xll_tgamma"</span>, <span class="st">"TGAMMA"</span>)</a>
<a class="sourceLine" id="cb1-6" title="6"> .Arguments({</a>
<a class="sourceLine" id="cb1-7" title="7"> Arg(XLL_DOUBLE, <span class="st">"x"</span>, <span class="st">"is the value for which you want to calculate Gamma."</span>)</a>
<a class="sourceLine" id="cb1-8" title="8"> })</a>
<a class="sourceLine" id="cb1-9" title="9"> .FunctionHelp(<span class="st">"Return the Gamma function value."</span>)</a>
<a class="sourceLine" id="cb1-10" title="10"> .Category(<span class="st">"MATH"</span>)</a>
<a class="sourceLine" id="cb1-11" title="11"> .HelpTopic(<span class="st">"https://docs.microsoft.com/en-us/cpp/c-runtime-library/reference/tgamma-tgammaf-tgammal"</span>)</a>
<a class="sourceLine" id="cb1-12" title="12">);</a></code></pre></div>
<p>This shows up in the <code>MATH</code> category of the function wizard as:</p>
<p><img src="images/tgamma.png" alt="Function Wizard dialog"></p>
<p>Click <a href="https://docs.microsoft.com/en-us/cpp/c-runtime-library/reference/tgamma-tgammaf-tgammal">Help on this function</a> to open the help topic URL in a browser.</p>
<p>Implement <code>xll_tgamma</code></p>
<div class="sourceCode" id="cb2"><pre class="sourceCode c++"><code class="sourceCode cpp"><a class="sourceLine" id="cb2-1" title="1"><span class="pp">#include </span><span class="im"><cmath></span><span class="pp"> </span><span class="co">// for double tgamma(double)</span></a>
<a class="sourceLine" id="cb2-2" title="2"></a>
<a class="sourceLine" id="cb2-3" title="3"><span class="dt">double</span> WINAPI xll_tgamma(<span class="dt">double</span> x)</a>
<a class="sourceLine" id="cb2-4" title="4">{</a>
<a class="sourceLine" id="cb2-5" title="5"><span class="pp">#pragma XLLEXPORT</span></a>
<a class="sourceLine" id="cb2-6" title="6"> <span class="cf">return</span> tgamma(x);</a>
<a class="sourceLine" id="cb2-7" title="7">}</a></code></pre></div>
<p>Use <code>WINAPI</code> for functions called by Excel if you don’t like debugging corrupt call stack crashes.</p>
<p>Export <code>xll_tgamma</code> from the dll with <code>#pragma XLLEXPORT</code>. If you forget you will get a warning when the add-in is opened.</p>
<h1 id="macros">Macros</h1>
<p>Macros take no arguments, they only produce side effects.</p>
<div class="sourceCode" id="cb3"><pre class="sourceCode c++"><code class="sourceCode cpp"><a class="sourceLine" id="cb3-1" title="1"><span class="co">// Alt-F8, XLL.MACRO calls xll_macro</span></a>
<a class="sourceLine" id="cb3-2" title="2">AddIn xai_macro(Macro(<span class="st">"xll_macro"</span>, <span class="st">"XLL.MACRO"</span>));</a>
<a class="sourceLine" id="cb3-3" title="3"><span class="dt">int</span> WINAPI xll_macro(<span class="dt">void</span>)</a>
<a class="sourceLine" id="cb3-4" title="4">{</a>
<a class="sourceLine" id="cb3-5" title="5"><span class="pp">#pragma XLLEXPORT</span></a>
<a class="sourceLine" id="cb3-6" title="6"> Excel(xlcAlert, </a>
<a class="sourceLine" id="cb3-7" title="7"> Excel(xlfConcatenate,</a>
<a class="sourceLine" id="cb3-8" title="8"> OPER(<span class="st">"XLL.MACRO 召唤 with активный cell: "</span>), <span class="co">// use utf-8</span></a>
<a class="sourceLine" id="cb3-9" title="9"> Excel(xlfReftext, </a>
<a class="sourceLine" id="cb3-10" title="10"> Excel(xlfActiveCell), </a>
<a class="sourceLine" id="cb3-11" title="11"> OPER(<span class="kw">true</span>) <span class="co">// A1 style instead of R1C1</span></a>
<a class="sourceLine" id="cb3-12" title="12"> )</a>
<a class="sourceLine" id="cb3-13" title="13"> ),</a>
<a class="sourceLine" id="cb3-14" title="14"> OPER(<span class="dv">2</span>), <span class="co">// general information</span></a>
<a class="sourceLine" id="cb3-15" title="15"> OPER(<span class="st">"https://xlladdins.github.io/Excel4Macros/alert.html"</span>)</a>
<a class="sourceLine" id="cb3-16" title="16"> );</a>
<a class="sourceLine" id="cb3-17" title="17"> </a>
<a class="sourceLine" id="cb3-18" title="18"> <span class="cf">return</span> TRUE;</a>
<a class="sourceLine" id="cb3-19" title="19">}</a></code></pre></div>
<p><code>Excel(xlfFunction, args, ...)</code> calls the Excel <b>f</b>unction <code>=FUNCTION(args, ...)</code>.</p>
<p><code>Excel(xlcMacro, args, ...)</code> executes the <b>m</b>acro <code>MACRO(args, ...)</code>. This can only be used from macros, not functions. Consult the <a href="https://xlladdins.github.io/Excel4Macros/">Excel4Macros</a> documentation to discern the appropriate arguments.</p>
<h1 id="oper"><code>OPER</code></h1>
<p>An <code>OPER</code> is a class that corresponds to an Excel cell. It can be a number, string, error, boolean, …, or a 2-dimensional range of cells.</p>
<p>It is a variant that satisfies the <a href="https://en.cppreference.com/w/cpp/concepts/regular"><code>std::regular</code></a> concept. <code>OPER o(1.23)</code> is the number <code>1.23</code>. Assigning a string, <code>o = "foo"</code>, or boolean, <code>o = true</code>, turns it into a string or boolean. The <code>xltype</code> member of <code>OPER</code> indicates the type defined in <a href="https://github.com/xlladdins/xll/blob/master/xll/XLCALL.H">Microsoft Excel SDK header file</a> as <code>xltypeNum</code>, <code>xltypeStr</code>, <code>xltypeErr, xltypeBool</code>, …, <code>xltypeMulti</code>.</p>
<p>Excel is <a href="https://devopedia.org/postel-s-law">Postel</a> in what arguments it accepts from functions. Any <code>XLL_</code><em><code>TYPE</code></em> can be used to leverage Excel type checking. If there is an argument type mismatch <code>#VALUE!</code> will be returned by Excel before the function is called.</p>
<p>!!! link to XLL_TYPE</p>
<h1 id="xllhandle"><code>xll::handle</code></h1>
<p>A <code>xll::handle<T></code> has a pointer to an object of type <code>T</code> and behaves like <code>std::unique_ptr<T></code>. The constructor <code>xll::handle<T> h(new T(args...))</code> stores the pointer returned by <code>new</code>. It refers to exactly one object and calls <code>delete</code> on the object when it goes out of scope. Its <code>ptr()</code> and arrow <code>operator->()</code> member functions return the pointer to the object. Use the <code>get()</code> member function to return a <code>HANDLEX</code> value that can be used in Excel.</p>
<h1 id="handlex"><code>HANDLEX</code></h1>
<p>A <code>HANDLEX</code> is a 64-bit IEEE double. Its bits are the same bits as the pointer. Converting a <code>HANDLEX</code> to a pointer and back is a cast that only takes a few machine instructions instead of a lookup in an associative array. On 64-bit Windows 10 the first 16-bits of a pointer are zero so we only need the remaining 48-bits. Doubles can exactly represent integers less than 2<sup>53</sup> so there is plenty of room to spare.</p>
<p>The constructor <code>xll::handle<T>(HANDLEX)</code> converts a <code>HANDLEX</code> to a pointer. If the <code>HANDLEX</code> was not created by a prior call to <code>xll::handle<T>(T*)</code> a null pointer is returned. Use <code>explicit xll::handle<T>::operator bool() const</code> to detect that.</p>
<h1 id="example">Example</h1>
<p>Generic C++ class holding a regular type.</p>
<div class="sourceCode" id="cb4"><pre class="sourceCode c++"><code class="sourceCode cpp"><a class="sourceLine" id="cb4-1" title="1"><span class="co">// base.h</span></a>
<a class="sourceLine" id="cb4-2" title="2"><span class="pp">#include </span><span class="im"><concepts></span></a>
<a class="sourceLine" id="cb4-3" title="3"></a>
<a class="sourceLine" id="cb4-4" title="4"><span class="kw">template</span><<span class="bu">std::</span>regular T></a>
<a class="sourceLine" id="cb4-5" title="5"><span class="kw">class</span> base {</a>
<a class="sourceLine" id="cb4-6" title="6"> T t;</a>
<a class="sourceLine" id="cb4-7" title="7"><span class="kw">public</span>:</a>
<a class="sourceLine" id="cb4-8" title="8"> base()</a>
<a class="sourceLine" id="cb4-9" title="9"> { }</a>
<a class="sourceLine" id="cb4-10" title="10"> base(<span class="at">const</span> T& t) </a>
<a class="sourceLine" id="cb4-11" title="11"> : t(t) </a>
<a class="sourceLine" id="cb4-12" title="12"> { }</a>
<a class="sourceLine" id="cb4-13" title="13"> <span class="kw">virtual</span> ~base() </a>
<a class="sourceLine" id="cb4-14" title="14"> { }</a>
<a class="sourceLine" id="cb4-15" title="15"> T get() <span class="at">const</span></a>
<a class="sourceLine" id="cb4-16" title="16"> {</a>
<a class="sourceLine" id="cb4-17" title="17"> <span class="cf">return</span> t; </a>
<a class="sourceLine" id="cb4-18" title="18"> }</a>
<a class="sourceLine" id="cb4-19" title="19">};</a></code></pre></div>
<p>Embed <code>base<OPER></code> in Excel.</p>
<div class="sourceCode" id="cb5"><pre class="sourceCode c++"><code class="sourceCode cpp"><a class="sourceLine" id="cb5-1" title="1"><span class="co">// xll_base.cpp</span></a>
<a class="sourceLine" id="cb5-2" title="2"><span class="pp">#include </span><span class="im">"base.h"</span></a>
<a class="sourceLine" id="cb5-3" title="3"><span class="pp">#include </span><span class="im">"xll/xll.h"</span></a>
<a class="sourceLine" id="cb5-4" title="4"></a>
<a class="sourceLine" id="cb5-5" title="5"><span class="kw">using</span> <span class="kw">namespace</span> xll;</a>
<a class="sourceLine" id="cb5-6" title="6"></a>
<a class="sourceLine" id="cb5-7" title="7">AddIn xai_base(</a>
<a class="sourceLine" id="cb5-8" title="8"> Function(XLL_HANDLEX, <span class="st">"xll_base"</span>, <span class="st">"</span><span class="sc">\\</span><span class="st">XLL.BASE"</span>)</a>
<a class="sourceLine" id="cb5-9" title="9"> .Arguments({</a>
<a class="sourceLine" id="cb5-10" title="10"> Arg(XLL_LPOPER, <span class="st">"cell"</span>, <span class="st">"is a cell."</span>))</a>
<a class="sourceLine" id="cb5-11" title="11"> })</a>
<a class="sourceLine" id="cb5-12" title="12"> .Uncalced() <span class="co">// \XLL.BASE has a side effect</span></a>
<a class="sourceLine" id="cb5-13" title="13">);</a>
<a class="sourceLine" id="cb5-14" title="14">HANDLEX WINAPI xll_base(LPOPER po)</a>
<a class="sourceLine" id="cb5-15" title="15">{</a>
<a class="sourceLine" id="cb5-16" title="16"><span class="pp">#pragma XLLEXPORT</span></a>
<a class="sourceLine" id="cb5-17" title="17"> handle<base<OPER>> h(<span class="kw">new</span> base(*po));</a>
<a class="sourceLine" id="cb5-18" title="18"> </a>
<a class="sourceLine" id="cb5-19" title="19"> <span class="cf">return</span> h.get();</a>
<a class="sourceLine" id="cb5-20" title="20">}</a></code></pre></div>
<p>A <code>LPOPER</code> is a pointer to an <code>OPER</code>.</p>
<p>Call a member function.</p>
<div class="sourceCode" id="cb6"><pre class="sourceCode c++"><code class="sourceCode cpp"><a class="sourceLine" id="cb6-1" title="1">AddIn xai_base_get(</a>
<a class="sourceLine" id="cb6-2" title="2"> Function(XLL_LPOPER, <span class="st">"xll_base_get"</span>, <span class="st">"XLL.BASE.GET"</span>)</a>
<a class="sourceLine" id="cb6-3" title="3"> .Arguments({</a>
<a class="sourceLine" id="cb6-4" title="4"> Arg(XLL_HANDLEX, <span class="st">"handle"</span>, <span class="st">"is a handle to a base<OPER> object."</span>))</a>
<a class="sourceLine" id="cb6-5" title="5"> })</a>
<a class="sourceLine" id="cb6-6" title="6">);</a>
<a class="sourceLine" id="cb6-7" title="7">LPOPER WINAPI xll_base_get(HANDLEX h)</a>
<a class="sourceLine" id="cb6-8" title="8">{</a>
<a class="sourceLine" id="cb6-9" title="9"><span class="pp">#pragma XLLEXPORT</span></a>
<a class="sourceLine" id="cb6-10" title="10"> handle<base<OPER>> <span class="va">h_</span>(h);</a>
<a class="sourceLine" id="cb6-11" title="11"> </a>
<a class="sourceLine" id="cb6-12" title="12"> <span class="cf">return</span> <span class="va">h_</span> ? &<span class="va">h_</span>->get() : (LPOPER)ErrValue; </a>
<a class="sourceLine" id="cb6-13" title="13">}</a></code></pre></div>
<p>Arguments and return values using <code>OPER</code>s are passed as pointers. Note <code>h.get()</code> returns the <code>HANDLEX</code> that <code>h_->get()</code> uses to call the member function. If the handle did not come from a previous call to <code>\XLL.BASE</code> then <code>#VALUE!</code> is returned. Returning the address of <code>h_->get()</code> assumes the handle lives beyond the function return.</p>
<h1 id="single-inheritence">Single inheritence</h1>
<p>When creating a handle to an object <code>U</code> that is derived from <code>T</code> use <code>xll::handle<T> h(new U(args...))</code> and return the <code>HANDLEX</code> with <code>h.get()</code>. The handle can be used to call any member function of <code>T</code>. To call member functions of <code>U</code> use <code>dynamic_cast</code>. The convenience function <code>xll::handle<T>::as<U>()</code> does the dynamic cast for you.</p>
<div class="sourceCode" id="cb7"><pre class="sourceCode c++"><code class="sourceCode cpp"><a class="sourceLine" id="cb7-1" title="1"><span class="co">// derived.h</span></a>
<a class="sourceLine" id="cb7-2" title="2"><span class="pp">#include </span><span class="im">"base.h"</span></a>
<a class="sourceLine" id="cb7-3" title="3"></a>
<a class="sourceLine" id="cb7-4" title="4"><span class="kw">template</span><<span class="bu">std::</span>regular T></a>
<a class="sourceLine" id="cb7-5" title="5"><span class="kw">class</span> derived : <span class="kw">public</span> base<T> {</a>
<a class="sourceLine" id="cb7-6" title="6"> T t2;</a>
<a class="sourceLine" id="cb7-7" title="7"><span class="kw">public</span>:</a>
<a class="sourceLine" id="cb7-8" title="8"> derived()</a>
<a class="sourceLine" id="cb7-9" title="9"> { }</a>
<a class="sourceLine" id="cb7-10" title="10"> derived(<span class="at">const</span> T& t, <span class="at">const</span> T& t2) </a>
<a class="sourceLine" id="cb7-11" title="11"> : base<T>(t), t2(t2)</a>
<a class="sourceLine" id="cb7-12" title="12"> { }</a>
<a class="sourceLine" id="cb7-13" title="13"> ~derived() </a>
<a class="sourceLine" id="cb7-14" title="14"> { }</a>
<a class="sourceLine" id="cb7-15" title="15"> T get2() <span class="at">const</span></a>
<a class="sourceLine" id="cb7-16" title="16"> {</a>
<a class="sourceLine" id="cb7-17" title="17"> <span class="cf">return</span> t2; </a>
<a class="sourceLine" id="cb7-18" title="18"> }</a>
<a class="sourceLine" id="cb7-19" title="19">};</a></code></pre></div>
<div class="sourceCode" id="cb8"><pre class="sourceCode c++"><code class="sourceCode cpp"><a class="sourceLine" id="cb8-1" title="1"><span class="co">// xll_derived.cpp</span></a>
<a class="sourceLine" id="cb8-2" title="2"><span class="pp">#include </span><span class="im">"derived.h"</span></a>
<a class="sourceLine" id="cb8-3" title="3"><span class="pp">#include </span><span class="im">"xll/xll.h"</span></a>
<a class="sourceLine" id="cb8-4" title="4"></a>
<a class="sourceLine" id="cb8-5" title="5"><span class="kw">using</span> <span class="kw">namespace</span> xll;</a>
<a class="sourceLine" id="cb8-6" title="6"></a>
<a class="sourceLine" id="cb8-7" title="7">AddIn xai_derived(</a>
<a class="sourceLine" id="cb8-8" title="8"> Function(XLL_HANDLEX, <span class="st">"xll_derived"</span>, <span class="st">"</span><span class="sc">\\</span><span class="st">XLL.DERIVED"</span>)</a>
<a class="sourceLine" id="cb8-9" title="9"> .Arguments({</a>
<a class="sourceLine" id="cb8-10" title="10"> Arg(XLL_LPOPER, <span class="st">"cell"</span>, <span class="st">"is a cell passed to base<OPER>. ))</span></a>
<a class="sourceLine" id="cb8-11" title="11"> Arg(XLL_LPOPER, <span class="st">"cell2"</span>, <span class="st">"is a cell passed to derived<OPER>."</span>))</a>
<a class="sourceLine" id="cb8-12" title="12"> })</a>
<a class="sourceLine" id="cb8-13" title="13"> .Uncalced() <span class="co">// \XLL.DERIVED has a side effect</span></a>
<a class="sourceLine" id="cb8-14" title="14">);</a>
<a class="sourceLine" id="cb8-15" title="15">HANDLEX WINAPI xll_derived(LPOPER po, LPOPER po2)</a>
<a class="sourceLine" id="cb8-16" title="16">{</a>
<a class="sourceLine" id="cb8-17" title="17"><span class="pp">#pragma XLLEXPORT</span></a>
<a class="sourceLine" id="cb8-18" title="18"> handle<base<OPER>> h(<span class="kw">new</span> derived<OPER>(*po, *po2));</a>
<a class="sourceLine" id="cb8-19" title="19"> </a>
<a class="sourceLine" id="cb8-20" title="20"> <span class="cf">return</span> h.get();</a>
<a class="sourceLine" id="cb8-21" title="21">}</a>
<a class="sourceLine" id="cb8-22" title="22"></a>
<a class="sourceLine" id="cb8-23" title="23">AddIn xai_derived_get2(</a>
<a class="sourceLine" id="cb8-24" title="24"> Function(XLL_LPOPER, <span class="st">"xll_derived_get2, "</span>XLL.DERIVED.GET2<span class="st">")</span></a>
<a class="sourceLine" id="cb8-25" title="25"> .Arguments({</a>
<a class="sourceLine" id="cb8-26" title="26"> Arg(XLL_HANDLEX, <span class="st">"handle"</span>, <span class="st">"is a handle to a derived<OPER> object."</span>))</a>
<a class="sourceLine" id="cb8-27" title="27"> })</a>
<a class="sourceLine" id="cb8-28" title="28">);</a>
<a class="sourceLine" id="cb8-29" title="29">LPOPER WINAPI xll_derived_get2(HANDLEX h)</a>
<a class="sourceLine" id="cb8-30" title="30">{</a>
<a class="sourceLine" id="cb8-31" title="31"><span class="pp">#pragma XLLEXPORT</span></a>
<a class="sourceLine" id="cb8-32" title="32"> handle<base<OPER>> <span class="va">h_</span>(h);</a>
<a class="sourceLine" id="cb8-33" title="33"> <span class="cf">if</span> (!<span class="va">h_</span>) {</a>
<a class="sourceLine" id="cb8-34" title="34"> <span class="cf">return</span> (LPOPER)ErrNA; <span class="co">// #N/A</span></a>
<a class="sourceLine" id="cb8-35" title="35"> }</a>
<a class="sourceLine" id="cb8-36" title="36"></a>
<a class="sourceLine" id="cb8-37" title="37"> <span class="kw">auto</span> h2 = <span class="va">h_</span>.as<derived<OPER>>();</a>
<a class="sourceLine" id="cb8-38" title="38"> </a>
<a class="sourceLine" id="cb8-39" title="39"> <span class="cf">return</span> h2 ? h2->get2() : (LPOPER)ErrValue;</a>
<a class="sourceLine" id="cb8-40" title="40">}</a></code></pre></div>
<p>Now you know how to embed C++ in Excel and use single inheritance.</p>
<h1 id="calling-delete">Calling <code>delete</code></h1>
<p>Excel add-ins dealing with C++ objects typically have an “object manager” that implements a garbage collector. The <code>xll::handle</code> class uses the cell containing the handle to do garbage collection.</p>
<p>The <code>xll::handle</code> class will call <code>delete</code> on objects created by <code>xll::handle<T>(T*)</code> when called from a cell containing a <code>HANDLEX</code> from a previous call. The constructor is being provided with new arguments so the old object is no longer required.</p>
<p>Moving, copying, and pasting cells containing handles will not leak. If you are dead set on leaking memory then create a handle in a cell and delete it.</p>
<p>If a function that creates a handle is used as an argument to another function then there is no cell containing a handle that can be used to call delete. The <code>xll::handle(T*)</code> constructor keeps track of these temporary handles. The outer calling function can use them, and make a copy if necessary, but they are deleted when the outer function exits.</p>
<p><strong>Exercise</strong>. <em>Figure out how <code>XLL.BASE.GET</code> can leak</em>.</p>
<p><em>Hint</em>. See <a href="https://github.com/xlladdins/xll/blob/master/test/handle.cpp"><code>handle.cpp</code></a>.</p>
<h1 id="calling-new">Calling <code>new</code></h1>
<p>The flip side is when you save a spreadsheet with handles. When opened in a new Excel session the handles need to be refreshed. Use <code>Ctrl-Alt-F9</code> to call <code>new</code> on all the handles. This can also be used for garbage collection in case you deleted a cell immediately after creating a handle.</p>
<h2 id="remarks">Remarks</h2>
<p>It only takes a couple of lines of code to call functions or embed C++ objects in Excel using the xll add-in library. Tell Excel what to return, the C++ function to call, and the name you want Excel to use. Specify the arguments the function takes and how you want them to show up in the Function Wizard.</p>
<p>The <code>WINAPI</code> function called by Excel should be a thin wrapper that massages the Excel data type arguments to what you need to pass to the platform-independent code, then fluff return values back to what Excel wants. Specifying <code>.Category</code>, <code>.FunctionHelp</code>, and <code>.HelpTopic</code> are optional.</p>
<p>Once you do this all of the functionality of Excel is available to you. You can copy and paste to your heart’s content and insert graphs to <del>break things</del> get a better picture of data returned by your code.</p>
<p>The latest version of Excel has <a href="https://insider.office.com/en-us/blog/dynamic-arrays-and-new-functions-in-excel">dynamic arrays</a>. No more selecting a guess at the output range, <code>F2</code> and <code>Ctrl-Shift-Enter</code>, then <code>Ctrl-Z</code> to back off. The entire array is plopped into Excel as the return value. Use <code>A1#</code> to refer to it in function calls, where <code>A1</code> is the upper left corner of the array. Excel will pass the current size of the dynamic array to your function.</p>
<p>As Bjarne said, “There are two kinds of languages…” Complaining about Excel does not change the number of people who use that as a tool to get their work done. The xll library makes it easy to deliver a self contained add-in to anyone using Excel on Windows. If you have the quality problem of people actually using the code you write it is possible to provide more detailed <a href="https://github.com/xlladdins/xll/blob/master/README.md#documentation">documentation</a>. Spend your precious time explaining it once in a form you can point your customers to, then get back to writing cool stuff that even more people might want to use.</p>
</body>
</html>