# Posts by Om Avataar

• ## Summing Alternate Column Entries in a Row

Dear Excel Gurus,

I am seeking an INBUILT combination of functions (meaning NO programming thru' a macro or user-defined function!!) to find the sum of alternate values in a row.

For example, imagine I have numerical entries in a row, from A5 to U5, and in cell V5 I need to sum just alternate or 1-in-3 entries. That is, for the alternate case, just sum [A5,C5,E5,...], or in the case of 1-in-3, just sum [A5,D5,G5,...]. What formula in cell V5 will help me do this?
Obviously a simple: =SUM(A5:U5) won't do here. Is there something like: =SUM(A5:U5, 2) or =SUM(A5:U5, 3) which tells Excel to sum from A5:U5 BUT in steps of 2 or 3?

Please help... thanks in advance. Again, please note that I cannot afford a macro or some user-defined function to accomplish this.

Thanks.

- Om

• ## Standard Normal Random Number Generator

Re: Standard Normal Random Number Generator

Hi... Thanks for the link, but it references only Excel's in-built RAND() and RANK() functions - which facilitate random number generation from a Standard Uniform Distribution, i.e u[0,1].

I need to generate random numbers from a Standard Normal Distribution, i.e. N(0, 1).

Hope this clarifies.

• ## Standard Normal Random Number Generator

Hi VBA Gurus,

Is there an in-built Standard Normal Random Number Generator in Excel or Excel VBA? The 'RAND' built in function is a standard uniform number generator, I believe.

Thanks in advance to anyone who can help in any way... Gratzi.

- Om

• ## Assigning a 'Range' Argument to a 'Range' Variable

Re: Assigning a 'Range' Argument to a 'Range' Variable

Thanks to everyone who has guided me in solving my VBA problem referenced in this thread. This thread can now be deemed completely resolved, and therefore closed. Gratzi!

• ## Range Related

Re: Range Related

Thanks to everyone who has guided me in solving my VBA problem referenced in this thread. This thread can now be deemed completely resolved, and therefore closed. Gratzi!

• ## Incrementing an Element of a Range Variable: How To

Re: Incrementing an Element of a Range Variable: How To

Thanks to everyone who has guided me in solving my VBA problem referenced in this thread. This thread can now be deemed completely resolved, and therefore closed. Gratzi!

• ## Range Related

Re: Range Related

Someone, anyone... Please help if possible. Thanks. :kiwibird:

• ## Range Related

Dear VBA gurus,

I need a Range argument ‘s’ unchanged in function ZZ, as ‘s’ is needed for code in a later Module. I thought I would declare a Range variable ‘ss’ within function ZZ itself, increment a specified ‘element’ of ‘ss’, then pass this entire modified Range 'ss' as an argument to yet a different Module. Suffice to say that passing this entire Range 'ss' is a necessary evil...

Please advise on the code below, where the header of sub-/called function 'workHorse' has been shown for clarity... Thanks a million in advance.

• ## Incrementing an Element of a Range Variable: How To

Re: Incrementing an Element of a Range Variable: How To

Thanks for your replies, dangelor & Batman... I must unreservedly apologize for trying to oversimplify my actual problem statement in the initial help request.

See, thing is, I need my original 's' Range unchanged for code in another Module, while modified Range 'ss' itself needs to be passed as an argument to a different Module... Suffice to say that passing the entire modified Range 'ss' is a necessary evil...

Please advise on the re-posed (and truer to actual) code below, with (just the) header of sub-/called function workHorse shown for clarity... Thanks a million.

• ## Incrementing an Element of a Range Variable: How To

Below is just a test program for me to learn how to manipulate/change individual elements/members of a Range variable in VBA (if possible, that is).

Please advise... Thanks a million.

Code
``````Public Function Fuse(s As Range, custID As Integer, delta As Double) As Double
Dim ss As Range
Set ss = s

ss.Cells(custID, 1).Value = ss.Cells(custID, 1).Value + delta  'Error in this statement; Please advise
Fuse = ss.Cells(custID, 1).Value
End Function``````
• ## Assigning a 'Range' Argument to a 'Range' Variable

Re: Assigning a 'Range' Argument to a 'Range' Variable

Thanks for the quick reply, Norie... I'll try implementing it in my application shortly.

Thing is, I need my original 's' Range unchanged for code in another Module, while modified Range 'ss' needs to be passed as an argument to a different Module... Suffice to say its a necessary evil.

Withdrawn

• ## Modifying Elements of a 'Range' Argument

Dear VBA Gurus,

I need to pass a Range argument 's' to a caller/main public function ZZ, increment an individual element within 's' by an amount delta within ZZ, and subsequently pass this MODIFIED 's' as a Range argument to another called function 'uncdp', as skeletally presented in code below:

===================================

====================================

However, an error is thrown up at the code statement:

s(ID) = s(ID) + delta

Why am I getting this error?

i) Can't an element of a Range argument ('s' in code above) be modified within its own public function (ZZ above)?

ii) Or will I need to declare via 'Dim ...' a new Range variable, say 'ss', within ZZ, assign 's' to 'ss', increment element 'ss(ID)', and then pass this modified 'ss' Range object as an argument to called public function:
uncdp(s, r, a, time) ?

iii) Or is there a more efficient method?

Please advise with some code corrections/suggestions, where possible.

Thanks.

• ## Getting Size of a 2-D Array

Say I have:

Public Function xyz(ss As Range, rr As Range) as Double

with arguments 'ss' and 'rr' being 5R-by-6C and 6R-by-20C arrays respectively.

How do I assign each array's #Rows and/or #Cols to integer variables declared within the functions?

Tried using property 'RowCount' but got an error.

Please advise... Thanks

• ## Variable Scope

Re: Variable Scope

Hi JIUK,

Thanks, dude... You rock!

Cheers!
Om

• ## Variable Scope

For 2 public functions, one 'caller' & the other 'called', can I have LOCAL variables with the same name in both [as long as I do NOT pass these similarly named LOCAL variables as arguments from 'caller' to 'called' function]?

Better yet, as in other programming languages, can one declare GLOBAL-scope variables within a 'main' function in Excel VBA so that they may be accessed/manipulated by other sub-/'called' functions without having to pass these Global variables explicitly as arguments to each sub-/'called' function?

Please advise... Thanks.

• ## ReDim for 2-D Arrays

Please correct me if I'm wrong, but as I understand it, for a 1-D array:

ReDim V(10) <<--- gives an 11-element array, with individual elements referenced V(0) to V(10)

while

ReDim V(1 To 10) <<--- gives a 10-element array, with individual elements referenced V(1) to V(10)(called "Option Base 1" or similar in technical speak, I think)

===============

For a 2-D array,

ReDim W(10, 5) <<--- gives a 10R-by-5C array with "Option Base 1" the default option, correct? So that the first element of W is referenced W(1, 1)?

Or do we need to explicitly declare: ReDim W(1 To 10, 1 To 5)?

Thanks, and apologies if the question sounds silly...

• ## Caller/Called Function Location in Project Explorer

If I have 2 public functions (PF) - one 'ABC' main function which calls another 'xyz' function, where should the two be located in Excel VBA's Project Explorer window?

1) Both in the same module, i.e. Module1?

OR

2) 1) Both in different modules, i.e. 'ABC' in Module1 and 'xyz' in Module2?

OR

3) Some other relative location of the 2 PFs

Please advise... Thanks.

• ## Populating a 2-D array with 1-D Columns

Re: Populating a 2-D array with 1-D Columns

About my impatience, point noted and understood. Shall not happen again... :redface:

• ## Populating a 2-D array with 1-D Columns

Re: Populating a 2-D array with 1-D Columns

Someone, anyone, please assist... thanks. Usually, I post a query on this excellent forum and it gets answered by some kind soul - with generic code and all - well under an hour, but this time round...

:?