Use VB array as as argument for worksheet function
 

I know I can use Excel worksheet functions in VB. My question is when one of
the parameters of a function is an array. Can I used an array defined in VB
code (instead of the usual worksheet-range input) as the argument for this
function? Thanks.

Use VB array as as argument for worksheet function
 

Yeah, no problem, such as

Application.SUM(array)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"hmm" wrote in message
...
I know I can use Excel worksheet functions in VB. My question is when one
of
the parameters of a function is an array. Can I used an array defined in
VB
code (instead of the usual worksheet-range input) as the argument for this
function? Thanks.

Use VB array as as argument for worksheet function
 

Sub test()

myarray = Array(1, 2, 3, 4)
total = WorksheetFunction.Sum(myarray)
End Sub


"Bob Phillips" wrote:

Yeah, no problem, such as

Application.SUM(array)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"hmm" wrote in message
...
I know I can use Excel worksheet functions in VB. My question is when one
of
the parameters of a function is an array. Can I used an array defined in
VB
code (instead of the usual worksheet-range input) as the argument for this
function? Thanks.

Use VB array as as argument for worksheet function
 

Why do you always reply to someone else's post instead of the original post,
especially when you then just say the same thing?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Joel" wrote in message
...
Sub test()

myarray = Array(1, 2, 3, 4)
total = WorksheetFunction.Sum(myarray)
End Sub


"Bob Phillips" wrote:

Yeah, no problem, such as

Application.SUM(array)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"hmm" wrote in message
...
I know I can use Excel worksheet functions in VB. My question is when
one
of
the parameters of a function is an array. Can I used an array defined
in
VB
code (instead of the usual worksheet-range input) as the argument for
this
function? Thanks.

Use VB array as as argument for worksheet function
 

On Dec 9, 9:09 am, "Bob Phillips" wrote:
Why do you always reply to someone else's post instead of the original post,
especially when you then just say the same thing?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Joel" wrote in message

...

Sub test()

myarray = Array(1, 2, 3, 4)
total = WorksheetFunction.Sum(myarray)
End Sub

"Bob Phillips" wrote:

Yeah, no problem, such as

Application.SUM(array)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"hmm" wrote in message
...
I know I can use Excel worksheet functions in VB. My question is when
one
of
the parameters of a function is an array. Can I used an array defined
in
VB
code (instead of the usual worksheet-range input) as the argument for
this
function? Thanks.

Before a fence war starts, I've noticed that responses are not posted
immediately. So I'm assuming two guys can submit the same idea before
each has seen the other's posted. Now I also get the feeling that we
have an understated Jeopardy thing going on here in being first to
respond. Maybe if you guys got buzzers....:)

SteveM

Use VB array as as argument for worksheet function
 

Bob: First, If I reply to the originator then the replier doesn't see the
response. I think all the responders should see all the responses.

Second, I think you sometimes reply at a level that a Newbie would not
understand. Not everybody knows about the Array function in VB. A newbie
may code the following

Dim myarray(4)
myarray(0) = 1
myarray(1) = 2
myarray(2) = 3
myarraya3) = 4

My code showed how to define an array which your code didn't. It is
difficult by the psting to know the persons skill level. Too many times
people respond that they didn't understand the instructions. The experts
should be teaching the Newbie, not just answer the questions. Code that we
post should should be understandable that anybody including Newbies can
clearly understand. Document the code where necessary. Use intermediate
variable to make the code clear.

Rather than
data = Range("A" & rows.count).end(xlup)
It is better
LastRow = Range("A" & rows.count).end(xlup).row
data = Range("A" & LastRow)

The code may be less efficient, but it is more understandable.


"Bob Phillips" wrote:

Why do you always reply to someone else's post instead of the original post,
especially when you then just say the same thing?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Joel" wrote in message
...
Sub test()

myarray = Array(1, 2, 3, 4)
total = WorksheetFunction.Sum(myarray)
End Sub


"Bob Phillips" wrote:

Yeah, no problem, such as

Application.SUM(array)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"hmm" wrote in message
...
I know I can use Excel worksheet functions in VB. My question is when
one
of
the parameters of a function is an array. Can I used an array defined
in
VB
code (instead of the usual worksheet-range input) as the argument for
this
function? Thanks.

Use VB array as as argument for worksheet function
 

On Sun, 9 Dec 2007 06:26:00 -0800, Joel wrote:

Bob: First, If I reply to the originator then the replier doesn't see the
response. I think all the responders should see all the responses.

Why is that?

I've never found that to be a problem. Maybe you need a proper newsreader?
--ron

Use VB array as as argument for worksheet function
 

No, he quoted mine, so he must have seen it.

It's nothing to do with jeopardy, but it is wasting my time if I see a
response to my post as I tend to read it.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"SteveM" wrote in message
...
On Dec 9, 9:09 am, "Bob Phillips" wrote:
Why do you always reply to someone else's post instead of the original
post,
especially when you then just say the same thing?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Joel" wrote in message

...

Sub test()r

myarray = Array(1, 2, 3, 4)
total = WorksheetFunction.Sum(myarray)
End Sub

"Bob Phillips" wrote:

Yeah, no problem, such as

Application.SUM(array)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"hmm" wrote in message
...
I know I can use Excel worksheet functions in VB. My question is
when
one
of
the parameters of a function is an array. Can I used an array
defined
in
VB
code (instead of the usual worksheet-range input) as the argument
for
this
function? Thanks.

Before a fence war starts, I've noticed that responses are not posted
immediately. So I'm assuming two guys can submit the same idea before
each has seen the other's posted. Now I also get the feeling that we
have an understated Jeopardy thing going on here in being first to
respond. Maybe if you guys got buzzers....:)

SteveM

Use VB array as as argument for worksheet function
 

Joel,

I have no problem with you joining in if you think you have something to
add, but the guy mentioned an array defined in VB, so it seemed reasonable
to me that he knew what an array is. Notwithstanding that issue, and the
point that you make there may be valid, but perhaps you could take note of
someone like Dave Peterson who, when he jumps in, tends to make the
additional point, and explain that he is making an additional point and what
he thinks is not previously fully explained, rather than just seemingly
repeating the previous post, albeit in more words.

But on the other point, why would the replier not see the response. The
whole thread is there to be read. If I see a response below mine, I tend to
think it pertains to my response and I read it, which means that I waste my
time. And you are the only regular that I know that does that, no-one else
seems to think it valid.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Joel" wrote in message
...
Bob: First, If I reply to the originator then the replier doesn't see the
response. I think all the responders should see all the responses.

Second, I think you sometimes reply at a level that a Newbie would not
understand. Not everybody knows about the Array function in VB. A newbie
may code the following

Dim myarray(4)
myarray(0) = 1
myarray(1) = 2
myarray(2) = 3
myarraya3) = 4

My code showed how to define an array which your code didn't. It is
difficult by the psting to know the persons skill level. Too many times
people respond that they didn't understand the instructions. The experts
should be teaching the Newbie, not just answer the questions. Code that
we
post should should be understandable that anybody including Newbies can
clearly understand. Document the code where necessary. Use intermediate
variable to make the code clear.

Rather than
data = Range("A" & rows.count).end(xlup)
It is better
LastRow = Range("A" & rows.count).end(xlup).row
data = Range("A" & LastRow)

The code may be less efficient, but it is more understandable.


"Bob Phillips" wrote:

Why do you always reply to someone else's post instead of the original
post,
especially when you then just say the same thing?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Joel" wrote in message
...
Sub test()

myarray = Array(1, 2, 3, 4)
total = WorksheetFunction.Sum(myarray)
End Sub


"Bob Phillips" wrote:

Yeah, no problem, such as

Application.SUM(array)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"hmm" wrote in message
...
I know I can use Excel worksheet functions in VB. My question is
when
one
of
the parameters of a function is an array. Can I used an array
defined
in
VB
code (instead of the usual worksheet-range input) as the argument
for
this
function? Thanks.

Use VB array as as argument for worksheet function
 

On Dec 9, 9:52 am, "Bob Phillips" wrote:
Joel,

I have no problem with you joining in if you think you have something to
add, but the guy mentioned an array defined in VB, so it seemed reasonable
to me that he knew what an array is. Notwithstanding that issue, and the
point that you make there may be valid, but perhaps you could take note of
someone like Dave Peterson who, when he jumps in, tends to make the
additional point, and explain that he is making an additional point and what
he thinks is not previously fully explained, rather than just seemingly
repeating the previous post, albeit in more words.

But on the other point, why would the replier not see the response. The
whole thread is there to be read. If I see a response below mine, I tend to
think it pertains to my response and I read it, which means that I waste my
time. And you are the only regular that I know that does that, no-one else
seems to think it valid.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Joel" wrote in message

...

Bob: First, If I reply to the originator then the replier doesn't see the
response. I think all the responders should see all the responses.

Second, I think you sometimes reply at a level that a Newbie would not
understand. Not everybody knows about the Array function in VB. A newbie
may code the following

Dim myarray(4)
myarray(0) = 1
myarray(1) = 2
myarray(2) = 3
myarraya3) = 4

My code showed how to define an array which your code didn't. It is
difficult by the psting to know the persons skill level. Too many times
people respond that they didn't understand the instructions. The experts
should be teaching the Newbie, not just answer the questions. Code that
we
post should should be understandable that anybody including Newbies can
clearly understand. Document the code where necessary. Use intermediate
variable to make the code clear.

Rather than
data = Range("A" & rows.count).end(xlup)
It is better
LastRow = Range("A" & rows.count).end(xlup).row
data = Range("A" & LastRow)

The code may be less efficient, but it is more understandable.

"Bob Phillips" wrote:

Why do you always reply to someone else's post instead of the original
post,
especially when you then just say the same thing?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Joel" wrote in message
...
Sub test()

myarray = Array(1, 2, 3, 4)
total = WorksheetFunction.Sum(myarray)
End Sub

"Bob Phillips" wrote:

Yeah, no problem, such as

Application.SUM(array)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"hmm" wrote in message
...
I know I can use Excel worksheet functions in VB. My question is
when
one
of
the parameters of a function is an array. Can I used an array
defined
in
VB
code (instead of the usual worksheet-range input) as the argument
for
this
function? Thanks.

Bob, Suggest you ignore any responses submitted by Joel to minimize
time wasted.
Joel, Suggest you consider the rationale of Bob's position, but also
ignore his posts if it suits you.

If that doesn't work, I suggest Marquis of Queensbury Rules and 8
ounce gloves.

SteveM