Posts by Om Avataar

Need some help from any of ye VBA gurus out there...

I need to populate a 2-D array ZZ(N, M), EXCEPT I do NOT have a ready range of pre-calculated cells on the worksheet. I need a ‘FOR’ loop to call another Public Function ‘probcalc’, with header:

‘Public Function probcalc(s As Range) as Variant’

a total of M times, and each iteration i=1 To M results in ‘probcalc’ returning an N-element COLUMN vector - which is conveniently assigned to column i of array ZZ.

Please note that array ZZ is just an INTERMEDIATE STORAGE array of sorts which I do NOT want printed/reproduced on the Excel worksheet - elements of populated array ZZ will selectively be used in additional code thereafter to arrive at the final answer.

Apologies if my explanation sounds too gibberish, but hope you VBA gurus will easily understand - and advise accordingly with some code.

Thanks a million in advance...

- Om Avataar

Re: ReDim V(1 To 10): Gives Row or Column?

Hi...

Thanks, everyone, for your advice and suggestions.

Also, Andy Pope, much obliged for your set of 2 alternative codes to tackle my problem earlier. I used the 2nd code you provided as I found it a little easier to understand – and it works beautifully. Gratzi…

In another worksheet, my end objective remains much the same, i.e. populate a newly dimensioned array ZZ(N, M), EXCEPT this time I do NOT have a ready range of pre-calculated cells in the worksheet.

Now, I require the ‘FOR’ loop to call another Public Function ‘probcalc’, with header:

‘Public Function probcalc(s As Range) as Variant’

a total of M times, and each iteration i=1 To M results in ‘probcalc’ returning an N-element COLUMN vector - which is conveniently assigned to column i of array ZZ.

How does one modify (Andy Pope's earlier) code below to incorporate this function call feature?

P.S. Also, I'm afraid the 'Debug.Print' functionality called upon in the code below may not be suitable for my new purpose, as array ZZ above is ONLY an INTERMEDIATE STORAGE array of sorts which I do NOT want printed/reproduced on the Excel worksheet at all - reason being elements of populated array ZZ will selectively be used in additional code thereafter to arrive at the final answer.

Apologies if my explanation above sounds too gibberish, but hope you VBA gurus will easily understand what I need to program - and advise accordingly.

Thanks a million in advance... - Om Avataar

Sub Y() 
    Dim V(10, 8) 
    Dim intLoop1 As Integer 
    Dim intLoop2 As Integer 
    Dim rngTemp As Range 
     
    For Each rngTemp In Range("A1:H10") 
        V(rngTemp.Row, rngTemp.Column) = rngTemp.Value 
    Next rngTemp 
     
    For intLoop1 = LBound(V, 1) To UBound(V, 1) 
        For intLoop2 = LBound(V, 2) To UBound(V, 2) 
            Debug.Print intLoop1, intLoop2, V(intLoop1, intLoop2) 
        Next intLoop2 
    Next intLoop1 
End Sub

ReDim V(1 To 10): Gives Row or Column?

Please advise... Thanks.

Q1) Are the following two VBA statements equivalent?
ReDim V(10)
ReDim V(1 To 10)

Q2) Assume: ReDim V(1 To 10)
For matrix multiplication reasons, is array V as dimensioned above treated by Excel VBA as a 10-element ROW vector, or as a COLUMN vector?
P.S. I know that ReDim Z(10, yields a 10Row-by-8Column array Z.

Q3) Assume I have a set of 8 10-element COLUMN vectors A through H, and I have newly dimensioned array Z(10, with all elements defaulted to 0.

In a FOR loop format, how do I populate elements of array Z by successively assigning column vectors A though H to the respective columns of array Z? Could you show the sample code to effect such an assignment?

Thanks a million in advance...

There must be a faster way than that shown below, right? Something involving:

'For Each.....rngTemp....'

- saw it somewhere in passing; don't quite remember. Please advise... Thanks.

Dim V() as Double
Dim i As Long


ReDim V(10, 1)
        
For i = 1 To 10
    V(i) = 0
Next i

Re: Iterative Matrix Generation

First off, Derk (the Professor)'s one of the BEST advisors I have come across here! (Based on responses to my previous help postings) Hats off to you, O' Wise One...

Next, if someone/anyone could provide some advise (with illustrative sample code) on this thread, I would be eternally uber-grateful...

Gratzi!
- Om Avataar

Hi VBA Gurus,

The code snippet shown below essentially resides within a 'main' Public Function (PF) which calls a 'sub' PF - 'ColGenerator' - that in turn returns a column vector stored in array variable 'ColVec'. This sequence occurs in a 10-iteration 'For' loop and the end objective is to generate a master matrix formed of the 10 column vectors returned by repeated calls to 'sub' PF 'ColGenerator'.

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

For i = 1 To 10
        ColVec = ColGenerator(s, rr, i)   ' Public Function ColGenerator returns an N-by-1 column vector
        masterArr(??, i) = ColVec          ' ?? = Need Help!! Please suggest right/better code to effect 'masterArr' generation
Next i

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

Please advise on the following 3 queries:

1) Is the statement: ColVec = ColGenerator(s, rr, i)
the correct way a (main) PF calls another (sub) PF? If not, what is the correct syntax?
Also, is it imperative for both caller and called PFs to reside in the same 'Module' in the context of Project Explorer?

2) How should I be declaring and dimensioning array variable 'masterArr' above? Is it 'Dim masterArr As Variant'?
OR is it 'Dim masterArr() As Double' followed by ReDim masterArr(N, 10)?

3) What is the right code to effect column-by-column generation of 'masterArr'?

Thanks a million in advance, and apologies if my problem desription sounded too gibberish.

Solved

Re: Returning Arrays from Called to Caller Public Functions

Hi iwrk4dedpr... First off, thanks for your reply. Would you happen to have a very basic, sample code illustrating the syntax for a Public Function returning an array?

It would be very helpful indeed to this humble VBA beginner...

Thanks, and cheers.

Can a sub-Public Function, called from another 'main' Public Function, return an array (via type Range or Variant) to the 'main' Public Function? In other words,

'Public Function xyz(s As Range) as Range'
OR
'Public Function xyz(s As Range) as Variant'

- Are either of these valid?

Thanks.

hi all ye vba gurus,

s is a 10Rx2C Range, and r is a 2Rx8C Range; matrix multiplication of s and r should yield a 10Rx8C array h which is the object of interest

why doesn't the code below work? also, if the dimensions of s and r are variable [subject to: #columns in s = #rows in r], how do I incorporate this into the code to make it more versatile?

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

Public Function pqr(s As Range, r As Range) As Double
    Dim h() As Double
    
    ReDim h(10, 8)
        
    h = Application.WorksheetFunction.MMult(s, r)
    pqr = h(5, 5)
End Function

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

Please advise soonest possible... Thanks a million in advance.

Hi... I have a 5Row-by-NCol Array which I need matrix-multiplied to a 1Row-by-5Col Array to yield an NRow-by-1Col Array. Mathematically I would need to transpose both matrices to yield an NRow-by-5Col and a 5Row-by-1Col Array, which would then yield the requisite NRow-by-1Col Array.

Problem is, I do not know the VBA code to effect 2 important things:

i) Transpose of an m-by-n matrix (m,n both NOT equal 1)
ii) Matrix Multiplication between an f-by-g & a g-by-h matrix to yield an f-by-h matrix

Also, when one uses 'Dim xyz() As Double' followed by 'ReDim xyz(1 to N)', is array xyz then by default a row vector or a column vector? I suspect xyz thus declared/ dimensioned would be a row vector... Please clarify.

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

Public Function udp(s As Range) As Double
    Dim N As Long
    Dim y() As Double, X() As Double, result() As Double


    N = Application.WorksheetFunction.Count(s)
    ReDim y(5, N)
    ReDim X(1 To 5)
    ReDim result(N, 1)
    
    For M = -2 To 2
        For i = 1 To N
            y(M+3, i) = Exp(M) * Application.WorksheetFunction.NormSDist(s(i))
        Next i
    Next M
        
    X(1) = -2.0201828705
    X(2) = -0.9585724646
    X(3) = 0
    X(4) = 0.9585724646
    X(5) = 2.0201828705


    '
    ' Matrix Multiplication Code and 'result' matrix to be inserted here by a gracious VBA Guru
    '
    
    udp = Application.WorksheetFunction.Sum(result)
End Function

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

Please assist, and apologies if my problem statement above is a little confusing. Thanks in advance...

Hi all ye VBA gurus,

Got a very basic question: How does one determine which of Excel's in-built functions require the 'Application.WorksheetFunction.' prefix to work - and which don't?

For instance, in the 2 basic functions presented below, the first just will NOT work with the prefix, while the second MUST have the prefix in order to work...

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

Public Function Lexp(a As Double) As Double
    ' Lexp = Application.WorksheetFunction.Exp(a)  <--- Does NOT work
    Lexp = Exp(a)
End Function


Public Function Lnormsinv(a As Double) As Double
    Lnormsinv = Application.WorksheetFunction.NormSInv(a)
    ' Lnormsinv = NormSInv(a)  <--- Does NOT work
End Function

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

What gives? Within Excel or MS Visual Basic's Help utilities, is there a listing of functions which do require the 'Application.WorksheetFunction.' prefix?

Please advise... Thanks in advance.

Thanks for your reply to my earlier query, Andy Pope.

One NEW query... I need to write a Public Function which takes 2 Range arguments with the same number of elements, and a new array/range is created within the function by the division of each element in the first array argument by corresponding elements in the second array argument.

Code I wrote in an attempt to effect this is shown below, but it doesn't seem to work:

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

Public Function t(s As Range, r As Range) As Double
    Dim N As Long, i As Long
    Dim h As Range


    N = Application.WorksheetFunction.Count(s)


    For i = 1 To N
        h(i) = s(i) / (1 - r(i))
    Next i
     
    t = Application.WorksheetFunction.Product(h)
End Function

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

Hope my pre-code explanation as well as the code itself makes some sense to the reader/you VBA gurus. What am I coding wrongly? I'll be the first to admit I haven't quite grasped 'Dim xyz As Range' variables yet.

In fact, I'm not even sure if variable 'h' above should more appropriately be dimensioned as 'Dim h() As Double' instead of as 'Dim h As Range' as shown. How would you do this?

Please do assist this novice, and apologies if my query sounds just plain silly.

Thanks a million in advance...

Hi, all ye VBA Gurus out there... Please bestow upon this lowly VBA philistine some light of your infinite wisdom.

Attached below, for your reference, is my sample code:

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

Public Function zaq(r As Range) As Double
    Dim i As Long
    Dim C As Range
    
    For i = 1 To 10
        C(i) = Application.WorksheetFunction.Ln(r(i))
    Next i
        
    zaq = Application.WorksheetFunction.Sum(C)
End Function

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

Ideally, I need function 'zaq' to take an array/range argument 'r', perform a mathematical operation on each element [Actual mathematical manipulations are more complex than the illustrative natural log function shown above], and return a floating point answer 'zaq'.

I'm having problems declaring array/Range variable 'C' in my Public Function. I keep getting a '#VALUE!' error display in the caller cell, with the following error message: 'A value used in the formula is of the wrong data type.'

Why is this? What am I doing wrong? Please help me...!!

Also:
i) What's the difference between the following 3 declarations?

'Dim CArr() as Double', 'Dim C as Variant', 'Dim C As Range'

ii) Which is the best for manipulating data in array form?

iii) Can a Public Function return a Range variable?

Thanks a million in advance...