Use VB array as as argument for worksheet function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hmm - 09 Dec 2007 13:16 GMT

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.

Reply to this Message

Bob Phillips - 09 Dec 2007 13:49 GMT

Yeah, no problem, such as

Application.SUM(array)

Signature

HTH

Bob

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

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

Reply to this Message

Joel - 09 Dec 2007 13:56 GMT

Sub test()

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

> Yeah, no problem, such as
>
[quoted text clipped - 6 lines]
> > code (instead of the usual worksheet-range input) as the argument for this
> > function? Thanks.

Reply to this Message

Bob Phillips - 09 Dec 2007 14:09 GMT

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

Signature

HTH

Bob

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

> Sub test()
>
[quoted text clipped - 15 lines]
>> > this
>> > function? Thanks.

Reply to this Message

SteveM - 09 Dec 2007 14:20 GMT

> Why do you always reply to someone else's post instead of the original post,
> especially when you then just say the same thing?
[quoted text clipped - 33 lines]
> >> > 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

Reply to this Message

Bob Phillips - 09 Dec 2007 14:46 GMT

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.

Signature

---
HTH

Bob

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

>> Why do you always reply to someone else's post instead of the original
>> post,
[quoted text clipped - 46 lines]
>
> SteveM

Reply to this Message

Joel - 09 Dec 2007 14:26 GMT

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.

> Why do you always reply to someone else's post instead of the original post,
> especially when you then just say the same thing?
[quoted text clipped - 18 lines]
> >> > this
> >> > function? Thanks.

Reply to this Message

Ron Rosenfeld - 09 Dec 2007 14:42 GMT

>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

Reply to this Message

Bob Phillips - 09 Dec 2007 14:52 GMT

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.

Signature

---
HTH

Bob

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

> 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.
[quoted text clipped - 52 lines]
>> >> > this
>> >> > function? Thanks.

Reply to this Message

SteveM - 09 Dec 2007 15:49 GMT

> Joel,
>
[quoted text clipped - 93 lines]
> >> >> > 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

Reply to this Message