PDA

View Full Version : [SOLVED] Sub or Function not defined



exele
07-30-2014, 10:44 PM
Hi,

I'm new to VBA but I found yesterday a vba code suitable for my needs and it worked just fine. I tested a couple of times and now I notice that it is not working anymore. All I get is Compile error - Sub or Function not defined. I found some threads about this issue and they were mostly about problems occurring when you test the code for the first time. I do not have typos in the code as I copy&pasted it from the net, and I think I didn't change any excel settings.
Thank you in advance!


''Because this program is in the DDE worksheet, it runs
''each time a value changes
Private Sub Worksheet_Change(ByVal Target As Range)
''Do something only if the value changes in cell A1
If Target.Address = "$A$1" Then
''Look at the full list below the Target title
With ThisWorkbook.Names("ListDDE").RefersToRange.CurrentRegion
''Look at the cell at the bottom of the list
With.Offset(.Rows.Count, 0).Resize(1, 1)
''Enter the current time in the cell
.Value = Now
''Enter the new value to the right of the time
.Offset(0, 1).Value = Target.Value
End With
End With
End If
End Sub

westconn1
07-31-2014, 03:08 AM
there should be a space after the 2nd with, this may just be a typo in the post, but will cause an error
what is highlighted when error occurs?

exele
07-31-2014, 04:22 AM
Yes - that missing space was just a typo in the post. The first row (Private Sub Worksheet_Change(ByVal Target As Range) is highlighted.

westconn1
07-31-2014, 04:42 AM
when does the compile error occur?
what are you doing in excel at that time?

exele
07-31-2014, 05:07 AM
Sorry I didn't explain enough. The error occurs when I for the first time change the value in the cell A1. Earlier when the code worked just fine it was different; when I changed the value in A1, the current time and value of A1 became under the cell that is named ListDDE. And when I changed the value again, the new time and value came visible in the list under ListDDE.

westconn1
07-31-2014, 05:21 AM
i have pasted the exact code from your first post into some worksheet and it is working fine

possibly some other code in the workbook is causing a problem with this

exele
07-31-2014, 11:28 PM
Yes I'm afraid that something else is the problem. I don't have any other codes in my workbook and the code won't even work with a new workbook. Do you know if there is any way to re-boot the whole vba/excel, like if I changed some settings accidentally etc? Thanks!

westconn1
08-01-2014, 01:15 AM
are you pasting the entire sub into a code pane?

try selecting the worksheet selection change event from the dropdowns, then just paste the code within that procedure

afaik there is no setting that will cause this error

exele
08-01-2014, 03:08 AM
Now it says "compile error: syntax error" and highlights the first row when I change the value in A1... I tried both dropdowns; Change and SelectionChange and the same error occurs.

p45cal
08-01-2014, 03:43 AM
Remove any sensitive data first, and having done that, make sure the error still occurs then attach the file.

exele
08-01-2014, 04:16 AM
Actually now the problem solved itself - I copypasted the code once more (after having done it several times) and now it works. I have no idea was the reason for the errors, as the grammar etc were exactly the same when I copyed the text. But thank you for your help!