CODEWITHSUNDEEP
vbaexcel
user1125946
user1125946

Reputation:

Defining multiple VBA objects within one function or sub-routine?

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

Upvotes: 1

Views: 2260

Answers (2)

John Alexiou
John Alexiou

Reputation: 29244

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:

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149295

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

Upvotes: 0

Related Questions