Sending an Array into an Excel VBA function and Also Getting an Array Out 1

[JoelTXCive]

I am having problems sending an array into an excel function. I also may be having problems getting an array back out.

I'm trying to call a function named "Stem_Wind" and am passing several variables to this function. One of the variables is a 7x4 variant array.

After the function runs some calculations, the output of the function should be a 1x2 numeric array.

I think I have issues passing variables in to the function, but I probably have issues getting them back out too ...

Attached is the code... I can send more in text format if it helps.

Thank you in advance.

Here is the function CALL

'Lets redim the array to add 2 more columns for wind shear and moments
    ReDim Preserve StemArray(counter, 6)
    
    'Set up Some Variables to use in the loop
    Dim POI_Stem(2) As Double
    Dim PointX As Double
    
    'Loop through all my wall points and calculate shear and moments due to wind load
    For i = LBound(StemArray, 1) To UBound(StemArray, 1)
    
        PointX = StemArray(i, 4)
        
        POI_Stem = Stem_Wind(PointX, POI_Special, h_topfence, h_parapet, pressure_wind)
        
        StemArray(i, 5) = POI_Stem(1)
        StemArray(i, 6) = POI_Stem(2)
    Next i

And Here is the Actual Function:

Function Stem_Wind(PointX As Double, POI_Special() As Variant, h_topfence As Double, _
h_parapet As Double, pressure_wind As Double) As Double()
    '
    ' This function will calculate shear and moments due to a uniform wind load
    '
    '
    '       Written by JoelTxCIVE
    '
    '
    '       Last Modified 07/10/2018
    '
    
    'INPUT:
    'For each value sent into the function, we need to determine which region of the wall
    'we are in and then apply the proper equation to determine shear and moments.
    '
    'PointX        -    This is the point I am interested in.
    'POI_Special() -    This is a (7x4) variant matrix with string and numeric columns
    'h_topfence    -    Height of top of fence above wall
    'h_parapet     -    Height of parapet region
    'pressure_wind -    Uniform wind pressure
    '
    'OUTPUT:
    'A (1x2) array containing two numeric values.  Value 1 will be shear and Value 2 is moment.
    
        'We are going to use a select case operation to compare the PointX value
        'to the special points of interest array.
        
        Select Case PointX
             
            Case Is < POI_Special(3, 4)         'in the top fence
                Stem_Wind(1) = 0 'shear
                Stem_Wind(2) = 0 'moment
            
            
            Case Is = POI_Special(3, 4)         'at top of wall
                Stem_Wind(1) = h_topfence * pressure_wind        'shear
                Stem_Wind(2) = Stem_Wind(1) * h_topfence / 2     'moment
            
            
            Case Is < POI_Special(4, 4)         'between top of wall and nat grade
            Stem_Wind(1) = (h_topfence + PointX - POI_Special(3, 4)) * pressure_wind 'shear
            Stem_Wind(2) = Stem_Wind(1) * PointX / 2                                 'moment
            
            
            Case Is <= POI_Special(7, 4)        'below nat grade
            Stem_Wind(1) = (h_topfence + h_parapet) * pressure_wind               'shear
            Stem_Wind(2) = Stem_Wind(1) * (PointX - (h_topfence + h_parapet) / 2) 'moment
            
            
            Case Else
                Stem_Wind(1) = 0 'nothing
                Stem_Wind(2) = 0 'nothing
        End Select
          
End Function

 

[IDS]

Where does POI_Special come from? Are you sure it is a variant array, rather than a variant/range?

But I think your problem (at least one of them) is declaring the function as a Double array. Try changing:

) as Double()

to:

) as Variant

or just don't declare it, as Variant is the default.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[JoelTXCive]

Thank you for the input. I deleted the function declaration.

I get a compile error at this line.......

[highlight #FCE94F]POI_Stem[/highlight] = Stem_Wind(PointX, POI_Special, h_topfence, h_parapet, pressure_wind)

It says 'you can't assign a value to array'

'POI Special' is definitely an array. It contains special points of interest on a retaining wall where equations will change. (such as top of wall, natural grade height, top of footing)

 

[IDS]

OK, I should have noticed the POI_Stem declaration.

The message 'you can't assign a value to array' is pretty misleading. You can assign a function return value to an array, but you have to declare it as a variant object, without specifying what type of variant it is; so leave off the ().

Instead of:
Dim POI_Stem(2) As Double
use:
Dim POI_Stem as Variant

You then need to declare an array inside the function (say Dim POI_StemF(2) as Double), and the final lines of the function will be:
Stem_Wind = POI_StemF
Exit Function

Alternatively, you could pass POI_Stem as a function argument, and add the required values directly to POI_Stem, rather than Stem_Wind. The function return value could then be an error index (or even nothing).

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[JoelTXCive]

Thanks IDS!

I will give it a shot this evening after work and report back!

 

[JoelTXCive]

IDS.....you are genius!! It worked!!

Thank you!

 

[IDS]

I'm glad it worked.

No genius required. Just recalling having the same problem in the past.



Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/