Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Programming
What is the formula to arrange a set of numbers by a specific number.
Example, I have 1, 2, 3, 4, 5, 6, 7, 8, and 9. I would like to arrange them by (3) every third number. The answer would be: 3, 6, 9, 4, 8, 5, 2, 7, and then 1. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Programming
Interesting problem. I can think of a few different ways to do it, but
nothing very elegant in VBA; another language would make it easier. On the assumption that your numbers are not necessarily consecutive sequences every time, let's try them as character strings delimited by spaces. I'll fill a sample starting string with consecutive numbers, but I don't know how you should get yours. StrFm = "" For iv = 5 to 12 StrFm = StrFm & iv & " " next iv Now you have a string consisting of "5 6 7 8 9 10 11 12 ", your numbers (well, mine) with a space after each one. If you want to pick every 3rd number as you describe below, you could run through that string multiple times, each time peeling one number from the front of the string and moving it to the back end, picking out every third number to transfer to the target string instead: StrTo = "" Count = 3 Do ps = InStr(StrFm, " ") 'find the next space ThisNbr = Left(StrFm, ps) 'copy the next number... StrFm = Mid(StrFm, ps + 1) '...and remove it from the front of the list Count = Count - 1 If Count = 0 Then StrTo = StrTo & ThisNbr 'we'll take this one Count = 3 'start the counter over Else StrFm = StrFm & ThisNbr 'put it back and get another End If Loop While StrFm < "" I just tested this code and it seems to generate the list according to your logic. It's not pretty, though. Maybe a collection would be a bit more elegant. --- "Floyd" wrote: What is the formula to arrange a set of numbers by a specific number? Example: I have 1, 2, 3, 4, 5, 6, 7, 8, and 9. I would like to arrange them by (3) every third number. The answer would be 3, 6, 9, 4, 8, 5, 2, 7, and then 1. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Programming
Thanks Bob but I was looking for a different answer.
If N= 30, then something like that... Your help would be greatly appreciated "Bob Bridges" wrote: Interesting problem. I can think of a few different ways to do it, but nothing very elegant in VBA; another language would make it easier. On the assumption that your numbers are not necessarily consecutive sequences every time, let's try them as character strings delimited by spaces. I'll fill a sample starting string with consecutive numbers, but I don't know how you should get yours. StrFm = "" For iv = 5 to 12 StrFm = StrFm & iv & " " next iv Now you have a string consisting of "5 6 7 8 9 10 11 12 ", your numbers (well, mine) with a space after each one. If you want to pick every 3rd number as you describe below, you could run through that string multiple times, each time peeling one number from the front of the string and moving it to the back end, picking out every third number to transfer to the target string instead: StrTo = "" Count = 3 Do ps = InStr(StrFm, " ") 'find the next space ThisNbr = Left(StrFm, ps) 'copy the next number... StrFm = Mid(StrFm, ps + 1) '...and remove it from the front of the list Count = Count - 1 If Count = 0 Then StrTo = StrTo & ThisNbr 'we'll take this one Count = 3 'start the counter over Else StrFm = StrFm & ThisNbr 'put it back and get another End If Loop While StrFm < "" I just tested this code and it seems to generate the list according to your logic. It's not pretty, though. Maybe a collection would be a bit more elegant. --- "Floyd" wrote: What is the formula to arrange a set of numbers by a specific number? Example: I have 1, 2, 3, 4, 5, 6, 7, 8, and 9. I would like to arrange them by (3) every third number. The answer would be 3, 6, 9, 4, 8, 5, 2, 7, and then 1. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Programming
Not sure what you mean. The below algorithm does indeed
arrange a set of numbers, choosing every third one, and it seems to match the example you gave. Do you mean you wanted to know how to do it to a sequence of numbers from 1 to 30? The below code works for that, after you modify the initial startup loop to match your specs. Do you mean you want to be able to pick every 30th number, not just every 3rd? But the below logic does that too, if only you modify the way Count is handled. What 'N' is supposed to be set to 3? --- "Floyd" wrote: Thanks Bob but I was looking for a different answer. If N= 30, then something like that... Your help would be greatly appreciated --- "Bob Bridges" wrote: Interesting problem. I can think of a few different ways to do it, but nothing very elegant in VBA; another language would make it easier. On the assumption that your numbers are not necessarily consecutive sequences every time, let's try them as character strings delimited by spaces. I'll fill a sample starting string with consecutive numbers, but I don't know how you should get yours. StrFm = "" For iv = 5 to 12 StrFm = StrFm & iv & " " next iv Now you have a string consisting of "5 6 7 8 9 10 11 12 ", your numbers (well, mine) with a space after each one. If you want to pick every 3rd number as you describe below, you could run through that string multiple times, each time peeling one number from the front of the string and moving it to the back end, picking out every third number to transfer to the target string instead: StrTo = "" Count = 3 Do ps = InStr(StrFm, " ") 'find the next space ThisNbr = Left(StrFm, ps) 'copy the next number... StrFm = Mid(StrFm, ps + 1) '...and remove it from the front of the list Count = Count - 1 If Count = 0 Then StrTo = StrTo & ThisNbr 'we'll take this one Count = 3 'start the counter over Else StrFm = StrFm & ThisNbr 'put it back and get another End If Loop While StrFm < "" --- "Floyd" wrote: What is the formula to arrange a set of numbers by a specific number? Example: I have 1, 2, 3, 4, 5, 6, 7, 8, and 9. I would like to arrange them by (3) every third number. The answer would be 3, 6, 9, 4, 8, 5, 2, 7, and then 1. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Programming
On Sep 17, 12:18*am, Floyd wrote:
What is the formula to arrange a set of numbers by a specific number. * Example, I have 1, 2, 3, 4, 5, 6, 7, 8, and 9. I would like to arrange them by (3) every third number. The answer would be: 3, 6, 9, 4, 8, 5, 2, 7, and then 1. I'm not quite sure what you mean by "formula". What you are asking about is related to the classic Josephus problem http://en.wikipedia.org/wiki/Josephus_problem (which asks for the last number listed) and even that is a fairly hard problem - so I don't think a closed form solution which gives the full listing directly without needing to actually step through the process is possible - though I may be wrong there. Maybe you could post this in sci.math. In any event, I wrote a VBA function which takes an array and a step size and returns the array obtained by repeatedly stepping through the remaining items according to the step size. I also include two test routines (the first one to verify output, the second one a rough speed test): Function JosephusPermutation(A As Variant, stepSize As Long) As Variant Dim lower As Long, upper As Long Dim i As Long, j As Long Dim whereAt As Long, fromWhere As Long Dim tempA As Variant, retA As Variant lower = LBound(A) upper = UBound(A) ReDim tempA(lower To upper, 0 To 1) ReDim retA(lower To upper) For i = lower To upper tempA(i, 0) = A(i) tempA(i, 1) = i + 1 Next i tempA(upper, 1) = lower whereAt = lower For i = lower To upper For j = 1 To stepSize - 1 'walk fromWhere = whereAt whereAt = tempA(whereAt, 1) Next j retA(i) = tempA(whereAt, 0) whereAt = tempA(whereAt, 1) tempA(fromWhere, 1) = whereAt 'effectively removing item just selected Next i JosephusPermutation = retA End Function Sub Test() Dim A As Variant, skip As Long Dim i As Long Dim retString As String A = Split(InputBox("Enter numbers, separated by spaces")) skip = InputBox("Enter step size") A = JosephusPermutation(A, skip) For i = 0 To UBound(A) retString = retString & " " & A(i) Next i retString = "Output:" & retString MsgBox retString End Sub Sub Test2() Dim A(1 To 10000) As Integer Dim i As Long For i = 1 To 10000 A(i) = i Next i Range("A1:A10000").Value = Application.WorksheetFunction.Transpose(JosephusPe rmutation(A, 179)) End Sub hth -scattered |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Programming
Thank You Bob and Scattered for your response but I looking for something
different. Maybe the information below is helpful. I posted this question about two year ago and received a different answer. The answer that I received had something to do with the following; {=everynth(20, 3)} This formula represent 20 numbers (1-20) listd by every 3 numbers. The VBA is listed below: Option Explicit Function EveryNth(Num As Long, Nth As Long) Dim i As Long Dim j As Long Dim k As Long Dim x As Variant Dim y As Variant ReDim x(1 To Num) For i = 1 To Num x(i) = i Next i ReDim y(1 To Num) i = 0 j = 0 k = 0 Do i = i + 1 If i Num Then i = 1 If x(i) < 0 Then k = k + 1 If k = Nth Then j = j + 1 y(j) = x(i) If j = Num Then Exit Do x(i) = 0 k = 0 End If End If Loop EveryNth = y End Function This is the Macro that I want to use. Please review, it's just not working for me. "scattered" wrote: On Sep 17, 12:18 am, Floyd wrote: What is the formula to arrange a set of numbers by a specific number. Example, I have 1, 2, 3, 4, 5, 6, 7, 8, and 9. I would like to arrange them by (3) every third number. The answer would be: 3, 6, 9, 4, 8, 5, 2, 7, and then 1. I'm not quite sure what you mean by "formula". What you are asking about is related to the classic Josephus problem http://en.wikipedia.org/wiki/Josephus_problem (which asks for the last number listed) and even that is a fairly hard problem - so I don't think a closed form solution which gives the full listing directly without needing to actually step through the process is possible - though I may be wrong there. Maybe you could post this in sci.math. In any event, I wrote a VBA function which takes an array and a step size and returns the array obtained by repeatedly stepping through the remaining items according to the step size. I also include two test routines (the first one to verify output, the second one a rough speed test): Function JosephusPermutation(A As Variant, stepSize As Long) As Variant Dim lower As Long, upper As Long Dim i As Long, j As Long Dim whereAt As Long, fromWhere As Long Dim tempA As Variant, retA As Variant lower = LBound(A) upper = UBound(A) ReDim tempA(lower To upper, 0 To 1) ReDim retA(lower To upper) For i = lower To upper tempA(i, 0) = A(i) tempA(i, 1) = i + 1 Next i tempA(upper, 1) = lower whereAt = lower For i = lower To upper For j = 1 To stepSize - 1 'walk fromWhere = whereAt whereAt = tempA(whereAt, 1) Next j retA(i) = tempA(whereAt, 0) whereAt = tempA(whereAt, 1) tempA(fromWhere, 1) = whereAt 'effectively removing item just selected Next i JosephusPermutation = retA End Function Sub Test() Dim A As Variant, skip As Long Dim i As Long Dim retString As String A = Split(InputBox("Enter numbers, separated by spaces")) skip = InputBox("Enter step size") A = JosephusPermutation(A, skip) For i = 0 To UBound(A) retString = retString & " " & A(i) Next i retString = "Output:" & retString MsgBox retString End Sub Sub Test2() Dim A(1 To 10000) As Integer Dim i As Long For i = 1 To 10000 A(i) = i Next i Range("A1:A10000").Value = Application.WorksheetFunction.Transpose(JosephusPe rmutation(A, 179)) End Sub hth -scattered |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Programming
On Sep 18, 4:32*pm, Floyd wrote:
Thank You Bob and Scattered for your response but I looking for something different. *Maybe the information below is helpful. I posted this question about two year ago and received a different answer.. * The answer that I received had something to do with the following; {=everynth(20, 3)} This formula represent 20 numbers (1-20) listd by every 3 numbers. The VBA is listed below: Option Explicit Function EveryNth(Num As Long, Nth As Long) Dim i As Long Dim j As Long Dim k As Long Dim x As Variant Dim y As Variant ReDim x(1 To Num) For i = 1 To Num * *x(i) = i Next i ReDim y(1 To Num) i = 0 j = 0 k = 0 Do i = i + 1 If i Num Then i = 1 If x(i) < 0 Then k = k + 1 If k = Nth Then j = j + 1 y(j) = x(i) If j = Num Then Exit Do x(i) = 0 k = 0 End If End If Loop EveryNth = y End Function This is the Macro that I want to use. *Please review, it's just not working for me. "scattered" wrote: On Sep 17, 12:18 am, Floyd wrote: What is the formula to arrange a set of numbers by a specific number. * Example, I have 1, 2, 3, 4, 5, 6, 7, 8, and 9. I would like to arrange them by (3) every third number. The answer would be: 3, 6, 9, 4, 8, 5, 2, 7, and then 1. I'm not quite sure what you mean by "formula". What you are asking about is related to the classic Josephus problem http://en.wikipedia.org/wiki/Josephus_problem(which asks for the last number listed) and even that is a fairly hard problem - so I don't think a closed form solution which gives the full listing directly without needing to actually step through the process is possible - though I may be wrong there. Maybe you could post this in sci.math. In any event, I wrote a VBA function which takes an array and a step size and returns the array obtained by repeatedly stepping through the remaining items according to the step size. I also include two test routines (the first one to verify output, the second one a rough speed test): Function JosephusPermutation(A As Variant, stepSize As Long) As Variant * * Dim lower As Long, upper As Long * * Dim i As Long, j As Long * * Dim whereAt As Long, fromWhere As Long * * Dim tempA As Variant, retA As Variant * * lower = LBound(A) * * upper = UBound(A) * * ReDim tempA(lower To upper, 0 To 1) * * ReDim retA(lower To upper) * * For i = lower To upper * * * * tempA(i, 0) = A(i) * * * * tempA(i, 1) = i + 1 * * Next i * * tempA(upper, 1) = lower * * whereAt = lower * * For i = lower To upper * * * * For j = 1 To stepSize - 1 'walk * * * * * * fromWhere = whereAt * * * * * * whereAt = tempA(whereAt, 1) * * * * Next j * * * * retA(i) = tempA(whereAt, 0) * * * * whereAt = tempA(whereAt, 1) * * * * tempA(fromWhere, 1) = whereAt 'effectively removing item just selected * * Next i * * JosephusPermutation = retA End Function Sub Test() * * Dim A As Variant, skip As Long * * Dim i As Long * * Dim retString As String * * A = Split(InputBox("Enter numbers, separated by spaces")) * * skip = InputBox("Enter step size") * * A = JosephusPermutation(A, skip) * * For i = 0 To UBound(A) * * * * retString = retString & " " & A(i) * * Next i * * retString = "Output:" & retString * * MsgBox retString End Sub Sub Test2() * * Dim A(1 To 10000) As Integer * * Dim i As Long * * For i = 1 To 10000 * * * * A(i) = i * * Next i * * Range("A1:A10000").Value = Application.WorksheetFunction.Transpose(JosephusPe rmutation(A, 179)) End Sub hth -scattered- Hide quoted text - - Show quoted text - Your problem is *using* an already written macro by array formulas. Why didn't you say so? In any event: try replacing {=everynth(20, 3)} by {=transpose(everynth(20, 3))} hth -scattered |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Programming
On Sep 18, 4:58*pm, scattered wrote:
On Sep 18, 4:32*pm, Floyd wrote: Thank You Bob and Scattered for your response but I looking for something different. *Maybe the information below is helpful. I posted this question about two year ago and received a different answer. * The answer that I received had something to do with the following; {=everynth(20, 3)} This formula represent 20 numbers (1-20) listd by every 3 numbers. The VBA is listed below: Option Explicit Function EveryNth(Num As Long, Nth As Long) Dim i As Long Dim j As Long Dim k As Long Dim x As Variant Dim y As Variant ReDim x(1 To Num) For i = 1 To Num * *x(i) = i Next i ReDim y(1 To Num) i = 0 j = 0 k = 0 Do i = i + 1 If i Num Then i = 1 If x(i) < 0 Then k = k + 1 If k = Nth Then j = j + 1 y(j) = x(i) If j = Num Then Exit Do x(i) = 0 k = 0 End If End If Loop EveryNth = y End Function This is the Macro that I want to use. *Please review, it's just not working for me. "scattered" wrote: On Sep 17, 12:18 am, Floyd wrote: What is the formula to arrange a set of numbers by a specific number. * Example, I have 1, 2, 3, 4, 5, 6, 7, 8, and 9. I would like to arrange them by (3) every third number. The answer would be: 3, 6, 9, 4, 8, 5, 2, 7, and then 1. I'm not quite sure what you mean by "formula". What you are asking about is related to the classic Josephus problem http://en.wikipedia.org/wiki/Josephus_problem(whichasks for the last number listed) and even that is a fairly hard problem - so I don't think a closed form solution which gives the full listing directly without needing to actually step through the process is possible - though I may be wrong there. Maybe you could post this in sci.math. In any event, I wrote a VBA function which takes an array and a step size and returns the array obtained by repeatedly stepping through the remaining items according to the step size. I also include two test routines (the first one to verify output, the second one a rough speed test): Function JosephusPermutation(A As Variant, stepSize As Long) As Variant * * Dim lower As Long, upper As Long * * Dim i As Long, j As Long * * Dim whereAt As Long, fromWhere As Long * * Dim tempA As Variant, retA As Variant * * lower = LBound(A) * * upper = UBound(A) * * ReDim tempA(lower To upper, 0 To 1) * * ReDim retA(lower To upper) * * For i = lower To upper * * * * tempA(i, 0) = A(i) * * * * tempA(i, 1) = i + 1 * * Next i * * tempA(upper, 1) = lower * * whereAt = lower * * For i = lower To upper * * * * For j = 1 To stepSize - 1 'walk * * * * * * fromWhere = whereAt * * * * * * whereAt = tempA(whereAt, 1) * * * * Next j * * * * retA(i) = tempA(whereAt, 0) * * * * whereAt = tempA(whereAt, 1) * * * * tempA(fromWhere, 1) = whereAt 'effectively removing item just selected * * Next i * * JosephusPermutation = retA End Function Sub Test() * * Dim A As Variant, skip As Long * * Dim i As Long * * Dim retString As String * * A = Split(InputBox("Enter numbers, separated by spaces")) * * skip = InputBox("Enter step size") * * A = JosephusPermutation(A, skip) * * For i = 0 To UBound(A) * * * * retString = retString & " " & A(i) * * Next i * * retString = "Output:" & retString * * MsgBox retString End Sub Sub Test2() * * Dim A(1 To 10000) As Integer * * Dim i As Long * * For i = 1 To 10000 * * * * A(i) = i * * Next i * * Range("A1:A10000").Value = Application.WorksheetFunction.Transpose(JosephusPe rmutation(A, 179)) End Sub hth -scattered- Hide quoted text - - Show quoted text - Your problem is *using* an already written macro by array formulas. Why didn't you say so? In any event: try replacing *{=everynth(20, 3)} by *{=transpose(everynth(20, 3))} hth -scattered- Hide quoted text - - Show quoted text - Did this not work? The macro you gave doesn't have any bugs in it that I see and yields the same output as the one I wrote. If you highlight a column range of 20 cells and enter =transpose(everynth(20,3)) in the formula bar then hit Ctrl+Shift+Enter you should get the 20 numbers you are looking for displayed in the spreadsheet. Note that *you* don't include the { and } in the array formula {=transpose(everynth(20,3)} - Excel provides them when you hit ctrl +shift+enter. Also, if you don't include the transpose function then you just get the number 3 repeated 20 times (which is what I guessed you meant by "macro not working for me"). This is because the everynth returns a horizontal array and columns contain vertical arrays. Another possible problem is that you put the macro code in a sheet (or workbook) module instead of a general code module. This is probably the case if you are seeing #NAME? displayed on the worksheet. If so, open up the visual basic editor and *Insert* a new module, then cut/ paste the everynth code from the sheet module to the new module. If none of this works - just what is happening? What do you mean by "doesn't work"? hth -scattered |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Programming
Well, it looks a bit more complicated than necessary. Using arrays probably
isn't the VERY simplest way to do it anyway, but if you want to use arrays for some reason I would do it this way: Function EveryNth(Top As Long, Nth As Long) Dim Fm(1 to Top) as Boolean 'Tracks which have been transferred Dim To(1 to Top) as Long 'Numbers in new order (output of fnc) Dim nTaken as Long 'Count of numbers tranferred to new Dim N as Long 'Temp counter of Nth ' Initialize For ii = 1 to Top Fm(ii) = True End If iTo=0 nTaken = Top N = Nth ' Loop until all numbers transferred to new list Do For iFm = 1 To Top If Fm(iFm) Then 'this number not tranferred yet N = N - 1 '...but we're transferring only every Nth If N < 1 Then iTo = iTo + 1 To(iTo) = iFm Fm(iFm) = False 'mark as transferred nTaken = nTaken - 1 'one down, nTaken to go N = Nth 'restart the temp counter of Nth End If End If Next iFm Loop While nTaken 0 'keep going until all taken End Function Only slightly shorter, but it uses one fewer counter, I think, and the counters are named for greater clarity. But if want to keep on using the one you have, I think you'll have to tell us what's "not working" about it. Is it bombing in the middle with an error (and if so what error)? pretending to work but giving incorrect results (and if so what results)? Better yet, have you tried walking through its execution one step at a time, using <F8 and the other debug tools? If you've never tried them before, they're well worth learning; a little familiarity with them might well have saved you these days of asking on the forum. If you're not sure how they work, let us know and someone (me! pick me!) will help you get started. --- "Floyd" wrote: Thank You Bob and Scattered for your response but I looking for something different. Maybe the information below is helpful. I posted this question about two year ago and received a different answer. The answer that I received had something to do with {=everynth(20, 3)}. This formula represent 20 numbers (1-20) listd by every 3 numbers. The VBA is listed below: Option Explicit Function EveryNth(Num As Long, Nth As Long) Dim i As Long Dim j As Long Dim k As Long Dim x As Variant Dim y As Variant ReDim x(1 To Num) For i = 1 To Num x(i) = i Next i ReDim y(1 To Num) i = 0 j = 0 k = 0 Do i = i + 1 If i Num Then i = 1 If x(i) < 0 Then k = k + 1 If k = Nth Then j = j + 1 y(j) = x(i) If j = Num Then Exit Do x(i) = 0 k = 0 End If End If Loop EveryNth = y End Function This is the Macro that I want to use. Please review, it's just not working for me. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Programming
Thank You Bob,
Please, help me in learning the new tools so that I can solve my problem. "Bob Bridges" wrote: Well, it looks a bit more complicated than necessary. Using arrays probably isn't the VERY simplest way to do it anyway, but if you want to use arrays for some reason I would do it this way: Function EveryNth(Top As Long, Nth As Long) Dim Fm(1 to Top) as Boolean 'Tracks which have been transferred Dim To(1 to Top) as Long 'Numbers in new order (output of fnc) Dim nTaken as Long 'Count of numbers tranferred to new Dim N as Long 'Temp counter of Nth ' Initialize For ii = 1 to Top Fm(ii) = True End If iTo=0 nTaken = Top N = Nth ' Loop until all numbers transferred to new list Do For iFm = 1 To Top If Fm(iFm) Then 'this number not tranferred yet N = N - 1 '...but we're transferring only every Nth If N < 1 Then iTo = iTo + 1 To(iTo) = iFm Fm(iFm) = False 'mark as transferred nTaken = nTaken - 1 'one down, nTaken to go N = Nth 'restart the temp counter of Nth End If End If Next iFm Loop While nTaken 0 'keep going until all taken End Function Only slightly shorter, but it uses one fewer counter, I think, and the counters are named for greater clarity. But if want to keep on using the one you have, I think you'll have to tell us what's "not working" about it. Is it bombing in the middle with an error (and if so what error)? pretending to work but giving incorrect results (and if so what results)? Better yet, have you tried walking through its execution one step at a time, using <F8 and the other debug tools? If you've never tried them before, they're well worth learning; a little familiarity with them might well have saved you these days of asking on the forum. If you're not sure how they work, let us know and someone (me! pick me!) will help you get started. --- "Floyd" wrote: Thank You Bob and Scattered for your response but I looking for something different. Maybe the information below is helpful. I posted this question about two year ago and received a different answer. The answer that I received had something to do with {=everynth(20, 3)}. This formula represent 20 numbers (1-20) listd by every 3 numbers. The VBA is listed below: Option Explicit Function EveryNth(Num As Long, Nth As Long) Dim i As Long Dim j As Long Dim k As Long Dim x As Variant Dim y As Variant ReDim x(1 To Num) For i = 1 To Num x(i) = i Next i ReDim y(1 To Num) i = 0 j = 0 k = 0 Do i = i + 1 If i Num Then i = 1 If x(i) < 0 Then k = k + 1 If k = Nth Then j = j + 1 y(j) = x(i) If j = Num Then Exit Do x(i) = 0 k = 0 End If End If Loop EveryNth = y End Function This is the Macro that I want to use. Please review, it's just not working for me. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Programming
Well, start at the beginning, though I've little doubt you've seen the first
step: You have to get to the Visual Basic Editor, where this code is typed in. You get there from your spreadsheet by selecting Tools, Macros, Visual Basic Editor. Your actual code can be in a number of places there, but actually I'm sure you must already know about this part because after all how else could you have captured the code and copied it to us? So now you're looking at your code and you want to know what's going wrong with it. I expect you inherited this code from someone else, rather than having written it yourself, but you can still figure out what's going on if you can watch the program run, step by step, seeing inside the program so to speak so you can tell what the statement it's about to execute and what is the value of each variable at any given time. And so you can: Use your arrow keys to bring the text cursor down until it's somewhere on the first line, the one that says "Function EveryNth(etc)". With your text-insertion bar somewhere on that line, hit <F9. Immediately that whole line is highlighted (probably in the shade of dark red that I think of as burgandy), and a bugandy dot appears in the margin immediately to the left. In the language of VBA you have just set a "breakpoint" in your program: When the program starts executing and gets to that point, it'll stop and invite you to look over the program, investigate current values, type immediate commands for execution, even change some of the statements, and then tell it to resume execution. It's pretty powerful stuff. You can set breakpoints on pretty much any executable statement in your program, either by using <F9 or by just clicking with your mouse in the left margin next to your line. If you hit <F9 or click again on the same line, the breakpoint will go away. Now watch what happens when you execute the program. By the way, how DO you execute the program? I mean, do you go to Tools, Macros and pick a macro from the list to execute? Do you hit a button? Or is it, in this case, a worksheet function? If the latter, the way I always get it to execute (there may be others) is just go back to the spreadsheet, to one of the cells that uses that function; there hit <F2 to start editing the formula in that cell, and then hit <Enter without changing anything. Immediately, as I recall, the view will switch from the spreadsheet to the VBE, with the breakpointed line now highlighted in yellow, indicating that this statement will be next to execute when you allow it. Since you put the breakpoint on the Sub line, it hasn't done a blessed thing yet. That means you're looking at in before it's done anything wrong, but that in turn means you haven't seen anything useful yet. Here's what you do: Hit <F8. <F8 allows the program to execute exactly one command or statement, in this case the Function "command" itself. That doesn't do anything very interesting, but if you hit <F8 while you're looking at the VBE you'll see the yellow line jump to the next command, which is not, as you might, expect the next line ("Dim i As Long") but ... well, actually, I'm not sure whether it'll be the ReDim or the For statement. At any rate it doesn't consider Dim to be an executable statement so it doesn't show you anything for those, just leaps over them - in fact if you try to set a breakpoint on a Dim statement, nothing happens because breakpoints aren't allowed there. At any rate, now the yellow marker has advanced one, and something interesting has happened. Hover your mouse cursor over any variable name in your code: Num, Nth, i, whatever. What you should see -- and if you don't, something's wrong -- is a pop-up message saying, for example, "Num = 30". Most of the other variables will say simply "i = Empty", because you haven't assigned them any values yet -- or come to think of it maybe they'll be set to 0, because of the Dim statement. But Num and Nth you'll be able to see once you've come out of the introductory Function statement itself. Each time you hit <F8 it'll advance another step and you can see the results. You can advance one step at a time through the entire execution, if you like, which will give you a clear picture of everything it's doing. But if that get's boring and you want to jump ahead, you can do either of two things: 1) Hit <F5; this just runs along and finished the execution without you. But if you still need to see what it's doing later -- just not right in this part of the program -- you can 2) Set another breakpoint, with the mouse or using <F9, and THEN hit <F5; the program will run along without you, just as before, but as soon as it gets to this new breakpoint it'll stop and give you a chance to catch up again. There are other and fancier things your can do. Look through the Debug menu for some of them, and in your Help file(s). But one thing for sure you'll need to do in this case, because you have arrays: You can't hover your mouse over x and y to get their value because they don't have "a value", they have more like 30, being arrays. To see what's going on inside them you have to add them to a "watch" window. To do that, either bring down the Debug menu or right-click on the variable, and in either resulting drop-down menu select Add Watch. A little window will pop up allowing you to set some options; for now, just make sure the "Expression" is equal to the variable you want to inspect and hit OK or <Enter. In the Watch window -- it should be just below your code window -- an entry for that variable shows up. If it's a scalar variable you can see its present value to the right. If it's an object or an array, you have to click on the + symbol to expand it into its component parts. Either way you get a complete picture of what's happening in this variable or object. The Watch window in no way interferes with your ability to continue executing the program step by step or many at a time. These are the "tools" I had in mind; you can see what your program is doing, and if you know what it SHOULD be doing then you should be able to see at what point it goes wrong, and figure out why. There's more, of course, but this is most of what I need for most debugging. --- "Floyd" wrote: Thank You Bob, Please, help me in learning the new tools so that I can solve my problem. --- "Bob Bridges" wrote: ....But if you want to keep on using the one you have, I think you'll have to tell us what's "not working" about it. Is it bombing in the middle with an error (and if so what error)? pretending to work but giving incorrect results (and if so what results)? Better yet, have you tried walking through its execution one step at a time, using <F8 and the other debug tools? If you've never tried them before, they're well worth learning; a little familiarity with them might well have saved you these days of asking on the forum. If you're not sure how they work, let us know and someone (me! pick me!) will help you get started. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Vba Programming | Excel Programming | |||
Excel Programming Help | Excel Programming | |||
Excel programming | Excel Programming | |||
Excel programming using VBA | Excel Programming | |||
Excel Programming in VB | Excel Programming |