• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 550
  • Last Modified:

universal variable

i need a "universal" variable, to pass values from an activex DLL made in VB6, and my application, made in excel VBA. is there a way?
specifically, i have written a progress bar in my DLL, and i call and update its progress from within VBA. but i have a cancel button in the progressbar form in the DLL. i need that if i click it, the DLL form will return a StopProcedure that could be read by a loop in my VBA application. ideas?
0
newyuppie
Asked:
newyuppie
  • 9
  • 6
  • 6
  • +2
1 Solution
 
BrianGEFF719Commented:
In Visual Basic "Variant" is the universal variable.


-Brian
0
 
newyuppieAuthor Commented:
can this excersice be done:

in VB6 write a DLL that initializes a variable called myVar, with a value of 3. compile.
in excel, write a VBA program that references that DLL, and debug.print myVar (without initializing it) will return 3.
is that possible?
0
 
BrianGEFF719Commented:
Kinda, but not exactly.

You could write a function in the DLL.

Private MyVar as Variant
Public Function MyVarVal() as Variant
 myVarVal = MyVar
End Function

calling from VBA

msgbox myObject.MyVarVal



you could do something like that, but you would need to call the function. Or are you attempting to create a static value, because if you create a Constant That is Public, you can view the value from VBA. What are you trying to accomplish?

-
brian
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
newyuppieAuthor Commented:
i have a progressbar form made DLL
and i want the user to be able to cancel the process via a cancel button in that form
but the loop of the process is in VBA, not in the actual DLL. the DLL only contains the form of the progressbar

i need a loop in VBA

do while keepdoing = true
doEvents
...
...
loop

in the DLL, the progressbar form has a cancel button that will assign keepdoing = false, and i want VBA to read that value immediately to get out of the loop in an automatic fashion.
is it possible? or better suggestion maybe?
0
 
BrianGEFF719Commented:
okay, you can do this in VBA. Any reason why you want to use an ActiveX DLL?

-brian
0
 
BrianGEFF719Commented:
You can create a form in VBA.
0
 
newyuppieAuthor Commented:
i have my reasons.
main reason, deploying many VBA applications that will use the same progressbar, and ability to change the code once and only deploy DLL updates at a later stage.
i know i can VBA it, i just prefer to DLL it...
0
 
BrianGEFF719Commented:
Well you can easily pass updates to the DLL from VBA. Just create functions


DLL Example:

Private Running As Boolean

Public Sub StopProgessBar()
if running then
 running = false
 myForm.Hide
end if
End Sub

Public Sub StartProgressBar()
if not running then
 running = true
 myForm.show
 myForm.prgs.value = 0
 myForm.prgs.min = 1
 myForm.prgs.max = 100
end if
end sub

Public Sub SetProgress(byVal PrgsVal as integer)
if running then
 if not (myform.prgs.value < PrgsVal) Then 'Prevent from setting a val too high and crashing DLL
 myform.prgs.value = prgsval
 end if
end if
end sub



vba example

set x as new myDLL
call x.StartProgressBar

for i = 1 to 100
 call x.setProgress(i)
next i

call x.stopprogressbar




Hope that sets you on the right track

-brian
0
 
BrianGEFF719Commented:
Sorry you need to change this line:

if (myform.prgs.max < PrgsVal) Then 'Prevent from setting a val too high and crashing DLL

to

if not (myForm.prgs.max < PrgsVal) then
0
 
Erick37Commented:
And to handle the user pressing the Cancel button you should create an event that the DLL raises once the button is pressed.

Public Event CancelPressed()


Private Sub cmdCancel_Click()
    RaiseEvent CancelPressed
End Sub


'============

in your VBA code:

Private Sub myDLL_CancelPressed()
    keepdoing = False
End Sub
0
 
newyuppieAuthor Commented:
where should i put the public event? the form or class module or module? how do i close the event? end event?
0
 
MikeAngelCommented:
A stupid answer (maybe): save the variable value in a file and whenever you need the value, you can call this file. Use de "Open "file" for output as #1".
0
 
Erick37Commented:
The event is declared in your DLL in the declarations section:

Public Event CancelPressed() '<< that's all you need

in your DLL, to raise the event call
RaiseEvent CancelPressed()

In your program which references the DLL you will receive the event by its name.
To receive the event make sure you use the WithEvents keyword:

Private WithEvents TEST As MyDLL.MyInterface

Here is a pretty good How-To lesson on DLLs
http://www.thevbzone.com/l_dll.htm
0
 
newyuppieAuthor Commented:
erick, great link and great information. i am just having some issues adapting that to my code please help me:

in my DLL, the progressbar userform starts with

Public Event UserCancel()

then i have a cancel button in the form
Private Sub CancelButton_Click()
    RaiseEvent UserCancel
End Sub

now on my VBA i have the call:

Private KeepDoing as boolean

Sub TEST2()
Dim theProgress As New JACM.ProgressBar
KeepDoing = true
Do While KeepDoing
            DoEvents
             theProgress.UpdateProgress (i)
             i=i+1
Loop
End Sub

Private Sub JACM_UserCancel()
    keepdoing = False
End Sub

does this sound about right?? its not working for me, what am i doing wrong...?
0
 
Erick37Commented:
I believe that the declaration
Public Event UserCancel()
should be in your Class module (not the form) so it is associated with the JACM object.

When in your VBA window, press F2 to bring up the Object Browser.  Select your DLL from the dropdown list, and you should see all the functions, events, etc...
0
 
newyuppieAuthor Commented:
i declared Public Event UserCancel() in the class module, and when i compile the DLL error: "Event not found" higlighting
Private Sub CancelButton_Click()    
    RaiseEvent UserCancel
End Sub
0
 
Erick37Commented:
Yep, my mistake...

It appears to do this you need to declare an event in the form AND in the class module.
A much better decription here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon98/html/vbconaddingeventtooledllproject.asp

'-------------------------------------------------
'Form Code

Event UserCancel

Private Sub cmdCancel_Click() 'User presses the Cancel button
    'raise the event in the module which created the form
    RaiseEvent UserCancel
    Unload Me
End Sub

'--------------------------------------------------
'Class Module code

Private WithEvents frm As Form1 '<<the name of your form
Public Event UserCancel

Public Sub Show()
    'Show the form
    Set frm = New Form1
    frm.Show
End Sub

Private Sub frm_UserCancel()  'Event raised from the form
    RaiseEvent UserCancel  'Pass it on
    Set frm = Nothing
End Sub

'This should work for you.
   
0
 
newyuppieAuthor Commented:
ok erick, we're getting close..
that code works now on my DLL when i test it. the event is raised alright.
now how do i catch that UserCancel event in my VBA app?
remember in my VBA app y do not call the dll userform direclty, i call a subroutine on the DLL that calls the userform, namely the routine Public Sub Show() that you wrote on the code above.
0
 
Erick37Commented:
UserForm creates->DLL creates->Form

Form raises event to->DLL raises event to->UserForm


'===DLL Class Module===
'Remember to catch the UserCancel event from the form,
'    then raise the event to your client

Private Sub frm_UserCancel()  'Event raised from the form
   
    RaiseEvent UserCancel  'Pass it on
    ...
End Sub


'===UserForm Code===

Private WithEvents jacm As JACM.Classname  '<<< WithEvents (replace "classname" with your class)
Private KeepDoing as boolean

Private Sub UserForm_Activate()
    Set jacm = New JACM.Classname
End Sub

Private Sub jacm_UserCancel()  '<< this is the event you raised from the DLL class module.
    keepdoing = False
End Sub
0
 
newyuppieAuthor Commented:
perfect, exactly what i needed to know.
one last issue, imagine i don't use a form in VBA to call the dll class. sometimes i would like the progressbar to show not calling it from a form. i read that i cannot catch events if i code outise class modules or forms. can u think of any ideas to work around this to catch the event in my bas module?
0
 
Erick37Commented:
Hi newyuppie

You are correct about not being able to receive events from a standard module.  I do not know any way around this limitation.

The only thing I could think of is to somehow poll the dll at some intervel from your application to see if the form has been dismissed.
0
 
harnalCommented:
Variant is NOT a "universal variable", variant is a data type.  And if what newyuppie wants is a variable that maintains state across forms/modules/classes etc. he should use the 'global' modifier.


global <variable> as <datatype>
0
 
newyuppieAuthor Commented:
harnal, i want a variable that maintains state not only across forms,modules and classes, but also across components (different dlls, modules, vba forms, etc). thats why i said i needed a "universal" variable. ik think i will follow what erick told me and do it via events, which i learned now thanks to him, so im awarding him with the points.
my only limitation now is that i need to capture that event in a bas module because i dont want any forms in my vba app, but if you can think of an answer just for the heck of it post it for me, and maybe i can open up a new question with the points for this extension. thank you all!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 9
  • 6
  • 6
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now