Expected function or variable compile error
This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.
Answered by:
Question
I’m getting this odd error in my code. its probably obvious but i’ve been staring at it for so long i’ve got code blindness ! i’ve highlighted the error line in red below.
error is : expected function or variable
the msdn definition is here :http://msdn.microsoft.com/library/default.asp?url=/library/en-us/msaa/msaaccrf_0bla.asp
Public Function WinEventFunc(ByVal HookHandle As Long, ByVal LEvent As Long, ByVal HWnd As Long, ByVal idObject As Long, ByVal idChild As Long, ByVal idEventThread As Long, ByVal dwmsEventTime As Long) As Long
Dim ObA As IAccessible
Dim LRet As Long
Dim V As Variant
Dim s As String, s1 As String, sName As String
On Error Resume Next
Dim pxLeft As Long, pyTop As Long, pcxWidth As Long, pcyHeight As Long
pxLeft = 0
pyTop = 0
pcxWidth = 0
pcyHeight = 0
‘Select Case LEvent
‘Case SYS_FOREGROUND, SYS_ALERT, OB_FOCUS, OB_SELECTION, OB_VALUECHANGE
LRet = AccessibleObjectFromEvent(HWnd, idObject, idChild, ObA, V)
If LRet = 0 Then
With F1
sName = ObA.accName(V)
.Text1(0).Text = sName .Text1(1).Text = ObA.accDescription(V)
s1 = ObA.accValue(V)
.Text1(2).Text = s1
.Text1(3).Text = ObA.accRole(V)
.Text1(4).Text = ObA.accState(V)
RECT = ObA.accLocation(pxleft, pytop, pcxwidth, pcyheight, V)
End With
If (ObA.accState(V) And STATE_UNAVAILABLE) Then
s = » disabled»
End If
If LEvent = OB_VALUECHANGE Then
s = s1 & s
Else
s = sName & s
Expected function or variable compile error
This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.
Answered by:
Question
Hi,
Im new to this forum and VBA but have experience in many other languages.
I have a form with the following fields: Week, Day, Work_Order, Operator, Reading_Number, Disc_Size, USL, LSL, Centre, Outer_Position_1, Outer_Position_2, Outer_Position_3, Outer_Position_4, Outer_Average.
I want the SQL code to get the last Reading_Number of a certain Disc_Size when the Disc_Size field is changed.
eg The last Reading_Number of a Disc_Size 2 is 33
The Disc_Size field is changed from 1.6 to 2 so now the value 34 will be automatically entered into the Reading_Number field.
I’ve think I’ve attached a picture of the form and below is the code I’m trying to use.
I’m getting the following error at Private Sub Disc_Size_Change() :
«Compile error: Expected function or variable»
Thanks, I hope I was clear? I have other VBA code that runs under certain conditions which I can post if it might be involved.
Expected:
An expected part of the syntax was not found. The error is usually located to the left of the selected item, but isn’t always obvious. For example, you can invoke a Sub procedure with or without the Call keyword. However, if you use the Call keyword, you must enclose the argument list in parentheses. This error has the following causes and solutions:
Expected: End of Statement. Improper use of parentheses in a procedure invocation:
Use parentheses in a function call that specifies arguments or with a Sub procedure invocation that uses the Call keyword.
Expected: ). Incorrect syntax for a procedure call. For example, a function call can’t stand by itself, and Sub procedure calls sometimes require the Call keyword, depending on how you specify their arguments.
Always use function calls in expressions. If you have multiple arguments enclosed in parentheses in a Sub procedure call, you must use the Call keyword.
Expected: Expression. For example, when pasting code from the Object Browser, you may have forgotten to specify a value for a named argument.
Either add a value for the argument, or delete the argument if it’s optional.
Expected: Variable. For example, you may have used restricted keywords for variable names. In the following example, the Input # statement expects a variable as the second argument. Since Type is a restricted keyword, it can’t be used as a variable name.
Rename the variable so it doesn’t conflict with restricted keywords.
For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
Compile Error:- expected function or variable problem
LinkBack
Thread Tools
Rate This Thread
Display
Compile Error:- expected function or variable problem
A Code Problem — In Microsoft Visual Basic, I’m getting this message,
Compile Error:- expected function or variable
What’s Wrong?
The Sub Hide_Protect() is highlighted in Yellow.
Cells.Select — Cells. is hightlighted in Blue.
Last edited by skin.uk1; 07-08-2011 at 01:12 PM .
Re: Why is this Macro not working?
Hi skin.uk1, before anyone can help you, you must amend your title as per Rule #1 and also wrap your code in code tags as per Rule #3 in the forum rules located here.
If you’re happy with someone’s help, click that little star at the bottom left of their post to give them Reps.
—Keep on Coding in the Free World—
Re: Why is this Macro not working?
Hi
I have amended it, Can anyone help me, I’m stuck?
Re: Macro — Code Problem?
Hi skin.uk1, you haven’t used code tags around your code as per rule 3. We are not aloud to help unless all the rules are followed. Sorry, but even those that help are chastised by mods if the thread doesn’t follow forum rules.
Re: Macro — Code Problem?
Sorry, I am new to all this!
Can you help me now please, I really need help.
Re: Macro — Code Problem?
Is this title more accurately to my problem under rule 1.
If not please help?
Re: Compile Error:- expected function or variable problem
I changed the title using your own words —
Re: Compile Error:- expected function or variable problem
Thank you,
Can you please help me now, I really need help because I’m stuck and can not move forwards with help.
Re: Compile Error:- expected function or variable problem
Hi skin.uk1, I don’t get any errors with your code. However, I don’t know what it is you are trying to do. Perhaps you could upload a mock workbook with mockdata, as well as provide before and after scenarios with your data.
Re: Compile Error:- expected function or variable problem
I have uploaded a mockup! I have manually done the coding up to 13th!
Last edited by skin.uk1; 07-08-2011 at 01:19 PM .
Re: Compile Error:- expected function or variable problem
Thanks for the upload skin.uk1 but what do you want done? All the sheets look the same, even after sheet 13. As far as your code goes, all it seems to be doing is selecting and activating ranges and nothing else.
Re: Compile Error:- expected function or variable problem
I want to record a Macro to make life and time easier. I have done 1st — 12th Manually.
This is what I have done.
Click on Record a Macro.
Macro name:- Hide_Protect
CTRL +h
Click on top left of the screen to highlight the whole sheet.
Right click — format cells, Protection tab, untick locked.
I Highlight using CTRL & the mouse on the following cells,
B9, C9, G9, H9,
H18, H30:H62,
H66:H98, C98:G98.
I still hold down CTRL, right click, format cells, Protection tab, tick — Locked & Hidden.
I go to Tools, Protection, protect sheet, enter password twice. ok
When I try to run the Macro (CTRL+h) on 14th,
Microsoft Visual Basic pops up,
with a box saying — Compile error:- expected function or Variable.
The coding is at top of page.
Help, what have i done wrong.
Re: Compile Error:- expected function or variable problem
Give the following code a try instead. It will loop through each sheet for you, unlock all cells, lock only the cells you specified, and then protect the sheet with a password (just change the password to desired»)
Notes about the macro:
May give an error if any of the sheets are already protected.
Hope that helps,
Re: Compile Error:- expected function or variable problem
FYI, your error is due to you having a sub in the other module called Cells. Since Cells is a built-in Excel property, that’s not a good name — if you rename that sub (e.g. mycells) then your code works, though it is not efficient (all that scrollrow stuff is unnecessary but the macro recorder generally records all the steps you take in Excel including just moving around the worksheet)
I would suggest you use Tigeravatar’s code instead.
Welcome to the forum, by the way, and thank you for amending your post as requested.
VBA Compile Error
In this Article
This tutorial will explain what a VBA Compile Error means and how it occurs.
Before running your code, the VBA Editor compiles the code. This basically means that VBA examines your code to make sure that all the requirements are there to run it correctly – it will check that all the variables are declared (if you use Option Explicit which you should!), check that all the procedures are declared, check the loops and if statements etc. By compiling the code, VBA helps to minimize any runtime errors occurring.
(See our Error Handling Guide for more information about VBA Errors)
Undeclared Variables
If you do not declare variables, but your Option Explicit is switched on at the top of your module, and then you run the macro, a compile error will occur.
If you click OK, the relevant procedure will go into debug mode.
Alternatively, before you run your code, you can force a compilation of the code.
In the Menu, select Debug > Compile Project.
The compiler will find any compile errors and highlight the first one it finds accordingly.
Undeclared Procedures
If you code refers to a procedure that does not exist, you will also get a compile error.
However, if the procedure – NextProcedure does not exist, then a compile error will occur.
Incorrect Coding – Expected End of Statement
If you create a loop using For..Each..Next or With..End With and forget to and the Next or the End With… you will also get a compile error.
The same will happen with an If statement if the End If is omitted!
Missing References
If you are using an Object Library that is not part of Excel, but you are using the objects from the library in your variable declaration, you will also receive a compile error.
This can be solved by either Late Binding – declaring the variables are Objects; or by adding the relevant Object Library to the Project.
In the Menu, select Tools > References and add the relevant object library to your project.
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! 
VBA Code Examples Add-in
Easily access all of the code examples found on our site.
Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.