Hello Dear Excel Help
I would like to create empty n-dimensional array with function farr for example as follows:
bounds="1 To 10,1 To 10,1 To 10"
arr=farr(bounds)
That means the bounds is varibale
Any thoughts, is it possible?
I know it can be done with Project object model with which
I can programmatically create module with funnction inside it at runtime,
but I would rather not use it.
Last edited by jakopak; 11-11-2016 at 02:50 PM.
Will you ever have more than 3 dimensions? If you really want n to be completely flexible, I think automating the VBE would be your only real option.
Don
Please remember to mark your thread 'Solved' when appropriate.
You cannot create a "dynamic array" exactly as you describe. The way you create a dynamic array is to Dim the array, then follow it up with a ReDim statement: https://msdn.microsoft.com/en-us/lib.../gg251578.aspx
The main limitation to be aware of (as described in the help file) is when you include the Preserve keyword in your ReDim statement, you can only change the last dimension of a multidimensional array. This usually means that you need to carefully think through what you are doing with your dynamic array so you can either avoid the use of Preserve, or structure the code so that only the last dimension changes size.
Please Login or Register to view this content.
Originally Posted by shg
Yes I would like to have that support and support for freely swaping, deleting (row, columns,elements...), combining, reshaping arrays.
Two ways for dynamical creation of n-dimensional array
1. Using VBA Project object model
2. Very long ifs structure like bellow:
Please Login or Register to view this content.
I found some ways how to implement array manipulation, none of which is pretty.
1. Using loops for copying one by one to another array (usable but slow)
2. Using sheet ranges (extremely slow)
3. Using Join and Split, for each dimension different separator (works only on String arrays, probably slow too)
4. Using nested arrays like Array(Array(1, 1, 1), Array(1, 1, 1)).
This is probably dead end, since Application.Evaluate("Array(Array(1, 1, 1), Array(1, 1, 1))(1)") yields Error 2029
5. Perhaps user defined Class?
6. Using external dll VarPtr http://stackoverflow.com/questions/7...tain-value-vba
Option 6. Is interesting, though there may be problem on systems where particular dll is missing and with restricted user rights. That is big drawback. Perhaps I can reference dll outside system folders or bundle all my modules and dlls in one file?
Last edited by jakopak; 11-12-2016 at 07:13 AM.
VarPtr is part of VBA- there is no external dll required.
Point 2 is the reason I inquired about the flexibility required.
I see, it works now with: Public Declare Function VarPtrArray Lib "VBE6" Alias "VarPtr" (Var() As Any) As Long
I have some functions in mind which operates on n-dimensional arrays. I don't want to rewrite them when I will encounter problem which needs arrays with higher dimensions. Up to now I have encountered problem for which I have needed n=6 or n=7. For example series of series of timeline sequence experiments. Each timeline sequence was 3D or 4D graph. I can use (and have used) R for this tasks, but I would like to do it excel too.
There may be problems which needs n>40. For example hundreds of temperature sensors, pressure sensors and humidity sensors placed in 3D space, for each coordinate in space I get n-dimensional graph of energy density. Another example is chemical sensors sensors in space which analyses air pollution. For each point in time I get n-dimensional graph. (n is a number of chemical pollutants for which I measure concentration)
Another example is clinical trials. Groups of patients are monitored and questioned throughout trial. Each patient generates time sequence. Every point in patient's time sequence is set of about 100 readings. Yet another example is tensor calculus or video stream analysis.
For theoretical work and playing it would be good to have unlimited number of dimensions. In R you can work with rectangular arrays like surgeon. With raged arrays it is a bit harder but still rather easy compared to excel.
Where do the dimension strings come from? I can't think of a situation where this would be advantageous; I also cannot contemplate using a 40 dimensional array so you are clearly a better man than I, but my instinct would be that you are using either the wrong language or the wrong constructs altogether.
An array with 40 dimensions, even if each dimension was of size 2, would be some multiple of a terabyte. I wouldn't plan on Excel accommodating that.
Last edited by shg; 11-12-2016 at 07:59 PM.
Entia non sunt multiplicanda sine necessitate
The dimensions come from user input or are constructed by function. For example I need function which creates n-dimensional array (input is the said "dimension strings"), so I can easily test my functions which operates on n-dimensional array. The testing routine will create n-dimensional arrays with dimensions from n=1 To n=20 and for each array it will try to apply my function which I want to test.
I would never dare to imply I am better man. OK, n-Dimensional array can be reshaped to one large 2D array or collection of arrays but I will lose the ability to slice the data in array easily with dimension numbers. I will have to use codes.
Yes good point.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks