Re: Compile error: Sub or Function not defined

[fairweather]

Group: Have two worksheets in same workbook. First worksheet is "ReArrangedAddr" Which basically has a command button to click to run a "Sub" behind the second worksheet "Orig SH Register". When I click on button on first worksheet, I get error "Compile Error: Sub or Function not defined"
* * * * * code behind command button * * * * *

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

Please Login or Register  to view this content.

* * * * * * *
Code for Sub attempting to be called by command button

Please Login or Register  to view this content.

Just learning VBA from books and searches on 'net. Can anyone tell what I am doing wrong or missing ?? Many thanks in advance

fairweather

[LJMetzger]

cloudy weather right now.

Please read the FAQ and put in code tags. We are NOT ALLOWED to answer your question if the post does not have CODE TAGS.

Please download a sample workbook so you will get a more correct answer in a shorter amount of time.

Lewis

[6StringJazzer]

There is no "End Sub" to match "Sub RearrangeAddresses()". I don't know if it's missing or if you just didn't include when you pasted your code into your post. If it does not exist then that is part of the problem. Please include all your code from top to bottom.

Also, why are you calling Application.Run? It requires an argument that you didn't include, which suggests maybe you are meaning to do something else.

[6StringJazzer]

LJMetzger, thanks for your support. For new users I am lenient and provide the code tags (the first time), which I was doing at the moment of your post.

[fairweather]

Don't know if I did the minor editing right, but did read on code tags. So reposted based on 2 comments from 6StringJazzer. The Application.Run was something I thought required, seen somewhere in a book and did add End Sub which did not get copied over. But with both changes, still get the compile error, Sub not defined

Group: Have two worksheets in same workbook. First worksheet is "ReArrangedAddr" Which basically has a command button to click to run a "Sub" behind the second worksheet "Orig SH Register". When I click on button on first worksheet, I get error "Compile Error: Sub or Function not defined"
* * * * * code behind command button * * * * *

Please Login or Register  to view this content.

* * * * * * *
Code for Sub attempting to be called by command button

Please Login or Register  to view this content.

[romperstomper]

This syntax is wrong:

Please Login or Register  to view this content.

I assume you meant:

Please Login or Register  to view this content.

[6StringJazzer]

Also, you have "Option Explicit" in the middle of a sub. It is only valid before the first Sub or Function in a module. Move it up to the top.

[fairweather]

Am trying to correct syntax, also moved "Option Explicit" up

Still have the Compile Error problem, am reposting revised code

Please Login or Register  to view this content.

[6StringJazzer]

When asking for help with a compiler error always list the error you are getting. The error in your latest code is "Else without If".

You have two Else statements. You are only allowed one. I have no idea what you intend here.

Please Login or Register  to view this content.

[fairweather]

Okay, seen that only two "Else" statements allowed so have tried a different coding approach. I know I have a lot to relearn, but I'm beginning to think this compiler really doesn't like me. Man, this is a BIG relearning curve, but if you guys hang in, I'll try to make the grade.

So have changed coding strategy to use of an embedded "Do While" loop with the hope that this will get me out of the two "ELSE" statement situation, but even this effort isn't cutting it for the compiler. Since I don't know the problem, can't put something meaningful in the title bar.

So for the gurus following along, have reposted the changed approach. Hopefully your sharp eyes will find my missteps or lessons yet to be learned. Reposted code to follow:

Please Login or Register  to view this content.

[protonLeah]

Which line is the compiler highlighting?
Which code is in a sheet module and which in a standard module.

[Norie]

What, in words, is the code supposed to do?

[fairweather]

This is my third try at making a reply. The last attempt was wiped out when the forum's response was I was not qualified to post a reply which is kind of silly in that I filed the original post . . . . and now I have to reconstruct the post with examples I had intended to include. Why wipe it out? At least leave it in a draft form so I don't have to retype everything.

Alright, here goes again. There are many more things that I don't know how to do than things I do know. First, I don't know or where to edit the Title, second, to proton.eah's question, I didn't know there were different kinds of modules. I checked both of the books I have and couldn't find any discussion on that topic so can't answer the question.

The intent of program or code is to convert a column of address elements to a series of rows of the address elements grouped so as to be a complete address for mail merge. The grouping is by blank rows that had personal information such as number of shares to each and that personal information has been cleared.

The line of code that is highlighted in yellow when the command button is clicked is the first line of the code shown below. It is the code behind the button and is for sheet "ReArrangedAddr"

Please Login or Register  to view this content.

It is intended to call the program or code to transpose the address elements

Example:
address 1
address 2 TO address 1 address 2 address 3
address 3

[6StringJazzer]

First, I don't know or where to edit the Title

Go to your first post. Click Edit Post underneath. Then in the next screen, under the edit box, click Go Advanced. Now at the top of the screen will be your title inside a text box, which you can edit. I think there is a two-day time limit, which has passed for this thread. If so you can report your own post and request that a moderator change the title for you.

second, to proton.eah's question, I didn't know there were different kinds of modules. I checked both of the books I have and couldn't find any discussion on that topic so can't answer the question.

There are several types of modules. Here is a tutorial explaining them with a screen shot of what they look like in the development window.

[fairweather]

The intent of program or code is to convert a column of address elements to a series of rows of the address elements grouped so as to be a complete address for mail merge. The grouping is by blank rows that use to contain personal information such as number of shares to each, that personal information has been cleared so blank.

The line of code that is highlighted in yellow when the command button is clicked is the first line of the code shown below. It is the code behind the button and is for sheet "ReArrangedAddr"

Please Login or Register  to view this content.

It is intended to call the program or code to transpose the address elements

Example:
address 1
address 2 TO address 1 address 2 address 3
address 3



6StringJazzer: You were right about the two day limit, I found the title in the text box and could edit it, but there is no button to post the change. So can you or one of the other moderators change the Title to something like "Compile error, Sub Not defined, don't know why"

After viewing the tutorial on modules, to proton.eah's question, I think the command button would be a Forms module since it is dealing with an event changing instruction. The code behind the button is I think, a forms module since it deals with a event change instruction (sheet "ReArrangedAddr"). The first line is highlighted in yellow when the button is clicked. Three lines of code below

The second line of code should execute the Sub / code (below) that actually does the transposing work

Please Login or Register  to view this content.

So bottom line is something is making the compiler unhappy so I can't yet check the transposing code to see if it works.

fairweather

[6StringJazzer]

This code compiles, if configured properly. What modules are these two pieces of code in?

Please attach your file, removing any private data.

[fairweather]

As I read further about modules I'm getting more confused. Then I checked the "Modules LIMB" of the tree diagram for my project and saw that the only code within "Module1" was from an initial attempt at using the macro recorder, none of the code behind either of the two sheets (ALT + F11) is apparently not in any kind of module, or so it seems. Silly me, I guess I just thought the Excel program would put the code where it should be, but apparently not so. I just did Alt + F11 and starting putting code in the code window. But apparently isn't that easy or that isn't the way it works. So where or how do I learn about these modules and how to get the code into the appropriate module ??

fairweather

[fairweather]

Tried to create a link to my Excel files with file to submit, MS did not like my attempt, so therefore, how does one attach a file to a reply ??

[6StringJazzer]

See post #14 for a link to a tutorial about modules. The macro recorder automatically creates a new Module called Module1 (or whatever number comes next) when you record a macro. There are a few different kinds of modules. You can't have code if it is not in a module.

We're not going to get any further without your file. To attach a file to a post:

1. Under the text box where you type your reply click the Go Advanced button.
2. In the next screen look above the reply box and click the "paper clip" icon. You will get a pop-up screen.
3. In the pop-up, click Add then Select to select a file from your local drive
4. Click the Upload button to upload the file
5. Click Done to attach it.

It will be displayed as an attachment underneath your post text. Alternatively as the last step you can click the Insert Inline button and a hyperlink to the file will be inserted directly into your post text in the spot where your cursor is set at the moment you click the button.

[6StringJazzer]

See post #14 for a link to a tutorial about modules. The macro recorder automatically creates a new Module called Module1 (or whatever number comes next) when you record a macro. There are a few different kinds of modules. You can't have code if it is not in a module.

We're not going to get any further without your file. To attach a file to a post:

1. Under the text box where you type your reply click the Go Advanced button.
2. In the next screen look above the reply box and click the "paper clip" icon. You will get a pop-up screen.
3. In the pop-up, click Add then Select to select a file from your local drive
4. Click the Upload button to upload the file
5. Click Done to attach it.

It will be displayed as an attachment underneath your post text. Alternatively as the last step you can click the Insert Inline button and a hyperlink to the file will be inserted directly into your post text in the spot where your cursor is set at the moment you click the button.

[6StringJazzer]

See post #14 for a link to a tutorial about modules. The macro recorder automatically creates a new Module called Module1 (or whatever number comes next) when you record a macro. There are a few different kinds of modules. You can't have code if it is not in a module.

We're not going to get any further without your file. To attach a file to a post:

1. Under the text box where you type your reply click the Go Advanced button.
2. In the next screen look above the reply box and click the "paper clip" icon. You will get a pop-up screen.
3. In the pop-up, click Add then Select to select a file from your local drive
4. Click the Upload button to upload the file
5. Click Done to attach it.

It will be displayed as an attachment underneath your post text. Alternatively as the last step you can click the Insert Inline button and a hyperlink to the file will be inserted directly into your post text in the spot where your cursor is set at the moment you click the button.

[fairweather]

Moderator: Tried to attach the file. Since, have done more reading and changed some of the code and tried to attach the file. Do not see any confirmation the file got
attached so may not have done right. Could follow everything up to the last step where it said to drag the file and it already seemed to be there, but not sure. If I screwed up, please tell about the last step and how, if there is a way, to verify the file is attached.

Thanks for your time. I guess the changes helped, at least it seems to compile now, but won't display the message "Got here" to try to start debugging

fairweather

[protonLeah]

Remove Module2, remove the code in the sheet1 & sheet2 modules. Leave only the Module1 code.
Right click the command button, select ASSIGN MACRO, then select "RearrangeAddresses"

[fairweather]

140729 - 2115 - ExcelForum Submission.xlsmModerator: Tried to follow the instructions of B. Johnson in his reply and actually started getting some results. Thanks to him for the help and direction. Before I started making some changes in the logic code, I got the message to display. Yeah !! but nothing further so back to the books and 'net for debugging help and among all of that I read of the "Local" window which sounded like a great tool for monitoring what was going on so opened it up and included it in the file of the latest I attached.

I entered some fictitious data to test it. (sheet 2, "Orig SH Register"). Review showed that most was as expected, problem child is the variable, "lastAddrElement" which showed 5 as the number of the last row of data when it should be 25 so I guess the syntax of "LastAddrElement = Cells(Rows.Count, 1).End(xlUp).Row" isn't right. So I would appreciate a little help in the right syntax or whatever else I did wrong.

As always, appreciate your guys time and expertise.

fairweather

[fairweather]

Should close this thread out as "SOLVED" since now compiling and problems are different. Thought this "SOLVED" option notice was in "Go Advance", but can't find how to do that. Perhaps one of the moderators could add the "SOLVED" notice. A good part of the problem had to do with choice and use of modules

fairweather

[6StringJazzer]

To mark as solved, go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

I've done it for you this time.