VBA to C: Pratfalls and Perils XLDevC London 2012

VBA to C: Pratfalls and PerilsXLDevC London 2012 Charles Williams Decision Models 1996 Author of FastExcel Co-author of Name Manager Charles@DecisionModels.com www.DecisionModels.com VBA to C - Pratfalls and Perils

VBA to C: Pratfalls & Perils • FastExcel functions • 8000 lines of VBA/VB6 Automation UDFs • Excel 2010 • 64-bit • Multi-threading/ multiple cores • VB6 is dead-end • My journey from VBA to C VBA to C - Pratfalls and Perils

Expectation • Steep Learning Curve • Memory Management • Pointers • Low level Language • Excel SDK • Buggy • Poorly documented • Uses complex Oper data structure VBA to C - Pratfalls and Perils

So minimise learning curve using • Planatech XLL+ • Visual Studio 2010 • C++ Primer Plus • Stephen Prata • XL Add-in Development in C++ • Steve Dalton VBA to C - Pratfalls and Perils

XLL+ Function Wizard VBA to C - Pratfalls and Perils

C Code with Equivalent VBA double AvTolXLL2_Impl(const CXlOper* theNumbers, double theTolerance) { // End of generated code //}}XLP_SRC // TODO - set the value of xloResult, or return another value // using CXlOper::RetXXX() or throw a CXlRuntimeException. RW12 nRows=0; // Dim nRows as long COL12 nCols=0; // Dim nCols as long double dTemp=0.0,dAvTol=0.0; // Dim dTemp as double, dAvTol as double RW12 Counter=0; // Dim Counter as long // theNumbers is pointer to oper value array (P)// vArr=theNumbers.Value2 // get num of rows cols in input data theNumbers->GetDims(nRows,nCols); //nRows=Ubound(vArr)nCols=Ubound(vArr,2) // loop thru the input data for (RW12 i=0; i<=nRows-1; i++) // For i=0 To nRows-1 Step 1 // curly brackets for a block of statements { // get value, (zero if not converted to double) dTemp=theNumbers->Cell(i,0).ToDouble(); // dTemp=cdbl(vArr(i+1,1) //compare to the tolerance if (fabs(dTemp)>theTolerance) // If Abs(dTemp)>theTolerance Then { dAvTol += dTemp; // dAvTol=dAvTol+dTemp Counter++; // Counter=Counter+1 }; // End If }; // Next i // return value if neither are zero: // ! means NOT && means AND || means OR if (dAvTol != 0.0 && Counter != 0) // If dAvtol<>0.0 and Counter<>0 then return dAvTol/Counter; // AvTolXLL2=dAvtol/Counter Else // else return 0.0; // AvTolXLL2=0.0 ‘Note the ; ends the IF } // End Function VBA to C - Pratfalls and Perils

The Journey so far … • STL/BOOST/XLL+/VS2010 • High-level language, rich framework • But lot to learn • Memory management not yet needed • Performance is good • Creating simple UDFs is easy … VBA to C - Pratfalls and Perils

Things I don’t (yet) understand • Strings • XLL & Excel Locales • Passing data between VBA & C • Oper data structure • Binary Names • Asynchronous functions • … VBA to C - Pratfalls and Perils

VBA to C: Pratfalls and Perils XLDevC London 2012