Calling Class module from a variable

[AdamC]

hi im lookin at a way of improving part of my program, i currenly have multiple class modules for differnt programs which users use.

at pressent the user selects the program they wish to run and it runs though a select case (as shown below) to find the class to call

jobrun = databaserecord.rsselectjobrun.Fields("Jobname")

Select Case jobrun

Case "JOB1":

Dim myclass As New JOB1

Sum = CallByName(myclass, "runjob", VbMethod)

Case "JOB2":

Dim myclass1 As New JOB2

Sum = CallByName(myclass1, "runjob", VbMethod)

Case "JOB3":

Dim myclass2 As New JOB3

Sum = CallByName(myclass2, "runjob", VbMethod)

and so forth

is there a way to hold the class name as a variable and then call the "runjob" public sub in the class

i have tired the below but i just get the error 'user-defind type not defined'

Dim myclass As Object
dim jobname as string

jobname = "JOB1"

Set myclass = New jobname

Sum = CallByName(myclass, "runjob", VbMethod)

Set myclass = Nothing

I would be gratefull for any help on the matter or if you can think of a more productive way of solving this issue

Many Thanks Adam

[WizBang]

The first thing that comes to mind is to put all public methods in one class. Then you can use the string to determine which method to execute.

One other possibility might be to use CreateObject(), though I think your classes would then need to be compiled into one or more DLL files (separately from the exe). However, if I'm not mistaken, this would mean late binding.