1

I have the following VBA code:

Option Explicit

Private a(2) as Double
Private b(2) as Double

Public Function Hello(X1 As Double, X2 As Double) As Double

    a(1) = X1 + X2
    a(2) = X1/X2
    b(1) = X1
    b(2) = X2^2

Hello = a(1)+a(2)+b(1)+b(2)

End Function

Within the function Hello I have defined a(1),a(2),b(1),b(2).

However, I want to make some function or sub-routine that accepts X1 and X2 as arguments and spits out the values for a(1),a(2),b(1),b(2). This is because I use the above definitions for a(1),a(2),b(1),b(2) in about 20 functions in my module and would like to avoid having to do the following in each function that I use thesis in:

    a(1) = X1 + X2
    a(2) = X1/X2
    b(1) = X1
    b(2) = X2^2
Improve this question
7
  • @MitchWheat I want some function or sub-routine that defines a(1),a(2),b(1),b(2) as stated above that I can call within the 20 functions that need those definitions. As far as functions go I only know how to make them output 1 value, I don't know how to get them to define multiple objects. Commented Sep 5, 2012 at 5:49
  • @Harokitty: Even I am confused as to what exactly do you want? I want some function or sub-routine that defines a(1),a(2),b(1),b(2) as stated above You are not defining but assigning in the above code... There is a difference between the two. Commented Sep 5, 2012 at 7:12
  • @SiddharthRout Ok. Right now I have 20 functions. In each function I type those 4 lines where I assign values to a(1), a(2), b(1), b(2). I want some way so I don't have to type those 4 lines in my 20 functions. Note that in each of these 20 functions, the values of X1 and X2 are different so I can't just assign values to them outside of all 20 functions. Commented Sep 5, 2012 at 7:25
  • 1
    You don't need 20 functions if all you are doing is assigning values to a(1),a(2),b(1),b(2) You can simply use 1 function and then using conditional code, assign values to them. Commented Sep 5, 2012 at 7:30
  • 1
    I would create a class object that holds a() and b() and act upon them with the methods in the class. cpearson.com/excel/classes.aspx Commented Sep 5, 2012 at 12:55

2 Answers 2

Reset to default
1

I would structure this with a class object and properties/methods. Try this in Class1

Option Explicit

Private a(2) As Double, b(2) As Double

Public Sub Initialize(ByVal x1 As Double, ByVal x2 As Double)
    a(1) = x1 + x2
    a(2) = x1 / x2
    b(1) = x1
    b(2) = x2 ^ 2
End Sub

Public Property Get Hello() As Double
    Hello = a(1) + a(2) + b(1) + b(2)
End Property

Public Property Get Goodbye() As Double
    Goodbye = a(1) - a(2) + b(1) - b(2)
End Property

Public Function BusyWork(ByVal t As Double) As Double
    Dim i As Integer, x As Double
    x = 0#
    For i = 1 To 2
        x = x + (a(i) - t) * (b(i) - t)
    Next i
    BusyWork = Sqr(x)
End Function

And then use it in a module as

Public Sub UseClass()
    Dim c As New Class1

    c.Initialize 10.6, -4#

    Debug.Print c.Hello
    Debug.Print c.Goodbye
    Debug.Print c.BusyWork(-1#)
End Sub

You can read more about VBA classes here:

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

If it is just assigning values to the array from different function then you can create a common procedure and call them from all the functions. See this example

Option Explicit

Dim a(2) As Double, b(2) As Double
Dim Hello As Double

Function FOne()
    '
    '~~> Rest of the code
    '      
    Sample 1,2
End Function

Function FTwo()
    '
    '~~> Rest of the code
    '      
    Sample 2,3
End Function   
'
'~~> Rest of the functions
'   
Function FTwenty()
    '
    '~~> Rest of the code
    '     
    Sample 3.2 ,4.2
End Function

Sub Sample(X1 As Double, X2 As Double)
    a(1) = X1 + X2: a(2) = X1 / X2
    b(1) = X1: b(2) = X2 ^ 2

    Hello = a(1) + a(2) + b(1) + b(2)
End Sub
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.