Can't declare Public Constant in a macro in Excel?

[Sacto95827]

Ok...I have a macro I wrote which calls on other subs. I wanted to declare some global constants so I could change the variable easily if I had to change it later on. So this is what I have:

Sub MyMacro()

Public Const MATRIX As String = "Tab 1"
Public Const MATRIX2 As String = "Tab 2"

Call OtherSub
Call OtherSub2

End Sub

So basically I want to pass the constants MATRIX and MATRIX2 to the other subs. But everytime I try to run the debugger, I get the error:

Compile error:
Invalid attribute in Sub or Function

I am using Microsoft Excel 2003 SP1 and VB 6.3 Version 9972. Any ideas or tips would be greatly appreciated!

[DKenny]

Move your constant declaration outside the procedure. Put them at the top of the module.

[Sacto95827]

okay, so now I have it like this:

Public Const MATRIX As String = "Tab 1"
Public Const MATRIX2 As String = "Tab 2"

Sub MyMacro()

Call OtherSub
Call OtherSub2

End Sub

And it worked the first time I ran it (actually, with different combinations and movement of code, it will work for the first time and then won't work the next time...) but then when I call OtherSub and that sub calls for the Const MATRIX, I get the error:

Compile error:
Ambigous name detected: MATRIX

But then again, if I just call one of the constants in the first Sub MyMacro(), it will work.

Very stumped...

[DKenny]

Have you another declaration of MATRIX in OtherSub?

[Sacto95827]

No I do not.

[billhuard]

then we will need the code of othersub and othersub2 to find where this error is coming from.

[Sacto95827]

Haha...I think I figured it out...can you pass variables between modules?

[DKenny]

Yes you can. I still think you should post your code.

[Sacto95827]

Okay...so here's my code:

MODULE 1:

Public Const MATRIX As String = "Tab 1"
Public Const MATRIX2 As String = "Tab 2"
 
Sub MyMacro()
 
Sheets(MATRIX).Select
Call OtherSub
Call OtherSub2
 
End Sub

MODULE 2:

Sub OtherSub()
 
Sheets(MATRIX2).Select
 
End Sub

So basically, when I'm just trying to bring up another sheet in a workbook. The first Constant works fine in Module 1, but when I call the OtherSub() in Module 2, I get the ambigious error. But if I move the OtherSub() from Module 2 to Module 1, it works. But I would much rather have my subs in another module...looks a lot cleaner.

[DKenny]

Is this your complete code for both modules?

[Sacto95827]

No...it's not my complete code. But I don't think it makes a difference because if I comment out all of my other code in Module 2 and just leave that one select statement...I get the error...right when the code switches to that sub...

[DKenny]

Well, I copied this code and don't get an error, so there has to be something in the rest of you code that is causing the issue.

[billhuard]

try this

Sheets([MATRIX]).Select
Sheets([MATRIX2]).Select

give me some news, because me too I Copied & Pasted your code and everything went fine

[Webtest]

Something doesn't make sense here ... your code runs fine on my machine.

How is VB6 involved in this operation?

You state:

Compile error:
Ambigous name detected: MATRIX

but your code says:

Sheets(MATRIX2).Select

Where is the complier finding "MATRIX" (as opposed to "MATRIX2"). You don't actually have a SPACE, as in "Matrix 2", do you?

[Sacto95827]

I think I might of ran into one of my problems. So when I copied and pasted all of my code from one module to the main module to make it work, I erased the old module. So now I just added a module and pasted the subs into the module and my main sub can't reference the other subs. Do I need to do something to have the modules reference each other? I think this might have been my initial problem all this time.

[Sacto95827]

Something doesn't make sense here ... your code runs fine on my machine.

How is VB6 involved in this operation?

You state: but your code says:

Sheets(MATRIX2).Select

Where is the complier finding "MATRIX" (as opposed to "MATRIX2"). You don't actually have a SPACE, as in "Matrix 2", do you?

sorry...should be MATRIX2

[billhuard]

you don't have to do anything to add reference between module,
Only by compiling you're project it will.

Do you have a sheet named MATRIX2????

[Webtest]

It should take you less than 5 minutes to prove the following ... open a clean workbook, Menu Bar: Insert > Worksheet. This adds "Sheet2" to the workbook. Record a dummy macro and then edit it with the following (in Module 1):

Option Explicit
Public Const MATRIX As String = "Sheet2"
Sub Macro1()
    Call junk
End Sub

In the VBA Project View right-click on "Modules" and select Insert > Module. In this new Module2, insert the following code:

Option Explicit
Sub junk()
    Sheets(MATRIX).Select
End Sub

Now when you select Sheet1 and then run Macro1, Sheet2 will be selected. Does this work for you?

[Webtest]

I'll bet you have something loaded in your References that has a keyword "Matrix" in its namespace.

[DKenny]

I'll bet you have something loaded in your References that has a keyword "Matrix" in its namespace.

Nice idae, but he stated that the constant worked in one sub, but not in another. If it was a keyword issue it wouldn't work at all.
5 will get you 10 that its double declared.

[Sacto95827]

I don't think it's double declared...I think it's something else. Check it out. So I initially had two modules. Module 1 had the macro I wanted to run. So it basically had:

MODULE 1:

Public Const MATRIX As String = "Tab 1"
Public Const MATRIX2 As String = "Tab 2"
 
Sub MyMacro()
 
Sheets(MATRIX).Select
Call OtherSub
Call OtherSub2
 
End Sub

Module 2 had all of my other subs...
MODULE 2:

Sub OtherSub()
 
Sheets(MATRIX2).Select
 
End Sub

Running from top-to-bottom, when Module 1 would call Sub OtherSub() and the pointer got to Module 2 Sub OtherSub(), I would right away get the error:

Compile error:
Ambigous name detected: MATRIX2

So I basically copied all of my code from Module 2 to Module 1 making it look like this:

MODULE 1:

Public Const MATRIX As String = "Tab 1"
Public Const MATRIX2 As String = "Tab 2"
 
Sub MyMacro()
 
Sheets(MATRIX).Select
Call OtherSub
Call OtherSub2
 
End Sub
 
Sub OtherSub()
 
Sheets(MATRIX2).Select
 
End Sub

I then deleted Module 2 and this worked...but it's not really pretty or the best coding practice.

So billhuard suggested to try:

Sheets([MATRIX2])Select

I thought all I would have to do is insert another module, cut the code from Module 1 to the new Module and viola. But now I am getting:

Compile error:
Ambigous name detected: OtherSub

So for some reason my modules aren't associating with each other. And because of this, I think that this might of been my initial problem. Is this a stupid error that can be solved easily...I bet it is...

[Comintern]

So for some reason my modules aren't associating with each other. And because of this, I think that this might of been my initial problem. Is this a stupid error that can be solved easily...I bet it is...

Do you have Option Explicit on? If not, turn it on. Then, do a global search for MATRIX to make sure it isn't double declared. Also, make sure you don't have the constants declared in more than one place. If they are, it will work fine from any module it is declared in, as it defaults to it's own namespace. You can duplicate this behaviour by doing the following: Create 3 modules. In the first one put this code:

Private Sub Verify()
 
    Debug.Print TEST_IT
 
End Sub

In the second one put this code:

Public Const TEST_IT As String = "Module2"
 
Private Sub Verify()
 
    Debug.Print TEST_IT
 
End Sub

And in the third, put this code:

Public Const TEST_IT As String = "Module3"
 
Private Sub Verify()
 
    Debug.Print TEST_IT
 
End Sub

The Verify sub will run fine in any module with the Const declaration in it (modules 2 and 3), but will give your error in module 1. After you have done that, if you are still having problems, you can explicitly reference namespaces of modules by using syntax like this:

Sub OtherSub()
 
Sheets(Module1.MATRIX2).Select
 
End Sub

[EDIT] BTW, same thing applies to your Subs. I would also get into the habit of explicitly declaring them Public or Private. By default, they are Public.

[Sacto95827]

I tried everything and I'm still stuck. Here's my code pretty much in it's entirety:

Module 1:

Option Explicit
 
Public Const MATRIX As String = "SHEET 1"
Public Const PIVOTS As String = "PIVOTS"
Public Const MATRIX2 As String = "SHEET 2"
 
Sub RefreshLOR1()
    Sheets("Raw").Select
    ActiveWorkbook.RefreshAll
    Sheets(MATRIX).Select
    Range("A1").Select
    Call ClearScreen
    Call GetNames
    Range("B1").Select
    
End Sub

Module 2:

Option Explicit
 
Sub ClearScreen()
    Range("C6:C30").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("P6:Q30").Select
    Selection.ClearContents
    Range("P34:P85").Select
    Selection.Cut
    Range("Q34").Select
    ActiveSheet.Paste
End Sub
 
Sub GetNames()
    Range("P34").Select
    ChDir "Y:\Directory\Pro\Indicators"
    Workbooks.Open Filename:= _
        "Y:\Directory\Pro\Latest Matrix.xls"
    Sheets(MATRIX2).Select
    Call GetSection
Do While IsEmpty(ActiveCell.Offset(1, 1)) = False
    Sheets(MATRIX2).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    Windows("Schedule_Audit_Template.xls").Activate
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Select
    Windows("Latest Matrix.xls").Activate
Loop
    Windows("Schedule_Audit_Template.xls").Activate
    Range("P36:P85").Select
    Selection.Sort Key1:=Range("P36"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub
 
Sub GetSection()
    Dim str As String
    Dim fcell As Range
    Dim r As Range
 
    Set r = Worksheets(MATRIX2).Range("A1:A300")
    Range("A1").Select
    Do While fcell Is Nothing
    str = InputBox("Pick Section")
    Set fcell = r.Find(what:="Section-Done-" + str, _
    After:=r.Cells(r.Cells.Count), _
    LookIn:=xlValues, LookAt:=xlPart, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False)
    If fcell Is Nothing Then
        MsgBox "Section Not Found"
    End If
    Loop
    If Len(str) = 0 Then
        Range("U1").Select
    ElseIf StrPtr(str) = 0 Then
        Range("U1").Select
    Else
        fcell.Activate
    End If
End Sub

Like I said, when I'm in Module 1, the first constant MATRIX works fine. Then it calls the ClearScreen sub and it works fine. Then when I go into the GetNames sub, I get the error:

Compile error:

Ambigious name detected: MATRIX2

It's like the constant doesn't get passed between the modules because if I just copy and paste all of my code from Module 2 to Module 1, it works. Man I'm stuck.

[Comintern]

Your problem has nothing to do with the constants. It is here:

Sub GetNames()
    Range("P34").Select
    ChDir "Y:\Directory\Pro\Indicators"
    Workbooks.Open Filename:= _
        "Y:\Directory\Pro\Latest Matrix.xls"
    Sheets(MATRIX2).Select        'Error on this line.

You have more than one sheet open with the same name, so the worksheet is the ambiguous name, not the constant. You need to find some way to directly reference it's object without an implicit activate. I'd do it like this:

Sub GetNames()
    
    Dim oBook As Workbook
    
    Range("P34").Select
    ChDir "Y:\Directory\Pro\Indicators"
    Set oBook = Application.Workbooks.Open("Y:\Directory\Pro\Latest Matrix.xls")
    oBook.Sheets(MATRIX2).Select
    Call GetSection
    '....

It's generally better to always keep specific references to the objects you are working with in VBA (it also shows why you should post all your code). This is a perfect example.

[Sacto95827]

You have more than one sheet open with the same name, so the worksheet is the ambiguous name, not the constant.

What are you refering to as the same name? The sheets all have different sheet names:
SHEET 1
SHEET 2
PIVOTS

You need to find some way to directly reference it's object without an implicit activate. I'd do it like this:

Sub GetNames()
    
    Dim oBook As Workbook
    
    Range("P34").Select
    ChDir "Y:\Directory\Pro\Indicators"
    Set oBook = Application.Workbooks.Open("Y:\Directory\Pro\Latest Matrix.xls")
    oBook.Sheets(MATRIX2).Select
    Call GetSection
    '....

It's generally better to always keep specific references to the objects you are working with in VBA (it also shows why you should post all your code). This is a perfect example.

So I think I see what you are saying...declare an object that is specific to that sub?

But then I did try your suggestion, but to no avail...

[DKenny]

Sacto
Comintern is saying that you may have multiple workbooks open and that there may be a sheet called "SHEET 2" in more than one of those workbooks, hence the Ambigious name detected - VBA doesn't know which "sheet 2" you are referring to.

Try changing your GetSection procedure, such that it require the name of the workbook to be passed to it.
Now when setting the range 'r', you can qualify the workbook as well as the sheet name.

Sub GetSection(ByVal BookName As String)
    Dim str As String
    Dim fcell As Range
    Dim r As Range
 
    Set r = Workbooks(BookName).Worksheets(MATRIX2).Range("A1:A300")
    Range("A1").Select
    Do While fcell Is Nothing
    str = InputBox("Pick Section")
    Set fcell = r.Find(what:="Section-Done-" + str, _
    After:=r.Cells(r.Cells.Count), _
    LookIn:=xlValues, LookAt:=xlPart, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False)
    If fcell Is Nothing Then
        MsgBox "Section Not Found"
    End If
    Loop
    If Len(str) = 0 Then
        Range("U1").Select
    ElseIf StrPtr(str) = 0 Then
        Range("U1").Select
    Else
        fcell.Activate
    End If
End Sub

Now when calling it from GetNames, you can pass it the name of the workbook in question.

Sub GetNames()
    
    Dim oBook As Workbook
    
    Range("P34").Select
    ChDir "Y:\Directory\Pro\Indicators"
    Set oBook = Application.Workbooks.Open("Y:\Directory\Pro\Latest Matrix.xls")
    oBook.Sheets(MATRIX2).Select
    GetSection oBook.Name
    '......

[Sacto95827]

Sacto
Comintern is saying that you may have multiple workbooks open and that there may be a sheet called "SHEET 2" in more than one of those workbooks, hence the Ambigious name detected - VBA doesn't know which "sheet 2" you are referring to.

I don't think that can be the case because I made sure that both the workbooks that I have open have different sheet/tab names. I just used SHEET 2 as a generic name since I'm at work

But I am going to try your suggestion by passing the workbook, also.

[Webtest]

I know a lot of people HATE globals, but if I am working back and forth with a lot of manipulations on the same 2 or 3 sheets I do the following (for 2 sheets):

Option Explicit

'Define the Global Source Sheet
Public G_srcSheet As Worksheet
'Define the Global Destination Sheet
Public G_dstSheet As Worksheet

Sub Macro1()

'Sheet Handle Initialization
Set G_srcSheet = Workbooks("mySourceWorkbookName").Sheets("mySourceSheetName")
Set G_dstSheet = Workbooks("myDestWorkbookName").Sheets("myDestSheetName")

'Go on your merry way using just G_srcSheet and G_dstSheet anywhere in your code!!!
'They will never get confused.

End Sub

You can also initialize the Sheet Handle right after you open a workbook if that doesn't happen until later in the code. It is handy to know that opening a workbook immediately forces that workbook to be the ActiveWorkbook.