Transferring Variables

S

stephen antoine

Board Regular
Joined
Jun 4, 2008
Messages
128
Hello all,

Is there a way to transfer variables between modules and between forms and modules? There's quite a bit of code here, but I figure the VBA gurus that I need help from won't be scared off by it...

I have the following code:

Rich (BB code): 
Private Sub cmdOK_Click()
 
'This sets up the variables I want to use
 
CMPath = "J:\Utilities\Building Rent\FY2008\Rent Reconciliation\" & CMFolder.Value & "\"
CM_Current_Payments_Export = "Export_Current_Payments_Active_" & CMFile.Value & ".XLS"
CM_PP_Rec = "Rent_Rec_" & CMFile.Value & ".XLS"
 
PMPath = "J:\Utilities\Building Rent\FY2008\Rent Reconciliation\" & PMFolder.Value & "\"
PM_PP_Rec = "Rent Rec - " & PMFile & ".XLS"
PPRentForm.Hide
End Sub
 
Private Sub UserForm_Initialize()
 
'This populates the list boxes with the choices I want available
 
With PPRentForm.CMFolder
    .AddItem "01 - Jan"
    .AddItem "02 - Feb"
    .AddItem "03 - Mar"
    .AddItem "04 - Apr"
    .AddItem "05 - May"
    .AddItem "06 - Jun"
    .AddItem "07 - Jul"
    .AddItem "08 - Aug"
    .AddItem "09 - Sep"
    .AddItem "10 - Oct"
    .AddItem "11 - Nov"
    .AddItem "12 - Dec"
 
End With
With PPRentForm.CMFile
    .AddItem "Jan-2008"
    .AddItem "Feb-2008"
    .AddItem "Mar-2008"
    .AddItem "Apr-2008"
    .AddItem "May-2008"
    .AddItem "Jun-2008"
    .AddItem "Jul-2008"
    .AddItem "Aug-2008"
    .AddItem "Sep-2008"
    .AddItem "Oct-2008"
    .AddItem "Nov-2008"
    .AddItem "Dec-2008"
End With
With PPRentForm.PMFolder
    .AddItem "01 - Jan"
    .AddItem "02 - Feb"
    .AddItem "03 - Mar"
    .AddItem "04 - Apr"
    .AddItem "05 - May"
    .AddItem "06 - Jun"
    .AddItem "07 - Jul"
    .AddItem "08 - Aug"
    .AddItem "09 - Sep"
    .AddItem "10 - Oct"
    .AddItem "11 - Nov"
    .AddItem "12 - Dec"
End With
With PPRentForm.PMFile
    .AddItem "Jan-2008"
    .AddItem "Feb-2008"
    .AddItem "Mar-2008"
    .AddItem "Apr-2008"
    .AddItem "May-2008"
    .AddItem "Jun-2008"
    .AddItem "Jul-2008"
    .AddItem "Aug-2008"
    .AddItem "Sep-2008"
    .AddItem "Oct-2008"
    .AddItem "Nov-2008"
    .AddItem "Dec-2008"
End With
End Sub

Everything works just fine, but I want to be able to take the variables I established when the user clicks OK and use them in a different module.

The other module has the following code. I tried to highlight where I'll be using the variables:

Rich (BB code): 
Sub PP_Rent_Rec()
'
' PP_Rent_Rec Macro
' Set up PP Rent Rec (created 10/4/07)
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
PPRentForm.Show ' This code shows the form
 
 
  'INITIAL SETUP
 
    ActiveWindow.FreezePanes = False
    ActiveSheet.Name = "Rent_Rec"
    Sheets.Add.Name = "Current_Payments"
    Sheets("Current_Payments").Move After:=Sheets(Sheets.Count)
 
    Workbooks.Open FileName:= _
        CMPath & CM_Current_Payments_Export
    Cells.Copy
    Windows(CM_PP_Rec).Activate
    Sheets("Current_Payments").Paste
    Workbooks(CM_Current_Payments_Export).Close savechanges:=False
 
    Sheets("Rent_Rec").Select
    Columns("U:U").Copy
    Columns("V:Y").Select
    ActiveSheet.Paste
    Selection.ClearContents
    Range("U10").Select
    Selection.Copy
    Range("U10:Y10").Select
    ActiveSheet.Paste
 
' END INITIAL SETUP
 
' FORMATTING
 
    Range("V11").Select
    ActiveCell.FormulaR1C1 = "CAM, Txs, Ins"
    Range("V12").Select
    ActiveCell.FormulaR1C1 = "& Utilities"
    Range("V11").Select
    Range("W11").Select
    ActiveCell.FormulaR1C1 = "Current Pymts"
    Range("W12").Select
    ActiveCell.FormulaR1C1 = "Export"
    Range("X12").Select
    ActiveCell.FormulaR1C1 = "Difference"
    Range("Y12").Select
    ActiveCell.FormulaR1C1 = "Explanation"
    Range("R10:Y10").Select
 
    With Selection
        .HorizontalAlignment = xlCenter
        .Font.Bold = True
 
    End With
 
' END FORMATTING
' TEXT TO COLUMNS
   Columns("B:C").Insert shift:=xlToRight
   Range("A15").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 9), Array(2, 2), Array(4, 9), Array(5, 2), Array(10, 9), _
        Array(11, 2)), TrailingMinusNumbers:=True
    Columns("B:C").EntireColumn.AutoFit
    Columns("A:A").ColumnWidth = 5
    Columns("D:V").Group
 
 
' END TEXT TO COLUMNS
' FORMULAS
 
  'PP Rent SumIf'
 
Range("C3").Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 22).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(Current_Payments!C6,RC[-23],Current_Payments!C34)"
    Selection.Copy
    ActiveCell.Offset(0, -2).Range("A1").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(0, 2).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
 
    'Difference'
 
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]-(RC[-3]+RC[-2])"
    Selection.Copy
    ActiveCell.Offset(0, -1).Range("A1").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
 
    'CAM, Txs, Ins, & Util SumIF'
 
    Workbooks.Open FileName:= _
       PMPath & PM_PP_Rec
    Windows(CM_PP_Rec).Activate
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, -2).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUMIF('[Rent Rec - May-2008.XLS]Rent Rec'!C2,C2,'[Rent Rec - May-2008.XLS]Rent Rec'!C24)"
    Selection.Copy
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(0, -1).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
 
    'Sum Totals'
 
    Selection.End(xlDown).Select
    ActiveCell.Offset(3, -1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
    Selection.Copy
    ActiveCell.Range("A1:D1").Select
    ActiveSheet.Paste
 
' END FORMULAS
 
'FREEZE PANES
Range("D13").Select
ActiveWindow.FreezePanes = True
'CLOSE SHEETS'
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

If someone can help on this, it would really be apprecated...:)
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
stephen

No offense but I think before you try passing a variable(s) to another sub, which is pretty straightforward you should try and clean the code up a bit.

If you are just hiding the form then you should still be able to refer to the values on it rather than creating variables in the Click event.
 
Upvote 0
No problem, this was one of the first macros I worked on so I know it's in need of some scrubbing...

What is the code for transferring variables though?

Also, are you saying I should do the following:

Code: 
'THIS IN MY MAIN MODULE
 
PPRentForm.Show
CMPath = "J:\Utilities\Building Rent\FY2008\Rent Reconciliation\" & CMFolder.Value & "\"
CM_Current_Payments_Export = "Export_Current_Payments_Active_" & CMFile.Value & ".XLS"
CM_PP_Rec = "Rent_Rec_" & CMFile.Value & ".XLS"
PMPath = "J:\Utilities\Building Rent\FY2008\Rent Reconciliation\" & PMFolder.Value & "\"
PM_PP_Rec = "Rent Rec - " & PMFile & ".XLS"

Code: 
'THIS ON MY FORM FOR THE BUTTONCLICK EVENT
 
PPRentForm.Hide
 
Upvote 0
Stephen

Not 100% sure.

But like I said if you are only hiding the userform you still have access to the values on it as it's still in memory.

PS I don't actually see where you are calling the other sub.:eek:
 
Upvote 0
I got it to work...

In my main module I was using the following for my variables:

Code: 
CMPath = "J:\Utilities\Building Rent\FY2008\Rent Reconciliation\" & CMFolder.Value & "\"

But I should have used this:

Code: 
CMPath = "J:\Utilities\Building Rent\FY2008\Rent Reconciliation\" & [COLOR=lime]PPRentForm.[/COLOR]CMFolder.Value & "\"

This includes the name of the Form as well...For my button click code on my for it's simply:

Code: 
Private Sub cmdOK_Click()
PPRentForm.Hide
End Sub

This works like a charm....

Thanks...
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
How can i extract the correct leave time on this code?
Replies
1
Views
355
rlv01
R
T
  • Question
Years together I was using a macro for automate a w/book is not as on today why?
Replies
1
Views
341
GlennUK
G
C
  • Question
Workday in VBA
Replies
1
Views
333
Joe4
Joe4
H
  • Solved
VBA copy multiple columns in one file and pasting in another
Replies
3
Views
583
Alex Blakenburg
A
J
  • Question
Excel VBA to Sum to bottom of column of data
Replies
3
Views
611
Joe4
Joe4

Forum statistics

Threads
1,214,430
Messages
6,119,438
Members
448,897
Latest member
dukenia71

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top