?
Solved

universal variable

Posted on 2005-03-03
23
Medium Priority
?
545 Views
Last Modified: 2012-08-13
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
Comment
Question by:newyuppie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 6
  • +2
23 Comments
 
LVL 19

Expert Comment

by:BrianGEFF719
ID: 13456670
In Visual Basic "Variant" is the universal variable.


-Brian
0
 
LVL 13

Author Comment

by:newyuppie
ID: 13456693
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
 
LVL 19

Expert Comment

by:BrianGEFF719
ID: 13456704
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 13

Author Comment

by:newyuppie
ID: 13456729
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
 
LVL 19

Expert Comment

by:BrianGEFF719
ID: 13456735
okay, you can do this in VBA. Any reason why you want to use an ActiveX DLL?

-brian
0
 
LVL 19

Expert Comment

by:BrianGEFF719
ID: 13456737
You can create a form in VBA.
0
 
LVL 13

Author Comment

by:newyuppie
ID: 13456747
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
 
LVL 19

Expert Comment

by:BrianGEFF719
ID: 13456762
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
 
LVL 19

Expert Comment

by:BrianGEFF719
ID: 13456774
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
 
LVL 32

Expert Comment

by:Erick37
ID: 13458706
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
 
LVL 13

Author Comment

by:newyuppie
ID: 13459839
where should i put the public event? the form or class module or module? how do i close the event? end event?
0
 

Expert Comment

by:MikeAngel
ID: 13462043
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
 
LVL 32

Expert Comment

by:Erick37
ID: 13462203
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
 
LVL 13

Author Comment

by:newyuppie
ID: 13463799
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
 
LVL 32

Expert Comment

by:Erick37
ID: 13463844
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
 
LVL 13

Author Comment

by:newyuppie
ID: 13463903
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
 
LVL 32

Accepted Solution

by:
Erick37 earned 2000 total points
ID: 13464096
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
 
LVL 13

Author Comment

by:newyuppie
ID: 13467152
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
 
LVL 32

Expert Comment

by:Erick37
ID: 13467608
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
 
LVL 13

Author Comment

by:newyuppie
ID: 13467879
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
 
LVL 32

Expert Comment

by:Erick37
ID: 13477025
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
 
LVL 2

Expert Comment

by:harnal
ID: 13478301
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
 
LVL 13

Author Comment

by:newyuppie
ID: 13479547
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month8 days, 15 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question