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.

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

"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.

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

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

"Alan Beban" wrote...
...
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.

Simplicity and convenience in this thread's OP's case would have been
accomplished with

Dim x As Variant
x = Array(1!, 2!, 3!, 4!)

which the OP has been nice enough to confirm works just fine.

You have an uncanny ability to overcomplicate simple things. As simple as
possible but no simpler is a design principle you refuse to appreciate.

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

Harlan Grove wrote:
. . . Simplicity and convenience in this thread's OP's case would have been
accomplished with

Dim x As Variant
x = Array(1!, 2!, 3!, 4!)

which the OP has been nice enough to confirm works just fine.

You have an uncanny ability to overcomplicate simple things. As simple as
possible but no simpler is a design principle you refuse to appreciate.

Nonsense; you continually set up straw men so you can pontificate. The
OP indicated he wanted x to be of type Single(), and I honored that. You
didn't and it turns out the OP didn't really mean it. Had I known that,
I'd have ended up where you did; or might not even have responded at
all. I suspect now that even x=Array(1,2,3,4) is satisfactory for the OP.

Alan Beban

"Alan Beban" wrote...
...
OP indicated he wanted x to be of type Single(), and I honored that. You
didn't and it turns out the OP didn't really mean it. Had I known that,
I'd have ended up where you did; or might not even have responded at
all. I suspect now that even x=Array(1,2,3,4) is satisfactory for the OP.

Don't worry, Alan. When you get more experience answering newsgroup questions,
you may develop the ability to anticipate the OP's actual requirements without
needing to have them spelled out for you.

The OP's original use of Data and Read statements should have been a tip-off
that the OP may BASIC but not VB[A], in which case it wouldn't be a leap in the
dark to figure that the OP might not have known that s/he could assign Array(.)
results to a variant. Most people with some experience with old fashioned BASIC
dialects could figure that out pretty quickly.

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

Sorry to you both,

to compound my guilt for causing friction between you both (i imagin
this is a long standing battle) I admit that i can now not figure ou
how i was unable to find what i needed within the online help, as
find it now without problem. - DOH!

I had got sidetracked by old BASIC ways (Data & Read) and no amount o
reference to my Walkenbach books helped as i was looking in the wron
direction.

Alan: are you are saying that had you realised my question was so dumb
you wouldn't have answered it - well - thanks but when your stuck you
stuck - just the sollution is easier to reveal.
and yes, x=Array(1,2,3,4) is probably satisfactory and i appologis
for not asking the right question - but I was stuck.

You both deserve thanks for responding, and please do consider/continu
answering even the dumb questions.


Thanks and appologies again

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

Skyway < wrote:
to compound my guilt for causing friction between you both . . . .

You should feel no guilt for something beyond your control, not caused
by you.

. . . Alan: are you are saying that had you realised my question was so dumb,
you wouldn't have answered it?

Absolutely not. I was interested in responding because I thought from
your post (specifically, from Dim MyValue As Single) that you were
seeking to load an array of type Single(). The fact that you weren't
really interested in that in no way makes your question dumb; it simply
makes it of lesser interest to me.

- well - thanks but when you're stuck you're
stuck - just the sollution is easier to reveal.
and yes, x=Array(1,2,3,4) is probably satisfactory and i appologise
for not asking the right question - but I was stuck.

You both deserve thanks for responding, and please do consider/continue
answering even the dumb questions.


Thanks and appologies again.

I assure you, no apology is called for. Thanks for the feedback.

Alan Beban

You asked the right question. It's just a matter of reading between the
lines. DATA/READ is a strong indicator that you'd never seen a Variant or a
dynamic array before.