Enum type variables

[KC]

Good morning

For exercise,
I have weekdays in row 1, Sun, Mon, Tue...Sat and repeat
To highlight every Sunday, I use this code below.

I would like to seek advice if I can read contents in cells as value in the
enum variable, ie can I get rid of the first set of select case please?

Enum wkdays
sun = 6
End Enum

Sub test()
Dim eidx As wkdays

For k = 1 To Cells(1, 1).End(xlToRight).Column

Select Case Cells(1, k)
Case "Sun"
eidx = sun
Case Else
eidx = 0
End Select

Select Case eidx
Case sun
Columns(k).Interior.ColorIndex = sun
End Select

Next k

End Sub

[Rick Hansen]

Good Morning Back at ya KC,

Try this bit of Code, It should be much more simple to use, and
Understand..

enjoy, Rick

Sub test()
Dim k As Integer
Dim sun As Integer

sun = 6
For k = 1 To Cells(1, 1).End(xlToRight).Column
If Cells(1, k).value = "Sun" Then
Columns(k).Interior.ColorIndex = sun
End If
Next k
End Sub



"KC" <KC@discussions.microsoft.com> wrote in message
news:D5E8720D-0797-492E-8491-BDA5A129A5F4@microsoft.com...
> Good morning
>
> For exercise,
> I have weekdays in row 1, Sun, Mon, Tue...Sat and repeat
> To highlight every Sunday, I use this code below.
>
> I would like to seek advice if I can read contents in cells as value in
the
> enum variable, ie can I get rid of the first set of select case please?
>
> Enum wkdays
> sun = 6
> End Enum
>
> Sub test()
> Dim eidx As wkdays
>
> For k = 1 To Cells(1, 1).End(xlToRight).Column
>
> Select Case Cells(1, k)
> Case "Sun"
> eidx = sun
> Case Else
> eidx = 0
> End Select
>
> Select Case eidx
> Case sun
> Columns(k).Interior.ColorIndex = sun
> End Select
>
> Next k
>
> End Sub
>

[KC]

Thank you Rick,

Nope, I understand that.
I am trying to learn how to use enum variables please.


"Rick Hansen" wrote:

> Good Morning Back at ya KC,
>
> Try this bit of Code, It should be much more simple to use, and
> Understand..
>
> enjoy, Rick
>
> Sub test()
> Dim k As Integer
> Dim sun As Integer
>
> sun = 6
> For k = 1 To Cells(1, 1).End(xlToRight).Column
> If Cells(1, k).value = "Sun" Then
> Columns(k).Interior.ColorIndex = sun
> End If
> Next k
> End Sub
>
>
>
> "KC" <KC@discussions.microsoft.com> wrote in message
> news:D5E8720D-0797-492E-8491-BDA5A129A5F4@microsoft.com...
> > Good morning
> >
> > For exercise,
> > I have weekdays in row 1, Sun, Mon, Tue...Sat and repeat
> > To highlight every Sunday, I use this code below.
> >
> > I would like to seek advice if I can read contents in cells as value in
> the
> > enum variable, ie can I get rid of the first set of select case please?
> >
> > Enum wkdays
> > sun = 6
> > End Enum
> >
> > Sub test()
> > Dim eidx As wkdays
> >
> > For k = 1 To Cells(1, 1).End(xlToRight).Column
> >
> > Select Case Cells(1, k)
> > Case "Sun"
> > eidx = sun
> > Case Else
> > eidx = 0
> > End Select
> >
> > Select Case eidx
> > Case sun
> > Columns(k).Interior.ColorIndex = sun
> > End Select
> >
> > Next k
> >
> > End Sub
> >
>
>
>

[Chip Pearson]

> I am trying to learn how to use enum variables please.

An Enum is a Long type variable that has specific named values.
For example,

Public Enum Fruit
Apple
Orange
Pear
End Enum

As written above, the compiler assigns the value 0 to the first
item in the list, and increments the value by 1 for each item in
the list. The above code is functionally equivalent to

Public Enum Fruit
Apple = 0
Orange = 1
Pear = 2
End Enum



You can also assign specific values to the elements of the enum.
E.g.,

Public Enum Fruit
Apple = 5
Orange = 10
Pear = 15
End Enum

Once you've defined the enum, you can declare a variable of that
type. E.g,

Dim F As Fruit

The primary advantage of using an enum is that the editor's
Intellisense will pop up the possible named values of the enum.
It is critical to remember that ANY long value can be assigned to
an enum, even if that value is not listed in the elements of the
enum. E.g., the following code is perfectly legal:

Dim F As Fruit
F = 123456



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"KC" <KC@discussions.microsoft.com> wrote in message
news:8F8D8C6C-09E3-480B-B574-33FC05048D94@microsoft.com...
> Thank you Rick,
>
> Nope, I understand that.
> I am trying to learn how to use enum variables please.
>
>
> "Rick Hansen" wrote:
>
>> Good Morning Back at ya KC,
>>
>> Try this bit of Code, It should be much more simple to use,
>> and
>> Understand..
>>
>> enjoy, Rick
>>
>> Sub test()
>> Dim k As Integer
>> Dim sun As Integer
>>
>> sun = 6
>> For k = 1 To Cells(1, 1).End(xlToRight).Column
>> If Cells(1, k).value = "Sun" Then
>> Columns(k).Interior.ColorIndex = sun
>> End If
>> Next k
>> End Sub
>>
>>
>>
>> "KC" <KC@discussions.microsoft.com> wrote in message
>> news:D5E8720D-0797-492E-8491-BDA5A129A5F4@microsoft.com...
>> > Good morning
>> >
>> > For exercise,
>> > I have weekdays in row 1, Sun, Mon, Tue...Sat and repeat
>> > To highlight every Sunday, I use this code below.
>> >
>> > I would like to seek advice if I can read contents in cells
>> > as value in
>> the
>> > enum variable, ie can I get rid of the first set of select
>> > case please?
>> >
>> > Enum wkdays
>> > sun = 6
>> > End Enum
>> >
>> > Sub test()
>> > Dim eidx As wkdays
>> >
>> > For k = 1 To Cells(1, 1).End(xlToRight).Column
>> >
>> > Select Case Cells(1, k)
>> > Case "Sun"
>> > eidx = sun
>> > Case Else
>> > eidx = 0
>> > End Select
>> >
>> > Select Case eidx
>> > Case sun
>> > Columns(k).Interior.ColorIndex = sun
>> > End Select
>> >
>> > Next k
>> >
>> > End Sub
>> >
>>
>>
>>

[KC]

Good morning Mr Pearson

So did Carlos J. Quintero of MZ-Tools advise me.
Perhaps I should rephrase my query as follow:

if we input cells(1,1) as "apple"

dim t
t=cells(1,1)
debug.print t

we get "apple"

But if we
Enum fruit
apple
End Enum

Dim t As fruit
t = Cells(1, 1)
Debug.Print t

We get type mismatch error.
I only know to use select case statement to read "apple" in and set t (using
intellisense) to enum apple again.

Is it possible to "read" in an enum variable directly from cell contents
please?

Regards

"Chip Pearson" wrote:

> > I am trying to learn how to use enum variables please.
>
> An Enum is a Long type variable that has specific named values.
> For example,
>
> Public Enum Fruit
> Apple
> Orange
> Pear
> End Enum
>
> As written above, the compiler assigns the value 0 to the first
> item in the list, and increments the value by 1 for each item in
> the list. The above code is functionally equivalent to
>
> Public Enum Fruit
> Apple = 0
> Orange = 1
> Pear = 2
> End Enum
>
>
>
> You can also assign specific values to the elements of the enum.
> E.g.,
>
> Public Enum Fruit
> Apple = 5
> Orange = 10
> Pear = 15
> End Enum
>
> Once you've defined the enum, you can declare a variable of that
> type. E.g,
>
> Dim F As Fruit
>
> The primary advantage of using an enum is that the editor's
> Intellisense will pop up the possible named values of the enum.
> It is critical to remember that ANY long value can be assigned to
> an enum, even if that value is not listed in the elements of the
> enum. E.g., the following code is perfectly legal:
>
> Dim F As Fruit
> F = 123456
>
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "KC" <KC@discussions.microsoft.com> wrote in message
> news:8F8D8C6C-09E3-480B-B574-33FC05048D94@microsoft.com...
> > Thank you Rick,
> >
> > Nope, I understand that.
> > I am trying to learn how to use enum variables please.
> >
> >
> > "Rick Hansen" wrote:
> >
> >> Good Morning Back at ya KC,
> >>
> >> Try this bit of Code, It should be much more simple to use,
> >> and
> >> Understand..
> >>
> >> enjoy, Rick
> >>
> >> Sub test()
> >> Dim k As Integer
> >> Dim sun As Integer
> >>
> >> sun = 6
> >> For k = 1 To Cells(1, 1).End(xlToRight).Column
> >> If Cells(1, k).value = "Sun" Then
> >> Columns(k).Interior.ColorIndex = sun
> >> End If
> >> Next k
> >> End Sub
> >>
> >>
> >>
> >> "KC" <KC@discussions.microsoft.com> wrote in message
> >> news:D5E8720D-0797-492E-8491-BDA5A129A5F4@microsoft.com...
> >> > Good morning
> >> >
> >> > For exercise,
> >> > I have weekdays in row 1, Sun, Mon, Tue...Sat and repeat
> >> > To highlight every Sunday, I use this code below.
> >> >
> >> > I would like to seek advice if I can read contents in cells
> >> > as value in
> >> the
> >> > enum variable, ie can I get rid of the first set of select
> >> > case please?
> >> >
> >> > Enum wkdays
> >> > sun = 6
> >> > End Enum
> >> >
> >> > Sub test()
> >> > Dim eidx As wkdays
> >> >
> >> > For k = 1 To Cells(1, 1).End(xlToRight).Column
> >> >
> >> > Select Case Cells(1, k)
> >> > Case "Sun"
> >> > eidx = sun
> >> > Case Else
> >> > eidx = 0
> >> > End Select
> >> >
> >> > Select Case eidx
> >> > Case sun
> >> > Columns(k).Interior.ColorIndex = sun
> >> > End Select
> >> >
> >> > Next k
> >> >
> >> > End Sub
> >> >
> >>
> >>
> >>
>
>
>

[Chip Pearson]

You're getting the Type Mismatch error because all Enum variables
are Long integers, and you're trying to assign the string value
'apple' to a numeric data type. You can only assign numeric
values to an Enum type variable.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"KC" <KC@discussions.microsoft.com> wrote in message
news:A6765138-D5A3-42D2-A301-2E45B1B2ECE8@microsoft.com...
> Good morning Mr Pearson
>
> So did Carlos J. Quintero of MZ-Tools advise me.
> Perhaps I should rephrase my query as follow:
>
> if we input cells(1,1) as "apple"
>
> dim t
> t=cells(1,1)
> debug.print t
>
> we get "apple"
>
> But if we
> Enum fruit
> apple
> End Enum
>
> Dim t As fruit
> t = Cells(1, 1)
> Debug.Print t
>
> We get type mismatch error.
> I only know to use select case statement to read "apple" in and
> set t (using
> intellisense) to enum apple again.
>
> Is it possible to "read" in an enum variable directly from cell
> contents
> please?
>
> Regards
>
> "Chip Pearson" wrote:
>
>> > I am trying to learn how to use enum variables please.
>>
>> An Enum is a Long type variable that has specific named
>> values.
>> For example,
>>
>> Public Enum Fruit
>> Apple
>> Orange
>> Pear
>> End Enum
>>
>> As written above, the compiler assigns the value 0 to the
>> first
>> item in the list, and increments the value by 1 for each item
>> in
>> the list. The above code is functionally equivalent to
>>
>> Public Enum Fruit
>> Apple = 0
>> Orange = 1
>> Pear = 2
>> End Enum
>>
>>
>>
>> You can also assign specific values to the elements of the
>> enum.
>> E.g.,
>>
>> Public Enum Fruit
>> Apple = 5
>> Orange = 10
>> Pear = 15
>> End Enum
>>
>> Once you've defined the enum, you can declare a variable of
>> that
>> type. E.g,
>>
>> Dim F As Fruit
>>
>> The primary advantage of using an enum is that the editor's
>> Intellisense will pop up the possible named values of the
>> enum.
>> It is critical to remember that ANY long value can be assigned
>> to
>> an enum, even if that value is not listed in the elements of
>> the
>> enum. E.g., the following code is perfectly legal:
>>
>> Dim F As Fruit
>> F = 123456
>>
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>> "KC" <KC@discussions.microsoft.com> wrote in message
>> news:8F8D8C6C-09E3-480B-B574-33FC05048D94@microsoft.com...
>> > Thank you Rick,
>> >
>> > Nope, I understand that.
>> > I am trying to learn how to use enum variables please.
>> >
>> >
>> > "Rick Hansen" wrote:
>> >
>> >> Good Morning Back at ya KC,
>> >>
>> >> Try this bit of Code, It should be much more simple to
>> >> use,
>> >> and
>> >> Understand..
>> >>
>> >> enjoy, Rick
>> >>
>> >> Sub test()
>> >> Dim k As Integer
>> >> Dim sun As Integer
>> >>
>> >> sun = 6
>> >> For k = 1 To Cells(1, 1).End(xlToRight).Column
>> >> If Cells(1, k).value = "Sun" Then
>> >> Columns(k).Interior.ColorIndex = sun
>> >> End If
>> >> Next k
>> >> End Sub
>> >>
>> >>
>> >>
>> >> "KC" <KC@discussions.microsoft.com> wrote in message
>> >> news:D5E8720D-0797-492E-8491-BDA5A129A5F4@microsoft.com...
>> >> > Good morning
>> >> >
>> >> > For exercise,
>> >> > I have weekdays in row 1, Sun, Mon, Tue...Sat and repeat
>> >> > To highlight every Sunday, I use this code below.
>> >> >
>> >> > I would like to seek advice if I can read contents in
>> >> > cells
>> >> > as value in
>> >> the
>> >> > enum variable, ie can I get rid of the first set of
>> >> > select
>> >> > case please?
>> >> >
>> >> > Enum wkdays
>> >> > sun = 6
>> >> > End Enum
>> >> >
>> >> > Sub test()
>> >> > Dim eidx As wkdays
>> >> >
>> >> > For k = 1 To Cells(1, 1).End(xlToRight).Column
>> >> >
>> >> > Select Case Cells(1, k)
>> >> > Case "Sun"
>> >> > eidx = sun
>> >> > Case Else
>> >> > eidx = 0
>> >> > End Select
>> >> >
>> >> > Select Case eidx
>> >> > Case sun
>> >> > Columns(k).Interior.ColorIndex = sun
>> >> > End Select
>> >> >
>> >> > Next k
>> >> >
>> >> > End Sub
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

[KC]

Thank you for this quick response.
Clearly I missed about the Long integers.
No further question.
Thank you again.

Regards

"Chip Pearson" wrote:

> You're getting the Type Mismatch error because all Enum variables
> are Long integers, and you're trying to assign the string value
> 'apple' to a numeric data type. You can only assign numeric
> values to an Enum type variable.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
>
> "KC" <KC@discussions.microsoft.com> wrote in message
> news:A6765138-D5A3-42D2-A301-2E45B1B2ECE8@microsoft.com...
> > Good morning Mr Pearson
> >
> > So did Carlos J. Quintero of MZ-Tools advise me.
> > Perhaps I should rephrase my query as follow:
> >
> > if we input cells(1,1) as "apple"
> >
> > dim t
> > t=cells(1,1)
> > debug.print t
> >
> > we get "apple"
> >
> > But if we
> > Enum fruit
> > apple
> > End Enum
> >
> > Dim t As fruit
> > t = Cells(1, 1)
> > Debug.Print t
> >
> > We get type mismatch error.
> > I only know to use select case statement to read "apple" in and
> > set t (using
> > intellisense) to enum apple again.
> >
> > Is it possible to "read" in an enum variable directly from cell
> > contents
> > please?
> >
> > Regards
> >
> > "Chip Pearson" wrote:
> >
> >> > I am trying to learn how to use enum variables please.
> >>
> >> An Enum is a Long type variable that has specific named
> >> values.
> >> For example,
> >>
> >> Public Enum Fruit
> >> Apple
> >> Orange
> >> Pear
> >> End Enum
> >>
> >> As written above, the compiler assigns the value 0 to the
> >> first
> >> item in the list, and increments the value by 1 for each item
> >> in
> >> the list. The above code is functionally equivalent to
> >>
> >> Public Enum Fruit
> >> Apple = 0
> >> Orange = 1
> >> Pear = 2
> >> End Enum
> >>
> >>
> >>
> >> You can also assign specific values to the elements of the
> >> enum.
> >> E.g.,
> >>
> >> Public Enum Fruit
> >> Apple = 5
> >> Orange = 10
> >> Pear = 15
> >> End Enum
> >>
> >> Once you've defined the enum, you can declare a variable of
> >> that
> >> type. E.g,
> >>
> >> Dim F As Fruit
> >>
> >> The primary advantage of using an enum is that the editor's
> >> Intellisense will pop up the possible named values of the
> >> enum.
> >> It is critical to remember that ANY long value can be assigned
> >> to
> >> an enum, even if that value is not listed in the elements of
> >> the
> >> enum. E.g., the following code is perfectly legal:
> >>
> >> Dim F As Fruit
> >> F = 123456
> >>
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >>
> >> "KC" <KC@discussions.microsoft.com> wrote in message
> >> news:8F8D8C6C-09E3-480B-B574-33FC05048D94@microsoft.com...
> >> > Thank you Rick,
> >> >
> >> > Nope, I understand that.
> >> > I am trying to learn how to use enum variables please.
> >> >
> >> >
> >> > "Rick Hansen" wrote:
> >> >
> >> >> Good Morning Back at ya KC,
> >> >>
> >> >> Try this bit of Code, It should be much more simple to
> >> >> use,
> >> >> and
> >> >> Understand..
> >> >>
> >> >> enjoy, Rick
> >> >>
> >> >> Sub test()
> >> >> Dim k As Integer
> >> >> Dim sun As Integer
> >> >>
> >> >> sun = 6
> >> >> For k = 1 To Cells(1, 1).End(xlToRight).Column
> >> >> If Cells(1, k).value = "Sun" Then
> >> >> Columns(k).Interior.ColorIndex = sun
> >> >> End If
> >> >> Next k
> >> >> End Sub
> >> >>
> >> >>
> >> >>
> >> >> "KC" <KC@discussions.microsoft.com> wrote in message
> >> >> news:D5E8720D-0797-492E-8491-BDA5A129A5F4@microsoft.com...
> >> >> > Good morning
> >> >> >
> >> >> > For exercise,
> >> >> > I have weekdays in row 1, Sun, Mon, Tue...Sat and repeat
> >> >> > To highlight every Sunday, I use this code below.
> >> >> >
> >> >> > I would like to seek advice if I can read contents in
> >> >> > cells
> >> >> > as value in
> >> >> the
> >> >> > enum variable, ie can I get rid of the first set of
> >> >> > select
> >> >> > case please?
> >> >> >
> >> >> > Enum wkdays
> >> >> > sun = 6
> >> >> > End Enum
> >> >> >
> >> >> > Sub test()
> >> >> > Dim eidx As wkdays
> >> >> >
> >> >> > For k = 1 To Cells(1, 1).End(xlToRight).Column
> >> >> >
> >> >> > Select Case Cells(1, k)
> >> >> > Case "Sun"
> >> >> > eidx = sun
> >> >> > Case Else
> >> >> > eidx = 0
> >> >> > End Select
> >> >> >
> >> >> > Select Case eidx
> >> >> > Case sun
> >> >> > Columns(k).Interior.ColorIndex = sun
> >> >> > End Select
> >> >> >
> >> >> > Next k
> >> >> >
> >> >> > End Sub
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>