Use VB array as as argument for worksheet function

[hmm]

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 Phillips]

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)

 

[Joel]

Sub test()

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

 

[Bob Phillips]

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)

 

[SteveM]

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)

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

 

[Joel]

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.

 

[Ron Rosenfeld]

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

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)

 

[SteveM]

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)

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