Why isn't Enum returning correct value?

[CW4AVRET]

Hello,

I am using MS Access with SharePoint Lists and some Access Tables to generate reports in pre-formatted MS Excel Worksheets. All is working pretty good but I just ran into something weird to me and cannot figure out why VBA is doing this.

My main module opens several classes. One of the classes receives a worksheet to keep in a historical book. This class can receive any one of several different types of pre-formatted worksheets. After all is said an done the class adds a cover sheet to the consolidated workbook and adds hyperlinks to the other sheets, kinda like a table of content.

In the class I have the following in the general declaration.

Public Enum BookType
    A = 1
    B = 2
End Enum
Private pBookType As BookType

Later in the class I have the following:

Public Property Let LoadBookType(ByVal btNewValue As BookType)
    pBookType = btNewValue
    AddWorkBook
End Property

in the main modual I have the following

    classConsolidated.LoadBookType = A

When assigning the above property it passes in "11"

if I change it to the following

    classConsolidated.LoadBookType = BookType.A

it passes in "1" as I expect and use in a Select Case statement later in the class.

So why does one pass in "11" but the other passes in "1"?

Thank you.

 

[vbaInet]

I don't know how else your class is constructed and what other calls you're making so I can't say for sure. But there are a couple of things I know you're doing wrong:

1. A Let property is meant to assign a value not to call a function
2. A Let property is meant to assign a value to a variable of a native type (such as Integer, String) and not to a variable of an Enum.
3. An Enum is a constant and nothing should be assigned to it, the same way you declare Const.
4. The Enum variable (pBookType) should only be used for equality checking not for assignment
5. If you want to use an Enum in a Select case statement you use the Enum itself (BookType), not a variable of the Enum.

 

[CW4AVRET]

Ooops. Is my slip showing? See what happens when one is self-taught, they continue to learn.

1. A Let property is meant to assign a value not to call a function

I don't understand why you cannot use a let/get call to call another function/sub. How else would you validate the inbound value?

4. The Enum variable (pBookType) should only be used for equality checking not for assignment

I'm not sure what you mean by Equality Checking.

5. If you want to use an Enum in a Select case statement you use the Enum itself (BookType), not a variable of the Enum.

Because each sheet by type being passed into the class has different number of columns I want I am using the Enum in a select case to set the variable

        Select Case pBookType
            Case BookType.A
                lgColumns = 14
            Case BookType.B
                lgColumns = 13
        End Select  ' pBookType

I hope I am not confusing the matter.

Thanks for you input and teaching.

 

[vbaInet]

Don't worry, I'm self taught too

There's nothing stopping you from calling a function from a property but it's bad programming. That's just not what they're meant for.

By equality checking I mean something like this:

pBookType = BookType.A

... or the way you've used it in the Select..Case statement is valid. Or you can pass it as a parameter in a function/sub.

I'm just not sure what you're trying to do in the Property.

Welcome to the forum by the way

 

[CW4AVRET]

One more clarifying point the whole reason for the enum is to limit the possible data being passed into the Class Module.

 

[vbaInet]

One more clarifying point the whole reason for the enum is to limit the possible data being passed into the Class Module.

Pass it as a parameter.

 

[CW4AVRET]

Pass it as a parameter.

Can you give me a little kick start, parameter?

I have used enum's in the past to limit the available arguments for a function/sub call. I thought I could use the enum to do the same thing to limit the available options to a pass into a Class.

 

[vbaInet]

It's the same way as you declare variables in a function's argument:

Public Function MyFunction(pBookType As BookType)

Remember a class can contain functions or subs to do something, so you're passing an argument to a function/sub, not a class.

 

[CW4AVRET]

So I changed it to

Public Sub SetBookType(ByVal btNewValue As BookType)
    pBookType = btNewValue
    AddWorkBook
End Sub

But the value being selected ("A") as an argument is still getting passed in as the value "11" and not "1".

I am so confused.

 

[vbaInet]

Brings me back to all the points I made in my first post. You don't assign a value to an enum, it's a constant. You use it to get a constant value.

Properties are ways to assign and retrieve value from a variable.

 

[CW4AVRET]

Brings me back to all the points I made in my first post. You don't assign a value to an enum, it's a constant. You use it to get a constant value.

Properties are ways to assign and retrieve value from a variable.

I understand ENUM are constants. ENUM at LONG data types and in the absence of directly assigning them a number in the ENUM statement they will receive a LONG value starting from zero. In my case I assigned "A=1" and "B=2". Theoretically if I use the enum as an argument in a sub or function the user will have two choices "A or B", which works. However, if the user selects "A" as the argument it is coming back as "11" and not "1". That is the confusing part.

I understand your point about good programming etiquette and commonly accepted practices but I cannot understand mechanically why it is coming back as 11 no matter if I use it as a Property or and Argument. I have used this method in Arguments for a long long time and have not seen this before.

BTW: MS Access 2010.

 

[vbaInet]

How is it coming back as 11? I've not seen any code where you attempt to get the value.

 

[vbaInet]

Oh I see what you mean. That's perfectly valid. I missed the part that you're passing the same type to it.

Have you setup a Watch?

 

[CW4AVRET]

Class Module ConsolidateWorksheets

Option Compare Database
Option Explicit
Option Base 1

Public Enum BookType
    BOLTS = 1
    ANCHORS = 2
End Enum
Private pBookType As BookType

Private Sub SaveWorkbook()
      With oxlAppForConsolidated
        Set xlwb_Consolidated = .Workbooks.Add
            xlwb_Consolidated.SaveAs _
                FileName:=[COLOR=Red]LookupBookType[/COLOR] & strFileSaveName
    End With  ' oxlAppForConsolidated
End sub


Private Function LookupBookType() As String
    Select Case pBookType       [COLOR=Red] '<<<< It's failing here because pBookType is "11"[/COLOR]
        Case BookType.BOLTS
            LookupBookType = "BOLTS"
        Case BookType.ANCHORS
            LookupBookType = "ANCHORS"
    End Select
End Function

Main Module (NOT CLASS)
Private classConsolidatedBOLTS As New ConsolidateWorksheets

Private Sub AddBoltsConsolidatedWorkBook()
       classConsolidatedBOLTS.LoadBookType = BookType.BOLTS
End sub

Maybe I am just that messed up. :banghead:

 

[vbaInet]

I think you should setup a watch to break when the value changes.
Also, I know you know this, but did you compile? Perhaps there's a variable name conflict.

 

[CW4AVRET]

I have not setup a watch, I will.

I have compiled many many times. This has been going on for days. I have worked around it but it is confounding me why it's doing it.

I have even compacted and repaired several times.

I am usually pretty careful with my variable. Those inside a Class are separate from those outside the class, right? I hope.

 

[vbaInet]

Yes their scope is within the class alone. You should be able to catch it with the watch.

Let us know.

 

[CW4AVRET]

Now this is completely unbelievable, I put a watch on pBookType to stop on all changes. The values being passed in according to the Watch are correct "1" or "2" but when you pass pBookType to the Select Case it is "11". This is so crazy I'm giggling like a little school girl.

I'm obviously doing something wrong.

More to follow.

 

[vbaInet]

Check the type of the value returned using the TypeName() function to see if it's a String or a Long.
Set another watch to break if the value is 11.

 

[CW4AVRET]

TypeName(pBookType)=LONG

I am really miffed.
Note the difference here.
If I call

classConsolidatedBOLTS.LoadBookType = Bolts

pBookType is set to 11

if I call

classConsolidatedBOLTS.LoadBookType = BookType.Bolts

pBookType is set to 1

If I call

classConsolidatedANCHORS.LoadBookType = Anchors

pBookType is set to 12

if I call

classConsolidatedANCHORS.LoadBookType = BookType.ANCHORS

pBookType is set to 2