Populate a variant in code

[Atom]

I am creating a function whose purpose is to hold some values which will be passes to another function which has two variants (1d array) as inputs. Here is what I've done and which is not working. Syntax help please.

Please Login or Register  to view this content.

[Fluff13]

You've got a stray ( bracket in the DegF

Please Login or Register  to view this content.

try removing it.

[MrShorty]

The DegF statement has an opening parenthesis, but no closing parenthesis. I removed this character so that the syntax is the same as the LvPct statement, and the code ran correctly (it output 13 to the immediate window, and output 0 as the result of the function).

You could also use the Array() function for these assignments DegF=Array(-40, 55, ...) https://docs.microsoft.com/en-us/off...array-function

[Atom]

Ah, thanks for pointing out my typo. However, the function is expecting a variant of form AR(x,y). So far I have only been able to create an array of for AR(x). If I use
debug.print DegF(1) is prints -40. I want the form to be debug.print DegF(1,1). How to do?

[Fluff13]

The function you've posted is expecting two numbers not an array.
Can you please explain what you are trying to do?

[MrShorty]

As coded, I don't see the function "expecting" a 2D array anywhere.

Other than assigning a Range(...).value to a variant array, I am not aware how to create a 2D array in one statement like you describe.

Perhaps a jagged array (or array of arrays) might be appropriate here. Assigning the original array could be done using nested Array() functions:

Please Login or Register  to view this content.

would create a variant containing an array of 3 variants, and each of those variants is a variant containing an array. You access the elements of the jagged array as

Please Login or Register  to view this content.

which would print 220 to the immediate window.

I also see others talk about using dictionaries or collections for this kind of thing, so maybe one of those concepts would be useful to explore.

At this point, it might be difficult for us to give good, specific recommendations because your sample function only shows us how you are trying to assign the values to the variant containing an array. You are not showing us how this fits into your overall project (the other function(s) that will receive these arrays or anything). How this part of the task fits into your overall project might help us make better recommendations.

[Atom]

Here is the function I want to invoke. I have used it a lot in this form.

Please Login or Register  to view this content.

Normally, I use the MyAkima function by passing it arrays from ranges on a spreadsheet. In this case, I want to pass it arrays from an array of fixed values.

[Fluff13]

As far as I know, the only way to convert a 1d array to a 2d array for what you need, would be to loop through the 1d array & put the values into the 2d array.
In which case you would probably be better off just pulling the 2d array from a sheet

[MrShorty]

If I set up both functions in a module in a new workbook, I don't have any trouble passing the variant/arrays to the MyAkima function. I would note that MyAkima appears to be expecting 1D ranges/arrays, so I'm not sure what the point of making 2D arrays was.

The troubles that I do run into are after entering MyAkima. MyAkima is coded to work with Variant/Ranges, but not work with Variant/Arrays. So, the first thing MyAkima tries to do is count how many values are in known_x using the .Count property. The .Count property is a perfectly good property of Range objects, but arrays do not have a .Count property. MyAkima errors on this statement when it receives a variant/array instead of a range.

Because the real problem is in MyAkima, I think the solution will be to go through MyAkima and edit its code to be able to handle both variant/range and variant/arrays. Maybe some Block Ifs using the TypeName() function (https://docs.microsoft.com/en-us/off...ename-function ) or something similar.

Please Login or Register  to view this content.

I'm sure there are other ways to do it. The main idea is that MyAkima needs to test its arguments for where they are coming from and then handle each possible "type" appropriately.

[Atom]

Ok, thank you. In this instance, the MyAkima function does not need to receive range inputs, so I will modify the code to use Ubound rather than .count.

[Rick Rothstein]

Other than assigning a Range(...).value to a variant array, I am not aware how to create a 2D array in one statement like you describe.

You could transpose the array to make it two-dimensional in the same way a single column assigned to a Variant becomes a two-dimensional array...

OneDimensional = [{1,2,3,4}]

TwoDimensional = [TRANSPOSE({1,2,3,4})]

[Marc L]

Hi !

According to Excel array syntax TRANSPOSE worksheet function is useless for a 2D column array : V = [{1;2;3;4}] …

[Rick Rothstein]

@Marc L,

I am afraid that I may not have made myself as clear as I wanted. I was not saying to use TRANSPOSE on a column array... what I was trying to convey that if you had a column of values (1, 2, 3, 4 in my example), that the two-dimensional array you would get by assigning that column of cells to a Variant variable would be the same as assigning [TRANSPOSE({1,2,3,4})] to a Variant variable. In other words, if Arr was a variant variable and A1:A4 held the values 1, 2, 3 and 4 respectively, then this array...

Arr = Range("A1:A4")

and this one...

Arr = [TRANSPOSE({1,2,3,4})]

would be identical. The reason for my posting the TRANSPOSE method was to show MrShorty that a 2D array could be created without assigning from a range... the reason for mentioning the array from a column of values was to demonstrate the type of array that would result, namely that the array elements would be Arr(1,1), Arr(2,1), Arr(3,1) and Arr(4,1).

But, of course, your method, namely V = [{1;2;3;4}], creates that same array more directly.

[Marc L]

The reason why I used italic …

[Atom]

I have modified my functions according to some of the advice here. The key was understanding the difference between a variant array, and a variant range. Here is the code:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks to everyone for the assistance.