Compile Error: Expected Array

[Lindsay Bradley]

I have code built into a form in Access and everytime I try to open the form it gives me a Compile error: Expected array message and then highlights the word Left. I did not create this program and know very little about VBA but have been given the responsibility to take over this program. Please help I'm desperate!

Private Sub Form_Open(Cancel As Integer) 'Added
    'unlock fields for STL
    If TempVars![tvIsSTL] And Status <> "Closed" And Status <> "Historical" Then
        Dim c As control
        For Each c In Me.Controls
            If Left(c.Name, 3) = "STL" Then
                Select Case c.ControlType
                    Case Is = acTextBox
                        c.Locked = False
                    Case Is = acListBox
                        c.Locked = False
                    Case Is = acComboBox
                        c.Locked = False
                    Case Is = acCheckBox
                        c.Locked = False
                    Case Is = acCommandButton
                        c.Enabled = True
                End Select
                If Left(c.Name, 4) = "STLO" Then c.Visible = True
                If InStr(1, UCase(c.Name), "CLOSEPROJECT") > 0 Then c.Visible = True
            End If
        Next
    End If

[Stewart Ross]

There is nothing wrong with the use of Left in the code segment above. Left returns the leftmost-N characters from a string, where N is the second argument in the call in each case. Left definitely does not take an array argument!

This type of error - where common built-in functions such as Left stop working - occurs either when there are errors in the database, or, more commonly, when upgrades leave problems with access to code libraries. First step would be to do a compact and repair on the database. If that fails, you need to check the library references. This article on Allen Browne's web site gives further details on how to do this: http://allenbrowne.com/ser-38.html

-Stewart

[Lindsay Bradley]

Thank you for your input. I found the solution not long after I posted this. The word Left had been defined elsewhere in the code so I added VBA.Left to this code and the error went away.

[Stewart Ross]

Ahh. Hadn't thought of the possibility of redefining a standard keyword in VBA to mean something else...

One of the useful naming conventions which can be adopted is to prefix user-written functions with the letter f (for Function) to make it clear that the function is user-defined and not built-in. In this case it would have the benefit of not being named the same as a standard function. The function header would be something like

Public Function fLeft(SomeArguments) as SomeType
  .
  .
  fLeft = SomeValue
End Function

Something to consider for the future?

-Stewart

[Lindsay Bradley]

I agree, that's not a bad idea.