Expected function or variable vba ошибка

Compile error expected function or variable

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

Источник

Compile error expected function or variable

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.

Источник

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.

Источник

Compile error expected function or variable

Лучший отвечающий

Вопрос

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

Источник

Thread: VBA module call in userform to diff sheets error: «expected function or variable»

Thread Tools

Display

VBA module call in userform to diff sheets error: «expected function or variable»

new here and would like to ask if someone could possibly check my code to see where i’m making a mistake.

first, i’ve created a form with two textboxes and two buttons that will go and get two different directories and the associated files. this is done through a call to a function that loads the dir to the textboxes.

then, i am trying to have a button get the two files that are loaded into the textboxes for comparing/diffing by a module.

a button to call a function to navigate dir and get the file loaded into textboxe

up to here, the code is ok. can do better with the code, but here’s where problems occur. i’d like to pass the directories as objects into the module to diff and there’s an error: «expected function or variable»

button that executes module to diff:

i know that i’ve changed myPath1 and myPath2 to Workbooks, where I’ve had them as strings before

So my question is. can someone please assist in seeing where i’m going wrong? i guess i’m not sure why there’s an error at all. much appreiated. and sorry for the verbose post.

Re: VBA module call in userform to diff sheets error: «expected function or variable»

If the error is on «getTheWorkbooksToCompare», then try this small change:

Re: VBA module call in userform to diff sheets error: «expected function or variable»

will have the same issue. do you have any suggestions?

Re: VBA module call in userform to diff sheets error: «expected function or variable»

Wouldn’t the fact that these two variables:

are dim’d within the selectFile function cause a problem when using them here:

I might be misunderstanding the flow of the code.

Re: VBA module call in userform to diff sheets error: «expected function or variable»

hi. i’m not sure that it would, but please correct me if i am wrong. because i believe i’m loading the file path into the textbox and then grabbing that file path from the textbox to pass as an object to the function for diffing. i guess i’m getting a little confused since i’ve tried so many ways to pass the object that i’m confusing myself now.

i’ve also tried to change the module call as suggested to

though, i believe i tried this a while ago without success.

Re: VBA module call in userform to diff sheets error: «expected function or variable»

Please post one continuous listing of your code. It is hard to discern what your code is from all these fragments.

Re: VBA module call in userform to diff sheets error: «expected function or variable»

Re: VBA module call in userform to diff sheets error: «expected function or variable»

DIR returns a string, do not use the SET keyword, that is for objects

depending what you want to do
if you are trying to open the fileNamePath1 then

where wb1 is a workbook or object
but if you are doing this from within Excel, you do not need (in most cases) to create an additional instance of excel, just use the application object

Dim book1 As Workbook
Dim book2 As Workbook

Call getWorkbooksToCompare(book1, book2)

this looks like you are passing empty workbook objects to someother procedure, which i an sure would give an error

i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

come back and mark your original post as resolved if your problem is fixed
pete

Re: VBA module call in userform to diff sheets error: «expected function or variable»

i’ve taken you suggestions and the code went further into the area that i’v not listed, which was the diff area. i get an error of: «for each object may only iterate over a collection object or an array»

here is the code that i left out to diff files

also. thank you for your assistance

Re: VBA module call in userform to diff sheets error: «expected function or variable»

so i think i fixed some of the above issues with everyone’s help. i now have an error at the above while loop that reads: «object doesn’t support this property or method». the error occurs on the commented while loop and the uncommented version

Re: VBA module call in userform to diff sheets error: «expected function or variable»

myWorksheetCounter is now a numeric value

you can loop through the worksheets in several ways, the two most common would be

you are trying several codings, but mixing which you work with further down

i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

come back and mark your original post as resolved if your problem is fixed
pete

Re: VBA module call in userform to diff sheets error: «expected function or variable»

i saw the same while i was working through the logic. i think i’m a bit stumped on the language. is there an example that you could please provide?

Re: VBA module call in userform to diff sheets error: «expected function or variable»

i did not test this
Note using usedrange to compare worksheet areas can be unreliable, as it may extend past the rows or columns of values
unless you are sure that the data is always the same rows or columns, even an equal count of cells may not mean that the data area matches, you may need to count either rows or columns are equal as well

depending on the size of the used range, it may be much faster to work with arrays of the data

i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

come back and mark your original post as resolved if your problem is fixed
pete

Re: VBA module call in userform to diff sheets error: «expected function or variable»

hi. tried the suggestion and received a run time error 438: object doesn’t support this property or method. the starting while loop at

is highlighted. am i missing something here in my types?

Re: VBA module call in userform to diff sheets error: «expected function or variable»

you can not compare anything with NULL

i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

come back and mark your original post as resolved if your problem is fixed
pete

Re: VBA module call in userform to diff sheets error: «expected function or variable»

thank you. that helped.

so, i’ve made the change and received an error of: run-time error 91 object variable or with block variable not set, where the

also, if you could please shed some light on my data structures here.

Last edited by whatever; Nov 9th, 2014 at 01:11 PM .

Re: VBA module call in userform to diff sheets error: «expected function or variable»

not at all, of course you can assign values to a variable within a loop
definitely the set keyword should not be used as worksheets.count returns an integer, not an object.

the error appears to mean there is no activeworkbook at that time, which is a bit strange

please explain what you want to know

i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

come back and mark your original post as resolved if your problem is fixed
pete

Re: VBA module call in userform to diff sheets error: «expected function or variable»

in the same thought process as you had mentioned about there is no activeworkbook at that time. this part is confusing me, because there should be an activeworkbook that’s passed.

so i’m thinking i’m doing something incorrectly in passing a reference or value to the workbook object. i’m not very proficient in the semantics of vb and some configurations i would expect to work are not working.

ultimately, i’d like to see a third workbook created with a diff, which is not happening.

Re: VBA module call in userform to diff sheets error: «expected function or variable»

i think i’ve been able to get passed most of the issues that were plaguing me before. the only issue i’m having now, is that i’m not seeing my diff in the new (3rd) workbook. anyone have any ideas why that may not be occurring? much appreciated

Re: VBA module call in userform to diff sheets error: «expected function or variable»

this does not make sense
for cell = 1 to must be another number or a variable or cell containing a number
cell would then always then be a number, not a range object

also it is good practice not to use reserved words (like cell) as names for variables
better to use descriptive names or abbreviations

better in this case to use for each cel in thisrange

i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

come back and mark your original post as resolved if your problem is fixed
pete

Re: VBA module call in userform to diff sheets error: «expected function or variable»

thanks. that fixed the loop issue.

do you have any ideas on the If statement that you had pointed out earlier? i’ve tried several ways and i’m getting an object required error.

Re: VBA module call in userform to diff sheets error: «expected function or variable»

Try changing «cell» to «cel» as per Pete’s suggestion. When you get the error, can you confirm that it’s not «myWorksheetObj2» that is the problem?

Re: VBA module call in userform to diff sheets error: «expected function or variable»

hi. yes, i made the change as he had suggested. here is the new version of code without errors, but no output to the 3rd workbook. hmmm.

Re: VBA module call in userform to diff sheets error: «expected function or variable»

Can you zip and attach your workbooks, with the entire current code, if not overly sensitive?

Re: VBA module call in userform to diff sheets error: «expected function or variable»

i’ll remove some things but keep the idea there. is that ok?

Re: VBA module call in userform to diff sheets error: «expected function or variable»

Re: VBA module call in userform to diff sheets error: «expected function or variable»

so i can send the information that i’ve made. just not the rest. if that’s ok. how would i go about uploading?

Re: VBA module call in userform to diff sheets error: «expected function or variable»

you have to zip a workbook to attach it (in Advanced mode).

Re: VBA module call in userform to diff sheets error: «expected function or variable»

so after some assistance from vbfbryce. i’m a lot further and just have a small issue of trying to get the proper output.

1) would like to take the iteration that’s already occurring and print out the names of the files and sheets, if there is a difference of a cell between the sheets
2) also, would like to print out the cell that’s from both sheets in column A and another in column B, to display the side by side diff.

Re: VBA module call in userform to diff sheets error: «expected function or variable»

where would you like to print these?

test if this does anything like what you want, make sure to assign rw a starting value before looping, else will cause an error

i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

come back and mark your original post as resolved if your problem is fixed
pete

Re: VBA module call in userform to diff sheets error: «expected function or variable»

hi. assigning rw a value such as rw = 0?

Re: VBA module call in userform to diff sheets error: «expected function or variable»

There isn’t a «row 0,» so you would want to initialize it to 1, not 0.

Re: VBA module call in userform to diff sheets error: «expected function or variable»

hi everyone. i think i’m a lot closer now. i’m trying to figure out if there’s a way to go from sheet tab 1 to n? i was trying this with a standard for loop:

Re: VBA module call in userform to diff sheets error: «expected function or variable»

Not exactly sure what you mean, but if you’re wanting to do something with the first «x» tabs (worksheets) in a workbook, try something like this:

Re: VBA module call in userform to diff sheets error: «expected function or variable»

is there a way to save the compared worksheets into one workbook? currently, the wbNew.SaveAs creates a workbook for each workbook. i’ve tried placing the wbNew.SaveAs inside the main if statement and at each for loop. so i’m wondering if there’s a better way to

Re: VBA module call in userform to diff sheets error: «expected function or variable»

as you are looping sheets and looping cells within sheets, you would need to open the new workbook before starting to loop anything and make sure not to open any further workbooks within any loop, you do not need to save the new workbook until after the outer loop, but it would not greatly affect any thing if additional saves are performed, make sure not to reset the value of rw within the any loop or it would cause some existing values to be overwritten

add workbook
rw = 8
add sheet headers row 7
loop sheets
loop cells
if cell different, then write details to workbook on row rw, increase rw by 1
next inner loops
you could increase rw by 1 here to leave a line space between worksheets
next outer loop
save and close new workbook

Last edited by westconn1; Nov 12th, 2014 at 03:25 PM .

i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

come back and mark your original post as resolved if your problem is fixed
pete

Источник