Posts by schizoidman

Re: Manipulating ranges with functions and eliminating "memory"

Hi Derk,

Thank you. That did the trick. However, I still don't understand why dist() was being interpreted as a row vector?

Shouldn't the interpretation simply be the way I select the cells for the output?

Thanks.

Hi,

I'm a relative VB newbie (< 3 months), though I do have an extensive programming background.

I am trying to write an Excel function that accepts a range as an input, and calculates a range (of similar dimensions) as an output. However, my results aren't quite what I expect.

The output range in the following program is simply replicating the value for the first cell of the input range repeatedly. I am using the array enter (Shift-Ctrl-Enter), but this is not helping. I would expect the output range to be the cumulative sum of the input range.

Any thoughts?

Thanks in advance,
Schiz

Public Function LossDist(pktm As Range) As Variant


Dim j As Integer, numAssets As Integer
Dim dist() As Double
numAssets = pktm.Rows.Count
ReDim dist(1 To numAssets) As Double


For j = LBound(dist) To UBound(dist)
    If j = 1 Then
        dist(j) = pktm(j)
    End If
    If j > 1 Then
        dist(j) = dist(j - 1) + pktm(j)
    End If
Next j
                            
LossDist = dist


End Function

Re: VBA Function: One range as input, two ranges as output

Hi Aaron,

Thank you very much for your example. It is immensely useful. You have essentially provided me with the complete solution to my problem.

Am I correct in assuming that with array enter (Shift-Ctrl-Enter), the entire array will be calculated only once and each cell will be assigned one element from the array, i.e. the function is doing exactly what the procedure does and there are not as many function calls as there are cells?

My final spreadsheet is going to be huge and I need something that is computationally inexpensive.

On a different note, is there a work around to using the Variant data type? Perhaps it's the C programmer in me, but I don't like non-explicit data types! : D

Thank you very much.
Schiz

Re: VBA Function: One range as input, two ranges as output

Hi Robert,

Thank you for the reply. I'm attaching a sample Excel sheet that I prepared. I have written a procedure that reads in two arrays as inputs and writes two arrays as outputs. Both the input array and output array have the same dimensions.

I would like to enhance this and make it a function instead for added flexibility, even though the trade-off will be computational efficiency.

I would appreciate any suggestions.

Thank you.

Hi,

I am am relatively new to VB and I have come across a slight problem. I want to pass an array to a function, and am unclear how to do it. For example, the following declaration does not work:

Public Function xyz(a() as Variant) as Double

There is an additional requirement that I would like the output also to be a range of the same dimensions, typically one column and 100 rows. Is this possible?

A further twist is that the function I am writing calculates two values for every element in the input array and I need the output of them both.

An inelegant solution would be to write two separate functions, but I was wondering if it is possible to get two array outputs from a single function.

Thank you in advance.
Schiz