PDA

View Full Version : Public Variables don't work



bassnsjp
06-10-2009, 06:53 AM
I'm using MS Office 2003 in an Windows XP Pro environment.

I'm developing an inventory set of macros. I have it working in a manner that requires manual intervention and want to integrate the modules together to automate the process. Now, in the process of integrating the modules I've declared variables that exist in more than one module as Public. I did so in the Main (master) module. However, when I go to use one of the variables in another module I get ambiguous name errors. Does Public delcaration really work, shouldn't I be able to reference the Public variable in any module within the same project? If I declare a variable once again as Public in the respective module(s) I don't get the error. Must I declare Public variables in every module? Thanks in advance for your assistance.

Public icnt as integer
Sub ABC()

icnt = icnt + 20
Call XYZ

end sub

Sub XYZ()

if icnt > 20 then
' do something
end if

end sub
Steve

lucas
06-10-2009, 08:00 AM
Im guessing there is somthing else wrong. This works:
Option Explicit
Public icnt As Integer
Sub ABC()
icnt = 10
icnt = icnt + 20
MsgBox icnt
End Sub
Sub XYZ()
icnt = 21
If icnt > 20 Then
MsgBox "over"
End If
End Sub

lucas
06-10-2009, 08:01 AM
this works:
Option Explicit
Public icnt As Integer
Sub ABC()
icnt = 10
icnt = icnt + 20
Call XYZ
End Sub
Sub XYZ()
If icnt > 20 Then
MsgBox "over"
End If
End Sub

mdmackillop
06-10-2009, 08:15 AM
You need to run ABC twice to get a result > 20

bassnsjp
06-10-2009, 01:30 PM
You are correct. My mistake for not providing a more accurate example. Lets say that Sub ABC is in module TestABC while Sub XYZ is in module TestXYZ. Both of which are in Project Test. I don't have access to the PC where the code is located which is why I'm using this example. In this scenario when XYZ is executed it gets an ambiguous name error.

ProjectTest.xls:

Module TestABC:

Option Explicit
Public icnt As Integer
Sub ABC()
icnt = 10
icnt = icnt + 20
Call XYZ
End Sub

Module TestXYZ:


Option Explicit
Sub XYZ()
If icnt > 20 Then
MsgBox "over"
End If
End Sub

mdmackillop
06-10-2009, 02:24 PM
Try this

bassnsjp
06-11-2009, 06:00 AM
MDMack,Yep that's the way I envisioned it should work, but for some reason I'm getting the ambiguous name error. I wonder if I need to comment out all Public statements in the subroutine that is being called from the Main. However, it does appear that the error is associated with a particular variable that is only declared as Public in the Main subroutine. By the way, I like how you setup the Run ABC button. How is that done? Also, thank you very much for taking the time to help in this matter I truly appreciate your time and efforts. Steve

lucas
06-11-2009, 06:08 AM
ambiguous name error usually means you have two subs with the same name.

mdmackillop
06-11-2009, 06:09 AM
To add a button, Open the Forms toolbar, Click the button tool and draw a button. You'll be prompted to assign a macro
Also see here (http://vbaexpress.com/forum/showthread.php?t=11142)

bassnsjp
06-11-2009, 06:59 AM
Lucas, definitely do not have two subs named the same. :banghead: :help

MDMack, thanks, pretty nifty. :thumb

bassnsjp
06-12-2009, 12:57 PM
Lucas/MDMack,

Just wanted to let you know that I got the public statements to work. I moved all the Public statements to the very first subroutine and did not remove all other Public statements from the various subroutines that were called. Once I did everything seem to function as designed. Took some time to get it to work. THANK YOU both for YOUR assistance.

Steve