×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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

CODE --> VBA

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

CODE --> VBA

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 

RE: Sending an Array into an Excel VBA function and Also Getting an Array Out

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/

RE: Sending an Array into an Excel VBA function and Also Getting an Array Out

(OP)
Thank you for the input. I deleted the function declaration.

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

POI_Stem = 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)


RE: Sending an Array into an Excel VBA function and Also Getting an Array Out

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/

RE: Sending an Array into an Excel VBA function and Also Getting an Array Out

(OP)
Thanks IDS!

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

RE: Sending an Array into an Excel VBA function and Also Getting an Array Out

(OP)
IDS.....you are genius!! It worked!!

Thank you!

RE: Sending an Array into an Excel VBA function and Also Getting an Array Out

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/

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close