Hi, Im having trouble assigning values to an array (nicely)
If you can assist or point me in the direction I would appretiate it.

Im writing a sheet where I want to dim an array, then assign variable
to the elements. The values are known in advance and I can set the
one at a time with the following code
MyArray(1) = 10: MyArray(2) = 27...

But I think i am looking for a vba version of something like..

Data 10,27,33,45,57
For valueCount = 1 to 5
Read AddValue(ValueCount)
Next ValueCount

I have the following code as an example of my current sollution,
but in fact I need to enter a minimum of 30 steps. I don't want t
read a range of cells for the values as ideally i want to keep in vba.

an example of my code

Dim AddValue(1 To 5) As Single
Dim ValueCount As Byte
Dim MyValue As Single

'---------------------[hmmm!]-----
AddValue(1) = 10
AddValue(2) = 27
AddValue(3) = 33
AddValue(4) = 45
AddValue(5) = 57
'----[ i'd like to assign these in loop with a data statement?? ]---

Do
ValueCount = ValueCount + 1
If ValueCount = 6 Then
ValueCount = 0
Exit Sub
Else
MyValue = MyValue + AddValue(ValueCount)
End If
Loop

End Sub


Any Assistance appretiated,

Regards

Skywa

--
Message posted from http://www.ExcelForum.com

I'm not sure why you said you want to "assign [the values] in a loop
with a data statement", but if the functions in the freely downloadable
file at http://home.pacbell.net/beban are available to your workbook you
might consider the following (simply expand the Array function to serve
as the "data statement"):

Option Base 1
__________________________
Dim MyValue() As Single
Assign Array(10, 27, 33, 45, 57), MyValue

Alan Beban

Skyway < wrote:
Hi, Im having trouble assigning values to an array (nicely)
If you can assist or point me in the direction I would appretiate it.

Im writing a sheet where I want to dim an array, then assign variables
to the elements. The values are known in advance and I can set them
one at a time with the following code
MyArray(1) = 10: MyArray(2) = 27...

But I think i am looking for a vba version of something like..

Data 10,27,33,45,57
For valueCount = 1 to 5
Read AddValue(ValueCount)
Next ValueCount

I have the following code as an example of my current sollution,
but in fact I need to enter a minimum of 30 steps. I don't want to
read a range of cells for the values as ideally i want to keep in vba.

an example of my code

Dim AddValue(1 To 5) As Single
Dim ValueCount As Byte
Dim MyValue As Single

'---------------------[hmmm!]-----
AddValue(1) = 10
AddValue(2) = 27
AddValue(3) = 33
AddValue(4) = 45
AddValue(5) = 57
'----[ i'd like to assign these in loop with a data statement?? ]---

Do
ValueCount = ValueCount + 1
If ValueCount = 6 Then
ValueCount = 0
Exit Sub
Else
MyValue = MyValue + AddValue(ValueCount)
End If
Loop

End Sub


Any Assistance appretiated,

Regards

Skyway


---
Message posted from http://www.ExcelForum.com/

Hi Alan,

Thanks for the rapid reply,
you have the essence of what Im doing but was hoping to not requir
additional functions and the like to achieve what i need.
and they are too complicated for me to grasp - sorry -

I feel I am not understanding the basic use of array's

to assign values to the array "Value" must I say

MyValue(1) = 10
MyValue(2) = 27
MyValue(3) = 33
MyValue(4) = 45
MyValue(5) = 57


I am looking for a line like your suggested
Assign Array(10, 27, 33, 45, 57), MyValue
without the custom functions??

Is it possible and fairly simple?


Thanks for your time, any assistance appretiated:confused

--
Message posted from http://www.ExcelForum.com

"Alan Beban" wrote...
I'm not sure why you said you want to "assign [the values] in a loop
with a data statement", but if the functions in the freely downloadable
file at http://home.pacbell.net/beban are available to your workbook you
might consider the following (simply expand the Array function to serve
as the "data statement"):

Option Base 1
__________________________
Dim MyValue() As Single
Assign Array(10, 27, 33, 45, 57), MyValue

Alan Beban
...

And what, pray tell, are the assumed or imagined advantages of doing this versus

Dim MyValue As Variant
MyValue = Array(10!, 27!, 33!, 45!, 57!)

??

For the OP: is there any particular reason you're using Single as the datatype
rather than Double? If you're running Excel under Windows on a CPU manufactured
sometime in the last decade, your CPU has hardware floating point support, which
means *ALL* floating point calculations take place in *extended* double
precision (80 bits) rather than single precision. The only potential advantage
to Single over Double would be in memory usage, since Single uses 4 bytes per
vlaue while Double uses 8.

--
To top-post is human, to bottom-post and snip is sublime.

I assume from the snotty tone of the question that it was directed to
me. The advantage is that MyValue is type Single() as specified by the
OP, which specification was conveniently snipped out of Harlan Grove's post.

Alan Beban

Harlan Grove wrote:
"Alan Beban" wrote...

I'm not sure why you said you want to "assign [the values] in a loop
with a data statement", but if the functions in the freely downloadable
file at http://home.pacbell.net/beban are available to your workbook you
might consider the following (simply expand the Array function to serve
as the "data statement"):

Option Base 1
__________________________
Dim MyValue() As Single
Assign Array(10, 27, 33, 45, 57), MyValue

Alan Beban

..

And what, pray tell, are the assumed or imagined advantages of doing this versus

Dim MyValue As Variant
MyValue = Array(10!, 27!, 33!, 45!, 57!)

??

For the OP: is there any particular reason you're using Single as the datatype
rather than Double? If you're running Excel under Windows on a CPU manufactured
sometime in the last decade, your CPU has hardware floating point support, which
means *ALL* floating point calculations take place in *extended* double
precision (80 bits) rather than single precision. The only potential advantage
to Single over Double would be in memory usage, since Single uses 4 bytes per
vlaue while Double uses 8.

--
To top-post is human, to bottom-post and snip is sublime.

"Alan Beban" wrote...
...
The advantage is that MyValue is type Single() as specified by the
OP, which specification was conveniently snipped out of Harlan Grove's post.
...

No, I anticipated the OP's response that your Assign macro was too complicated.
There *IS* both performance drag and additional storage overhead storing dynamic
arrays in variant scalars, but for a 5-entry array of singles those drags
combined would be less than the memory and execution time drag of adding and
calling your Assign macro. Your Assign macro has limited potential utility for
large arrays, in which case the function call and processing overhead would
likely be a small percentage of the overall runtime, but it's a gross waste of
resources when dealing with small arrays. Since you lack any professional
experience programming, it's not surprising you continue to fail to realize
this. Also becoming clearer that you're incapable of learning this lesson. I'll
keep on trying to educate you, forlorn hope that it is.

--
To top-post is human, to bottom-post and snip is sublime.

Harlan Grove wrote:
. . . There *IS* both performance drag and additional storage overhead storing dynamic
arrays in variant scalars, but for a 5-entry array of singles those drags
combined would be less than the memory and execution time drag of adding and
calling your Assign macro. Your Assign macro has limited potential utility for
large arrays, in which case the function call and processing overhead would
likely be a small percentage of the overall runtime, but it's a gross waste of
resources when dealing with small arrays. Since you lack any professional
experience programming, it's not surprising you continue to fail to realize
this. . . .

It's not that I fail to realize it; it's that I realize something else
that seems difficult for you to accept. There are users with
applications out there who couldn't care less that a step in their
procedure, which takes 200 nannoseconds to execute, could, with some
judicious analysis and choice of tradeoffs, be optimized to take only 10
nannoseconds to execute. Painful as it may be for a professional
programmer/developer to swallow, it is often OK with such users that a
particular algorithm be "a gross waste of resources", because in their
circumstances that is irrelevant to whether they effectively get their
work done.

Alan Beban

Hi Harlan,

Thanks very much. - This works for me.


Option Base 1
------------------------------
Dim MyValue As Variant
MyValue = Array(10!, 27!, 33!, 45!, 57!)



Why Single. I am very new to VBA and thaught that I should use th
smallest memory usage datatype I could. Is this misguided?
originally selected "Byte" but it failed as later numbers in my arra
are -3, - 19 which failed as byte.

I will look at your suggestions and enjoy any further advice on th
subject.


Thanks again.

And Thanks to anyone else who considered my ramblings

--
Message posted from http://www.ExcelForum.com

Hi Harlan,

Thanks very much. - This works for me.


Option Base 1
------------------------------
Dim MyValue As Variant
MyValue = Array(10!, 27!, 33!, 45!, 57!)



Why Single. I am very new to VBA and thaught that I should use th
smallest memory usage datatype I could. Is this misguided?
originally selected "Byte" but it failed as later numbers in my arra
are -3, - 19 which failed as byte.

I will look at your suggestions and enjoy any further advice on th
subject.


Thanks again.

And Thanks to anyone else who considered my ramblings

--
Message posted from http://www.ExcelForum.com