bpascal, > Dim Comp() As String > Dim PartR() As String > > So the sub would be: > call BellyDance(CompBis(), PartBis(), wksA, wksB, wksC) > or > BellyDance CompBis(), PartBis(), wksA, wksB, wksC
Actually, you call the arrays one thing when you declare them with DIM and something else I nyour procedure calls. With that change, what you wrote works for me. However, there are a couple other changes in order... > Private Function BellyDance(ByRef ArrX() As String, ByRef ArrY() As _ > String, wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet) You don't declare the return type for your function. It will default to variant, but better to declare to what you want. Probably Range, since you return a range: Private Function BellyDance(ByRef ArrX() As String, ByRef ArrY() As _ String, wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet) As Range Speaking of which. It's a function, and functions return objects or values, and you set a return value for your function. If you call it like you described calling it, you lose the return value. You probably want to call it along the lines of: Set rangeBelly = BellyDance(array1(), array2(), ws1, ws2, ws3) > but when I was running either the first or second one, I would the get > an object error or something related where I calling is made. Hard to say what your issue is without knowing the error. But double check the data types you are passing. Look in the Locals pane in VBE/macro editor when the error occurs. It will show all your variables, their data types, values, and members. You can also use the immediate pane to do detective work. Asa -----Original Message----- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of bpascal123 Sent: Friday, March 09, 2012 1:04 PM To: MS EXCEL AND VBA MACROS Subject: Re: $$Excel-Macros$$ how to make a sub with arrays parameters Yes, the function is one line, underscore is just missing from copy paste in this forum. I declared wksA, B, C as worksheet, and Comp() and PartR() dynamic array like Dim Comp() As String Dim PartR() As String So the sub would be: call BellyDance(CompBis(), PartBis(), wksA, wksB, wksC) or BellyDance CompBis(), PartBis(), wksA, wksB, wksC but when I was running either the first or second one, I would the get an object error or something related where I calling is made. Thanks if you can help On Mar 9, 8:40 pm, "Asa Rossoff" <a...@lovetour.info> wrote: > Hi Cyberuser :) > > 1) why pass _wk1 (or is _ supposed to be a line continuation and its wks1)? > You don't refer to wks1 in your procedure. > > 2) what do you mean -- how can you change it to... -- it looks to me like > what you want to change it to is exactly the procedure declaration you're > already using... (?) > > 3) yes, call is optional. There are two (main) ways to call sub > procedures: > > a. ProcedureName param1, param2, param3... > > b. Call ProcedureName (param1, param2, param3...) > > Basically, omit the parenthesis if you omit the Call statement. I don't use > Call. > > Function procedures on the other hand are designed to return values. If you > call a function procedure using the Call statement, you can't use the return > value, kind of defeating the purpose. To call a function, just use it as > part of an expression. For example: > > a. Set myrange = BellyDance(. . . . .) > > b. Range("A1").Value = BellyDance(. . .).Cells(2,3).Address > > Asa > > > > > > > > -----Original Message----- > From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] > > On Behalf Of bpascal123 > Sent: Friday, March 09, 2012 11:45 AM > To: MS EXCEL AND VBA MACROS > Subject: $$Excel-Macros$$ how to make a sub with arrays parameters > > Hi cyberspace, > > In a previous code, I wanted to implement a routine within a sub that > > would perform the last part of the code : > > Private Function BellyDance(ByRef ArrX() As String, ByRef ArrY() As > > String, _wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet) > > Dim rg As range > > Dim i As Long, j As Long > > Dim r As Long > > 'rg.Value = "ok" > > r = 2 > > For i = 0 To UBound(ArrX, 1) > > For j = 0 To UBound(ArrY, 1) > > If UCase(ArrY(j, 1)) = UCase(ArrX(i, 1)) Then > > wks2.Rows(ArrY(j, 0)).Copy wks3.Rows(r) > > r = r + 1 > > End If > > Next j > > Next i > > Set rg = wks3.range("A1").CurrentRegion > > Set BellyDance = rg > > End Function > > How can I change this to : > > Sub BellyDance(ByRef ArrX() As String, ByRef ArrY() As String, _wks1 > > As Worksheet, wks2 As Worksheet, wks3 As Worksheet) > > and the syntax for the calling part as it seems "call" is optional > > when calling a sub, sometimes I see ... call name_of_the_sub ...and > > sometimes i can just see ... name_of_the_sub ... > > Is there any difference? > > I know how to call sub without parameters and sometimes with > > parameters but for this code above, it wouldn't work, error at the > > line of the call. > > Any idea? > > Cyberuser, > > -- > > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will > not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > --------------------------------------------------------------------------- - > -------------------------- > > To post to this group, send email to <mailto:excel-macros@googlegroups.com> > excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
