Excel Add-on Development.

[asım şenyuva]

hello,
I Wrote a custom function on excel. My function is return an array. and its
runs OK. My problem is #N/A. Firstly. I wrote an my function command excel
function bar and after I select an area on worksheet and press CTRL + SHIFT +
ENTER.

if my selected range is bigger then function return value ( array - XLOPER -
size). I saw #N/A. how can i remove this. Anyone knows something about that ?

 

[Dave Peterson]

When I do this in VBA, I look at the size of the cells holding the array formula
and pad with "".

For instance, if I select A1:A10 and array enter:
=myArr()

with this function:

Option Explicit
Function myArr() As Variant

Dim myVal As Variant
Dim HowManyCells As Long
Dim HowManyElements As Long
Dim iCtr As Long

'Application.caller is the cell(s)
'containing the array formula
'I'm using a single column area for this
'but you don't need to.
HowManyCells = Application.Caller.Cells.Count

myVal = Array("a", "B", "c", "D")

HowManyElements = UBound(myVal) - LBound(myVal) + 1
If HowManyElements < HowManyCells Then
ReDim Preserve myVal(1 To HowManyCells)
For iCtr = HowManyElements + 1 To HowManyCells
myVal(iCtr) = ""
Next iCtr
End If

myArr = Application.Transpose(myVal)

End Function

Maybe you can use this idea????

 

[Peter T]

One disadvantage of array formulas is it is not possible to edit a "part of
an array".
If you have only just entered a "too big array" and got those #N/A, why not
press Undo and start again. Just a thought.

Regards,
Peter T

hello,
I Wrote a custom function on excel. My function is return an array. and its
runs OK. My problem is #N/A. Firstly. I wrote an my function command excel
function bar and after I select an area on worksheet and press CTRL + SHIFT +
ENTER.

if my selected range is bigger then function return value ( array - XLOPER -
size). I saw #N/A. how can i remove this. Anyone knows something about

that ?

 

[asım şenyuva]

i use XLOPER structure for passing data to excel. this structure knows array
Column Size and Row Size but when user press CTRL + ALT + DEL excel didnt
compare user selected area with xloper array dimension.

I think, i can calculate user selected area like A1:B3 sign. and after i can
pass param this to my DLL. and i try to calculate Selected dimensions. i can
set Array values to 0 ot emty char if i know user selected area.

but ,
is that basic way for this ?


structure of XLOPER

TVal = packed Record
Case Byte of
1: (num : Double); // xltypeNum
2: (str : ^ShortString); // xltypeStr
3: (bool : Word); // xltypeBool
4: (err : Word); // xltypeErr
5: (w : Integer); // xltypeInt
6: (sref : packed record
Count : word;
ref : Xlref;
end);
8: (_array : packed Record // xltypeMulti
lparray : lpxloperArray;
rows : WORD;
columns : WORD;
End);
9: (mref : packed record // xltyperef
lpmref : lpxlmref;
idsheet : integer;
End);
End; // tval

XLOPER = packed Record
val : TVal;
xltype : WORD;
dummy :array[0..5] of byte; // filler
End;

xlarrayArray = array[0..1024] of xloper;
xlarray = ^xlarrayArray;

I use GlobalAlloc method and pass my XLOPER pointer to excel. after that
excel return to me with AutoFree method.

-----------------------------------------------------------------------------------