CODEWITHSUNDEEP
classexcelmodulesubroutinevba
Amir
Amir

Reputation: 147

VBA: How can I get a variable from a class?

I have made the class named 'class1':

Private ka As Variant
Private ra As Variant

Public Property Get kolom() As Variant
    kolom = ka
End Property

Public Property Get rij() As Variant
    rij = ra
End Property

Public Property Let kolom(value As Variant)
    ka = value
End Property

Public Property Let rij(value As Variant)
    ra = value
End Property

Then in a normal module sub I defined the variables:

Public Sub MakeArray(OutputSheet As Variant)
Dim igeg As Class1
Set igeg = New Class1
igeg.kolom = 47
igeg.rij = 559
End Sub

Now in a new sub I want to get the values 47 and 559 but I cannot figure out how to do that. I tried the following but ik gives no value:

sub test()
Dim igeg As Class1
newkolom = igeg.kolom
Msgbox igeg.kolom
End sub

or tried the following without result:

Public Function Test1() As Class1
Set Test1 = New Class1

End Function

Public Sub test()
Dim newigeg As Class1
Set newigeg = Test1

Msgbox igeg.kolom
End sub

Actually the idea is that I would like to use the value calculated in sub 1 in another sub. How can i get the value from the class module or are there better ways. Don't want to use call sub(value).

Thank you for your help in advance!

Amir

Upvotes: 1

Views: 3805

Answers (2)

bonCodigo
bonCodigo

Reputation: 14361

You call makeArray() within test(). Why don't you use makeArray() as a function ? Because Function can return an object/value. It's much recommended to use a function in your case.

Try the following:

Public Function MakeArray(ByRef OutputSheet As Variant) as Class1
Dim igeg As Class1
Set igeg = New Class1
igeg.kolom = 47
igeg.rij = 559
set MakeArray = igeg
End Function


sub test()
Dim igeg As Class1
set igeg = MakeArray(myvar)
Msgbox igeg.kolom
End sub

If using just subs then:

Public Sub MakeArray(ByRef OutputSheet As Variant, ByRef igeg As Class1)
 igeg.kolom = 47
 igeg.rij = 559
End Sub


Sub test()
 Dim myvar As Variant
 Dim igeg As Class1

  Set igeg = New Class1

  Call MakeArray(myvar, igeg)
  MsgBox igeg.kolom
End Sub

Upvotes: 2

Sorceri
Sorceri

Reputation: 8053

You have to make the instance of the class global or make the class static. Below you setup your variables for the class but you instatiate the class in the method therefore it falls out of scope when the method finishes.

Public Sub MakeArray(OutputSheet As Variant)
Dim igeg As Class1
Set igeg = New Class1
igeg.kolom = 47
igeg.rij = 559
End Sub

Upvotes: 0

Related Questions