Can't get variables to pass from one procedure to same in the same module

**valhalla_33** · 05-20-2015, 10:13 PM

The code I have works great as separate procedures. I decided to combine the 2 procedures as the code is mostly the same with a few differences in which cells are copied to the array to save to different log sheets. Each Procedure has a Inputbox to get the year of interest (2014, 2015, 2016....) that will be used to open AND in the event the Log does not exist Save a new one from a master using the year code in the file name. Based on a cell value I am doing an If_Then_Else to determine to run one procedure or both. If I run them separately as individual procedures they work fine. When I combined them I decided to call one procedure (SaveToLog_Audit) from inside (SaveToLog_Replc). I removed the Date input from (SaveToLog_Audit) letting (SaveToLog_Replc) get that date code and pass it along. I don't want to have (SaveToLog_Audit) ask for a date a second time as it run since I basically want it to be inobtrusive (no need to ask twice when I can pass 'Ans' to it.

I have tried setting public Ans and used Explicit at the beginning with both procedures in same module --> didn't work. I tried Dim Ans As String after option Explicit --> Didn't work. I tried Public Ans as String --> didn't work. Pretty much I couldn't get it to pass the variable 'Ans" no matter what I tried. I'm still pretty new to coding VBA so any help would be great. Showing the basic code, only the parts that have issues. I apologize in advance if I didn't tag this correctly.

Please Login or Register  to view this content.

**humdingaling** · 05-20-2015, 10:27 PM

kyle has already provided the solution but here's a link for further information on how to pass variables between subroutines

http://www.cpearson.com/excel/byrefbyval.aspx

**Kyle123** · 05-21-2015, 03:41 AM

I haven't really read your code, but as a starter, to pass values, you need parameters (don't use public variables):

Firstly delete

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Should be:

Please Login or Register  to view this content.

It's then called in the other procedure:

Please Login or Register  to view this content.

Right now your Locally scoped variables are overriding you public, but you shouldn't be using public anyway and passing parameters will remove the issue

**valhalla_33** · 05-21-2015, 10:44 AM

I commented out the Dim Ans in both procedures. I found that Dim Ans in both reset the public variable and caused it to fail. Removing the Dim Ans from both allowed the variable to pass unimpeded. Yay! Problem solved.

**Kyle123** · 05-21-2015, 10:45 AM

Yes, that's what I said. I also said that it's wrong

**valhalla_33** · 05-21-2015, 10:50 AM

If I declare it the way you suggest, can the procedures then be placed in separate modules again? I prefer to keep each procedure in it's own module when I can. The exception to that is when I generate a UserForm and that code needs to be in the same place for the buttons and check boxes. Do I add the Ans to the () in SaveToLog_Audit name when I declare the sub or just in the call to that Sub?

**Kyle123** · 05-21-2015, 10:56 AM

Both, as I gave you in the example. Declaring it tells the sub that it requires a parameter, then you pass it the param when you call it.

Yes, they can go in different modules

Can't get variables to pass from one procedure to same in the same module

LinkBack

Thread Tools

Rate This Thread

Display

Can't get variables to pass from one procedure to same in the same module

Re: Can't get variables to pass from one procedure to same in the same module

Re: Can't get variables to pass from one procedure to same in the same module

Re: Can't get variables to pass from one procedure to same in the same module

Re: Can't get variables to pass from one procedure to same in the same module

Re: Can't get variables to pass from one procedure to same in the same module

Re: Can't get variables to pass from one procedure to same in the same module

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How can I call a Module or a sub procedure and run it automatically in current module?

[SOLVED] How to use second procedure (the 2nd Sub) results in first procedure in the same module

[SOLVED] Pass date variable to another procedure/module

[SOLVED] Pass variables from userform to module

[SOLVED] Pass variables from Worksheet_Calculate sub to Module

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions