How can I pass global variables into a function?

Return variable's value to a function

• QUESTION: I'm contacting you again because I'm trying to understand how pass value between functions in vba. Sub main() i = 0 r = 4 c = 2 rtp = 9 ctp = 2 Call ThisWorkbook.copycells(r, c, rtp, ctp, i) ...... ...... End Sub Sub copycells(r, c, rtp, ctp, i) 'use and increment r,c,rtp and ctp ==> NOW I would return new value of these variables to the "main" where I used them and pass again them to other function. End Sub Could you please help me? Maybe I can understand better how I should do that. Thanks a lot Best ANSWER: simba24d, first, all this code should be in a general module in your workbook - not in the Thisworkbook module. the thisworkbook module should be use for workbook level events. If you go into the Visual basic editor and choose Insert=>Module then that will be a general module. I put this code in that type of module: Sub main() i = 0 r = 4 c = 2 rtp = 9 ctp = 2 sbefore = "r = " & r & ", c = " & c & ", rtp = " & rtp _ & ", ctp = " & ctp & ", i = " & i copycells r, c, rtp, ctp, i sAfter = "r = " & r & ", c = " & c & ", rtp = " & rtp _ & ", ctp = " & ctp & ", i = " & i MsgBox "Before: " & vbNewLine & sbefore & _ vbNewLine & vbNewLine & vbNewLine & "After: " & _ vbNewLine & sAfter End Sub Sub copycells(r, c, rtp, ctp, i) i = i + 1 r = 22 c = -45 rtp = rtp + 1 ctp = ctp + 100 End Sub I ran main and you can see the results. So hopefully that answers your question. the default method of passing variables is by reference. That is what you want to do for what you describe. the other option is by Val. If you pass by val, you only pass the value of the variable to the subroutine and any changes are not passed back. But as I said, if your make no choice (as in your code), they are passed by ref. ---------- FOLLOW-UP ---------- QUESTION: I've just other two questions for you, hope to don't stress or bore so much ;-) Q1: I need to read something (a file name) written on cells merged(7, "G")-(7, "M") and assign that to variable XQ. Set XQ = Worksheets("Sheet1").Range(Cells(7, "G"), Cells(7, "M")).Value For Each labelname In XQ.Worksheets ..... ..... Next I don't understand why it doesn't work. It seems that XQ has not a value. I did some attempts with an easier situation and they worked fine, but not in this scenario. Where am I wrong? Q2: All sheet name that belong to a document are stored into a vector. While macro is going on it generate a new sheets. I've a problem to assigne to those sheet the name stored into the vector. As you suggested to me to read the sheet name of documentA For Each labelname In testplan.Worksheets vectorlabel(i) = labelname.Name Next Now I need to assign to all new sheets generated on documentB the "labelname". I suppose that "ThisWorkbook.ActiveSheet.Name" could be the right way and I'm trying to understand how to use it. Thanks so much Best

• Answer:

  simba24d, The value for merged cells is in the first cell of the merged range msgbox Worksheets("Sheet1").Cells(7,"G").value if you have sheet names listed in columns G to M in row 7, then those cells can't be merged and still retain all the names. When you merge those cells, then anything in range H7:M7 are lost. So I suspect your cells are not actually merged. If you have a list of sheetnames in G7:M7 then If the cells G7:M7 contained sheet names as in the following example: G7: Sheet1 H7: Sheet2 I7: Sheet3 J7: Sheet4 K7: Sheet5 L7: Sheet6 M7: Sheet7 and I had sheets iwth those names the workbook, then I could do: Sub ABC() Dim shts As Sheets Dim sh As Worksheet Set shts = Sheets(Application.Transpose( _ Application.Transpose(Range("G7:M7")))) For Each sh In shts MsgBox sh.Name Next End Sub Hopefully that will help you with what you are trying to do because I really didn't understand your second question.