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


    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, 8) 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, 8) 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.



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



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

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

    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.

    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?



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

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



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


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


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



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

    Code
    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:


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


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


    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:



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

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