You copied some Excel VBA code from the Internet, but when you try to run it, you get a compile error with Sub or Function Not Defined. This video shows you what might be causing that error and how to find the line to fix.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel Podcast, Episode 1820: Excel VBA Compile Error. Now What?
Hey.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question is sent in by Molly.
Molly watched one of my YouTube videos and tried to type the code in or recorded the code and edit the code and said, “Intently watched the video and followed the directions, but when I go to run the macro I'm getting ‘Compile Error: Sub or Function not defined.’ Any thoughts?” and here's the screenshot from the video.
I don't know, Molly, how you followed anything.
The resolution back then was so bad, but let's go take a look at some code that I have here.
It's not the exact code from the video but we have a workbook that's called CreatePivot.
Now, as you're typing things, you know, what could go wrong?
You could mistype things.
So, I’m going to come here where we have WSD.Cells and replace the two Ls with two 1s.
It kind of looks the same, and when we go back and attempt to run the macro -- so I've assigned the macro to this button -- I get the “Compile error: Method or data member not found,” and what you need to do is move that window out of the way and something will be highlighted in blue.
So, there, the word that I mistyped is the word highlighted in blue and that is going to be an indication of where your first problem is, alright?
So, always move this message out of the way and see what it is.
Now, rather than running the macro here, we’ll stop.
You can always come here and do Debug, Compile VBAProject before you attempt to run it and it will tell you if there are any obvious errors.
So, here, Debug, Compile VBAProject, and everything is fine.
That doesn't mean that the macro is going to run it.
It just means that it can't detect anything that was wrong in advance.
Like, for example, this xlUp, if we put a 1 there, it will Debug, it'll compile fine.
Here, debug, Compile VBAProject.
It’ll compile fine.
It's not until run time that it'll discover an error, but, Molly, your particular error -- error: sub or function not defined -- that tends to happen when we have some random word that Excel does not understand all alone on a line by itself.
So, Debug, Compile VBAProject, sub or function not defined, and, again, the word is highlighted in blue, but as I'm thinking about it -- I mean, Molly, I know that you wouldn't just randomly type some random word in -- so what I'm thinking has happened is we have either, you know, a two-word command like NextPT or maybe down here, this End With, where the space is missing, and that creates a word that Excel doesn't seem to understand.
So here, if we Debug, Compile Project, that could be the problem.
So, you know, do the Debug, Compile Project, see what is highlighted, and then correct that word or, you know, send me a note with, “This is the line that's highlighted in blue,” but it's still possible that after you typed everything you're going to get a clean compile even if there is a mistake like the x1up and you won't see that until we go to run the code.
Let's take a look at that -- alright, so, application-defined or object-defined error.
Now, nothing's highlighted in blue yet.
You have to click Debug and now we get a line highlighted in yellow, alright?
So, you don't know what the problem is here but the problem is somewhere in that line, and, you know, if you're going back to the person who wrote the code or post a comment on YouTube, it would help to say, you know, “Hey.
This is the line.
So, I'm getting an error [ 1004 ] and this is the line that's highlighted in yellow,” and then, you know, we can try and troubleshoot from there.
I appreciate people who are trying to get into VBA and this is the good way to do it to copy code from the Internet and, you know, start to use that code and modify that code, but you just have to be aware that sometimes you're going to get these errors and, you know, it's not obvious what you need to go fix but, by checking out what's highlighted either in blue or here in yellow after clicking Debug, it will help.
By the way, to get out, you want to press the reset button so that we are not still trying to run that macro.
Well, hey, I want to thank Molly for sending that question in and I want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
Learn Excel from MrExcel Podcast, Episode 1820: Excel VBA Compile Error. Now What?
Hey.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question is sent in by Molly.
Molly watched one of my YouTube videos and tried to type the code in or recorded the code and edit the code and said, “Intently watched the video and followed the directions, but when I go to run the macro I'm getting ‘Compile Error: Sub or Function not defined.’ Any thoughts?” and here's the screenshot from the video.
I don't know, Molly, how you followed anything.
The resolution back then was so bad, but let's go take a look at some code that I have here.
It's not the exact code from the video but we have a workbook that's called CreatePivot.
Now, as you're typing things, you know, what could go wrong?
You could mistype things.
So, I’m going to come here where we have WSD.Cells and replace the two Ls with two 1s.
It kind of looks the same, and when we go back and attempt to run the macro -- so I've assigned the macro to this button -- I get the “Compile error: Method or data member not found,” and what you need to do is move that window out of the way and something will be highlighted in blue.
So, there, the word that I mistyped is the word highlighted in blue and that is going to be an indication of where your first problem is, alright?
So, always move this message out of the way and see what it is.
Now, rather than running the macro here, we’ll stop.
You can always come here and do Debug, Compile VBAProject before you attempt to run it and it will tell you if there are any obvious errors.
So, here, Debug, Compile VBAProject, and everything is fine.
That doesn't mean that the macro is going to run it.
It just means that it can't detect anything that was wrong in advance.
Like, for example, this xlUp, if we put a 1 there, it will Debug, it'll compile fine.
Here, debug, Compile VBAProject.
It’ll compile fine.
It's not until run time that it'll discover an error, but, Molly, your particular error -- error: sub or function not defined -- that tends to happen when we have some random word that Excel does not understand all alone on a line by itself.
So, Debug, Compile VBAProject, sub or function not defined, and, again, the word is highlighted in blue, but as I'm thinking about it -- I mean, Molly, I know that you wouldn't just randomly type some random word in -- so what I'm thinking has happened is we have either, you know, a two-word command like NextPT or maybe down here, this End With, where the space is missing, and that creates a word that Excel doesn't seem to understand.
So here, if we Debug, Compile Project, that could be the problem.
So, you know, do the Debug, Compile Project, see what is highlighted, and then correct that word or, you know, send me a note with, “This is the line that's highlighted in blue,” but it's still possible that after you typed everything you're going to get a clean compile even if there is a mistake like the x1up and you won't see that until we go to run the code.
Let's take a look at that -- alright, so, application-defined or object-defined error.
Now, nothing's highlighted in blue yet.
You have to click Debug and now we get a line highlighted in yellow, alright?
So, you don't know what the problem is here but the problem is somewhere in that line, and, you know, if you're going back to the person who wrote the code or post a comment on YouTube, it would help to say, you know, “Hey.
This is the line.
So, I'm getting an error [ 1004 ] and this is the line that's highlighted in yellow,” and then, you know, we can try and troubleshoot from there.
I appreciate people who are trying to get into VBA and this is the good way to do it to copy code from the Internet and, you know, start to use that code and modify that code, but you just have to be aware that sometimes you're going to get these errors and, you know, it's not obvious what you need to go fix but, by checking out what's highlighted either in blue or here in yellow after clicking Debug, it will help.
By the way, to get out, you want to press the reset button so that we are not still trying to run that macro.
Well, hey, I want to thank Molly for sending that question in and I want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
