424,287 Members | 2,113 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,287 IT Pros & Developers. It's quick & easy.

Compile Error: Expected Array

P: 3
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!

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer) 'Added
  2.     'unlock fields for STL
  3.     If TempVars![tvIsSTL] And Status <> "Closed" And Status <> "Historical" Then
  4.         Dim c As control
  5.         For Each c In Me.Controls
  6.             If Left(c.Name, 3) = "STL" Then
  7.                 Select Case c.ControlType
  8.                     Case Is = acTextBox
  9.                         c.Locked = False
  10.                     Case Is = acListBox
  11.                         c.Locked = False
  12.                     Case Is = acComboBox
  13.                         c.Locked = False
  14.                     Case Is = acCheckBox
  15.                         c.Locked = False
  16.                     Case Is = acCommandButton
  17.                         c.Enabled = True
  18.                 End Select
  19.                 If Left(c.Name, 4) = "STLO" Then c.Visible = True
  20.                 If InStr(1, UCase(c.Name), "CLOSEPROJECT") > 0 Then c.Visible = True
  21.             End If
  22.         Next
  23.     End If
Feb 14 '11 #1
Share this Question
Share on Google+
4 Replies

Expert Mod 2.5K+
P: 2,545
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

Feb 14 '11 #2

P: 3
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.
Feb 15 '11 #3

Expert Mod 2.5K+
P: 2,545
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

Expand|Select|Wrap|Line Numbers
  1. Public Function fLeft(SomeArguments) as SomeType
  2.   .
  3.   .
  4.   fLeft = SomeValue
  5. End Function
Something to consider for the future?

Feb 15 '11 #4

P: 3
I agree, that's not a bad idea.
Feb 15 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.