xll:: xlw - excel addin

 

function INPUT/OUTPUT type

• short
• double
• bool
• std::string
• int    - although it can be used as an input type it cannot be an output type. Convert it to double for output.

 
 

---

Numeric array input/output

• MyMatrix    - two dimension
• MyArray     - one dimension  , typecase to std::vector<double>

 

---

Input/output matrices of mixed types

CellMatrix or XlfOper can be used to input or output matrices of mixed types. XlfOper is preferable since it does not need to copy data into temporary memory, which is especially important for big ranges.
 
XlfOper has the following methods which allow querying data directly from Excel:

• Inspectors:
  • IsMissing() - used by Excel to flag a parameter that hasn’t been supplied
  • IsError() - is the data an error?
  • IsRef() - is the data a reference?
  • IsSRef() - is the data a sheet reference?
  • IsNumber() - is the data a number?
  • IsString() - is the data a string?
  • IsNil() - is the data an empty cell?
  • IsBool() - is the data a boolean?
  • IsInt() - is the data an integer?
  • IsMulti() - is the data a matrix?. If multi can use the following methods:
    • rows() - number of rows in the matrix
    • columns() - number of columns in the matrix
    • (i, j) - can use bracket notation to set/retrieve values from the matrix
• Conversions:
  • AsDouble() to double
  • AsRef() to XlfRef
  • AsShort() to short
  • AsBool() to bool
  • AsInt() to int
  • AsULong() to unsigned long
  • AsString() to char*
  • AsWstring() to std::wstring
  • AsDoubleVector() to std::vector<double>
  • AsArray() to MyArray
  • AsMatrix() to MyMatrix
  • AsCellMatrix() to CellMatrix

 

# lib.h

XlfOper //Squares all numbers and sets all other cells to \"-\"
SquareNumbers(XlfOper& input //input range
  );

Note: Input XlfOper& argument should not be const, because otherwise you won’t be able to access its elements with (i, j) notation.
 
 
 

# lib.cpp

XlfOper SquareNumbers(XlfOper& input)
{
  if( !input.IsMulti() )
  {
    XlfOper res;
    if (input.IsNumber())
    {
      const double x = input.AsDouble();
      res = x * x;
    }
    else
    {
      res = "-";
    }
    return res;
  }

  XlfOper res(input.rows(), input.columns());
  for (size_t i = 0; i < res.rows(); ++i)
  {
    for (size_t j = 0; j < res.columns(); ++j)
    {
      if(input(i,j).IsNumber())
      {
        const double x = input(i, j).AsDouble();
        res(i, j) = x*x;
      }
      else
      {
        res(i, j) = "-";
      }
    }
  }
  return res;
}

 
 

# result

XlfOper ret(3, 2);
ret(0, 0) = "abc";
ret(0, 1) = (short)42;
ret(1, 0) = 1.23;
ret(1, 1) = XlfOper::Error(xlerrValue);
ret(2, 0) = true;



# Update 08/19/2016 Gotcha 
# Sometimes, when you use two dimensional XlfOper as a return 
# from your function, instead of your output you see #N\A in Excel cells. 
# This happens when one or more of the cells of XlfOper are left not initialized. 
# For instance if in the example above you remove the second line (ret(0, 0) = "abc";) 
# the output in Excel will contain #N\A in every cell (not just in the first one).

 
 
 
 
 
 
https://blog.adaptiverisk.com/xll/excel/2014/12/19/xlw-part4.html

 

Input/output arrays, vectors, matrices or ranges (XLW Part 4) - The AdaptiveRisk Blog

In the last post I described how to write simple functions using XLW. For the introduction to XLW series take a look at the first post. Today I am going to show how write functions that can input/ouput arrays, vectors, matrices or general ranges.The starti

blog.adaptiverisk.com

 
 

---

Parallel Calculations

Excel can compute cells in parallel if they are marked as thread safe. To mark a function as thread safe put the following directive before the function name in function declaration block:

double //Sum of square roots of two numbers
//<xlw:threadsafe
SumSqrt(double x, //argument 1
  double y //argument 2
  );

 
 
 
 

---

Volatile Function

A volatile function is a function whose value can change even if the arguments have not. Usually you apply volatile directive to a function which depends on random numbers or time. To specify that a function is volatile add //<xlw:volatile before the function’s name:

# lib.h

double // Number of minutes since Jan 1, 2000
//<xlw:volatile
SysTime();

 

# lib.c

#include <time.h>

double SysTime()
{
  time_t timer = time(NULL);

  struct tm y2k;
  y2k.tm_hour = 0;   y2k.tm_min = 0; y2k.tm_sec = 0;
  y2k.tm_year = 100; y2k.tm_mon = 0; y2k.tm_mday = 1;

  double seconds = difftime(timer, mktime(&y2k));

  return seconds/60;
}

 
 

---

 Calling add-in functions from VB

A function defined in an add-in can be called from VBA using Application.Run function: 
<Output Value> = Application.Run("<function name>", <input 1>, <input 2>, ... <input N>)
 
 
Add a module to an Excel file with the following code:

#vba 
Function vbaOuter1(v As Variant)
  vbaOuter1 = Application.Run("Outer1", v)
End Function

 
 

[##_File|kage@c0K3HQ/btssYQ2kjoA/AAAAAAAAAAAAAAAAAAAAAKlmuCkHsh5GgUsMWPouNi6h-wo4IfGribHfR_GuXWqC/tfile.zip?credential=yqXZFxpELC7KVnFOS48ylbz2pIh7yKj8&expires=1780239599&allow_ip=&allow_referer=&signature=Jdm%2FSb2gncWrIgkPLHx5GnuS%2BH0%3D|filename="xlw-part5.zip" size="3.74MB" data-ke-align="alignCenter"|_##]

 
 
 
 
 
_