Excel Add-on Development.
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 ? |
Excel Add-on Development.
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???? as?m ?enyuva wrote: 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 |
Excel Add-on Development.
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 "asim senyuva" <asim wrote in message ... 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 ? |
Excel Add-on Development.
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. ----------------------------------------------------------------------------------- "Peter T" wrote: 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 "asim senyuva" <asim wrote in message ... 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 ? |
All times are GMT +1. The time now is 02:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com