Project references in VBA

Hi,

In Excel XP the following code generates the error:
Compile Error - Expected Array

Enum ColourID
Rose = 38
Lavender = 39
Tan = 40
LightBlue = 41
End Enum

Sub Test()
Dim dblRes As Double
dblRes = Tan(1#)
MsgBox dblRes
End Sub


If I remove the Tan colour ID from the enum it works as expected.
Presumably Excel is seeing Tan in sub Test and matching it to
ColourID.Tan and not the Tan function.

So how do I get Excel to use the Tan function in this case?

[Interestingly, you cannot put a lower case 't' on tan in the enum -
VBA changes it to 'Tan']

Presumably because Tan is a reserved word, call it Tanish or something.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

<Andrew.Kirkham@gmail.com> wrote in message
news:1150817701.996466.253920@u72g2000cwu.googlegroups.com...
> Hi,
>
> In Excel XP the following code generates the error:
> Compile Error - Expected Array
>
> Enum ColourID
> Rose = 38
> Lavender = 39
> Tan = 40
> LightBlue = 41
> End Enum
>
> Sub Test()
> Dim dblRes As Double
> dblRes = Tan(1#)
> MsgBox dblRes
> End Sub
>
>
> If I remove the Tan colour ID from the enum it works as expected.
> Presumably Excel is seeing Tan in sub Test and matching it to
> ColourID.Tan and not the Tan function.
>
> So how do I get Excel to use the Tan function in this case?
>
> [Interestingly, you cannot put a lower case 't' on tan in the enum -
> VBA changes it to 'Tan']
>

Change
dblRes = Tan(1#)
to
dblRes = Tan

But as Bob mentioned, Tan is a reserved word in VBA (it means the
trigonometric function Tangent), so you should change the name.


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



<Andrew.Kirkham@gmail.com> wrote in message
news:1150817701.996466.253920@u72g2000cwu.googlegroups.com...
> Hi,
>
> In Excel XP the following code generates the error:
> Compile Error - Expected Array
>
> Enum ColourID
> Rose = 38
> Lavender = 39
> Tan = 40
> LightBlue = 41
> End Enum
>
> Sub Test()
> Dim dblRes As Double
> dblRes = Tan(1#)
> MsgBox dblRes
> End Sub
>
>
> If I remove the Tan colour ID from the enum it works as
> expected.
> Presumably Excel is seeing Tan in sub Test and matching it to
> ColourID.Tan and not the Tan function.
>
> So how do I get Excel to use the Tan function in this case?
>
> [Interestingly, you cannot put a lower case 't' on tan in the
> enum -
> VBA changes it to 'Tan']
>

Well obviously I can change the name in the enum from Tan to something
else, but the point was that I shouldn't have to do this. Technically I
haven't used the reserved word Tan, I have used ColourID.Tan. The VBA
compiler incorrectly interprets this.

Perhaps I'm just being a C programmer about it and expecting things to
work as they should!

Andrew.Kirkham@gmail.com wrote:
> Well obviously I can change the name in the enum from Tan to something
> else, but the point was that I shouldn't have to do this. Technically I
> haven't used the reserved word Tan, I have used ColourID.Tan. The VBA
> compiler incorrectly interprets this.
>
> Perhaps I'm just being a C programmer about it and expecting things to
> work as they should!

....more interestingly if you use a vba reserved enumerator name in a
new enumerator then it overwrites the existing one without any warning.

e.g.

Enum TRY
vbRed = 1
End Enum

Public Sub TEST()
MsgBox TRY.vbRed
'msgbox displays 1, as expected
MsgBox vbRed
'msgbox displays 1, not 255
End Sub

There are of course ways around this as Andrew says, but it is worrying
that there is no warning that this is happening. If you write a new
function with the same name as a user defined enumerator it complains,
if you write a function with the same name as a "hard-coded" one (e.g.
function vbGreen as integer) it doesn't. Very odd !