Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional argument as Variant not acting like number
I'm using an Optional Argument as Variant in a code, but the code doesn't seem to be treating the argument as a number. Do I need to re-declare it as integer? or coerce it into number? here's the code that is resulting in error:
In a sheet with week number "1x3" for example, column A contains product names, column B contains codes. The codes contain the shift number in the 4th position (1, 2, or 3). I want to count how many times the codes contain the shift number requested by the user. From what I can figure, it seems the code isn't treating the digit pulled out of the code as a number. Little help? Function TotalCC(Week As String, Optional ShiftNum As Variant) As Integer 'Application.Volatile 'item ranges based on sheet name Set ProductRange = Sheets(WeekNum).Range("A1:A100") 'initialize CountVals = 0 'check for shiftnum argument If IsMissing(ShiftNum) Then 'do some stuff - in this case, the shift argument is present ElseIf Not IsMissing(ShiftNum) Then For Each ProductCell In ProductRange If Mid(ProductCell.Offset(0, 1).Value, 4, 1) * 1 = ShiftNum * 1 Then On Error Resume Next CountVals = CountVals + 1 ElseIf Len(ProductCell.Offset(0, 9).Value) < 4 Then CountVals = CountVals + (1 / 3) End If Next End If TotalCC = CountVals End Function Here's how I enter the function in the worksheet: =TotalCC("1x2",3) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional argument as Variant not acting like number
wrote:
I'm using an Optional Argument as Variant in a code, but the code doesn't seem to be treating the argument as a number. Do I need to re-declare it as integer? No. In fact, if you did, your code would not work as intended at all. If IsMissing(ShiftNum) works as intended only if ShiftNum is type Variant. Read the help page for IsMissing. wrote: or coerce it into number? No, if your example is correct. Since ShiftNum is type Variant, it certainly is possible to pass a numeric-looking string like "3" instead of an actual number like 3. If you made that mistake, there are contexts in which VBA treats ShiftNum as a string instead of a number, for example a conditional expression (comparison). If that were possible, it might be prudent to coerce ShiftNum to a number. But first, aha!, you do that already. You wrote ShiftNum*1. That coerces a numeric string to a number. And second, in your example, you pass 3, not "3", in the function call. So no coercion was needed. wrote: Little help? First, you offer no evidence that your interpretation of the problem is correct. Try adding the following statement in the beginning to demonstrate that ShiftNum is indeed treat as a number: If Not IsMissing(ShiftNum) Then If TypeName(ShiftNum) < "Double" Then Stop End If Yes, "Double". All numbers in Excel are type Double. If all is well, VBA should not execute Stop. Second, I am curious about why you execute On Error Resume Next. In any case, once it is executed the first time in the loop, that error handling will remain effect. It will mask any other errors that might arise later. Consequently, for example, Mid(ProductCell.Offset(0,1).Value,4,1)*1 might fail on a subsequent loop because Mid(ProductCell.Offset(0,1).Value,4,1) is not numeric. If that happens, instead of raising a runtime error, VBA will execute the next statement, namely CountVals = CountVals + 1. Would that explain the misbehavior that leads you to conclude (incorrectly) that ShiftNum is not be treated as a number? I cannot say, since you never really explained how your function is misbehaving, in the first place. Hope this helps. Good luck! ------ PS.... Totally unrelated to any problem you might have, the following can be simplified. Change If IsMissing(ShiftNum) Then ElseIf Not IsMissing(ShiftNum) Then to simply If IsMissing(ShiftNum) Then Else ----- original message ----- wrote: I'm using an Optional Argument as Variant in a code, but the code doesn't seem to be treating the argument as a number. Do I need to re-declare it as integer? or coerce it into number? here's the code that is resulting in error: In a sheet with week number "1x3" for example, column A contains product names, column B contains codes. The codes contain the shift number in the 4th position (1, 2, or 3). I want to count how many times the codes contain the shift number requested by the user. From what I can figure, it seems the code isn't treating the digit pulled out of the code as a number. Little help? Function TotalCC(Week As String, Optional ShiftNum As Variant) As Integer 'Application.Volatile 'item ranges based on sheet name Set ProductRange = Sheets(WeekNum).Range("A1:A100") 'initialize CountVals = 0 'check for shiftnum argument If IsMissing(ShiftNum) Then 'do some stuff - in this case, the shift argument is present ElseIf Not IsMissing(ShiftNum) Then For Each ProductCell In ProductRange If Mid(ProductCell.Offset(0, 1).Value, 4, 1) * 1 = ShiftNum * 1 Then On Error Resume Next CountVals = CountVals + 1 ElseIf Len(ProductCell.Offset(0, 9).Value) < 4 Then CountVals = CountVals + (1 / 3) End If Next End If TotalCC = CountVals End Function Here's how I enter the function in the worksheet: =TotalCC("1x2",3) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional argument as Variant not acting like number
Very helpful.
I removed the "On Error Resume Next" statement as it was apparently masking the mistakes. Where I believe the code is failing is the line: "If Mid(ProductCell.Offset(0, 1).Value, 4, 1) * 1 = ShiftNum * 1 Then" The Mid statement (with or without the " * 1 "), never equals ShiftNum. Even substituting ShiftNum with a number (1, 2, or 3) that I knew 'should' yield True in some cases, still failed to advance past that statement. So I think my question is: is there a better method than Mid to find the nth number in a string of numbers and return that value as a number? Thanks for the very helpful response! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional argument as Variant not acting like number
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional argument as Variant not acting like number
wrote:
Where I believe the code is failing is the line: "If Mid(ProductCell.Offset(0, 1).Value, 4, 1) * 1 = ShiftNum * 1 Then" Why do you only "believe" that? How is your function failing? You can determine exactly where VBA is failing by: 1. Select the first executable line and press F9 to set a breakpoint. Alternatively, add a Stop statement as the first executable statement. 2. Once stopped, repeatedly press F8 to single-step through the function. Your original posting included a typo that I overlooked as, well, just a posting typo. But perhaps it is real, to wit: Function TotalCC(Week As String, Optional ShiftNum As Variant) As Integer Set ProductRange = Sheets(WeekNum).Range("A1:A100") If your function is failing with a #VALUE error, the reason might that either Week should be WeekNum in the Function statement, or WeekNum should be Week in the Set statement. wrote: The Mid statement (with or without the " * 1 "), never equals ShiftNum. Even substituting ShiftNum with a number (1, 2, or 3) that I knew 'should' yield True in some cases, still failed to advance past that statement. Did you determine that by single-stepping through the function? If so.... I cannot tell you why Mid(ProductCell.Offset(0,1).Value,4,1) is not the value that you expect. Try adding the following statement to be sure things are what you expect: Debug.Print Mid(ProductCell.Offset(0,1).Address(external:=True ), _ Len(ProductCell.Offset(0,1)), _ Chr(34) & ProductCell.Offset(0,1) & Chr(34) You can see Debug.Print output in the Immediate Window by pressing ctrl+G. wrote: is there a better method than Mid to find the nth number in a string of numbers and return that value as a number? No. And I thought you want to return a count, not "that value" (the nth number). Moreover, your original function did something else as well, something I do not understand at all. In any case, the following is how I would design your original function. It might help in the long run. See the important "Notes" following the VBA code. Option Explicit Function TotalCC(productRange As Range, _ Optional ShiftNum As String = "1") As Long Dim countVals As Long Dim countFrac As Long Dim productCell As Range countVals = 0 countFrac = 0 For Each productCell In productRange If Mid(productCell.Offset(0, 1), 4, 1) = ShiftNum Then countVals = countVals + 1 ElseIf Len(productCell.Offset(0, 9)) < 4 Then countFrac = countFrac + 1 End If Next TotalCC = WorksheetFunction.Round(countVals + countFrac / 3, 0) End Function Usage: =totalCC('1x2'!$A$1:$A$100,3) Notes: 1. Option Explicit Forces to declare the type of every variable. A "good practice" to catch typos like Week v. WeekNum early on. 2. productRange As Range By passing the range, you obviate the need for Application.Volatile. It is also more flexible if the location or size of the range A1:A100 might change. Application.Volatile is "evil". It is necessary in some circumstances. But it should be avoid whenever possible. Using a "volatile" function causes all cells that reference it and their dependents, direct and indirect, to be recalculated every time Excel does __any__ recalculation. That can become time-consuming. 3. Optional shiftNum As String = "1" If a parameter is optional, I feel there should be a useful default. Your original implementation return zero if the parameter were missing. Not useful. I use type String to make the later comparison more reliable; that is, to avoid multiplying Mid(...) by 1, which fails if Mid(...) is not numeric. Note the even though shiftNum is type String, we can pass a number, as demonstrated by the usage example above. So it does not encumber usage. But it also allows for non-numeric shifts, e.g. "G" for graveyard. 4. Function totalCC(...) As Long The advantage of type Long is flexibility. Type Integer is limited to 32767, too small if the size of productRange ever exceeds that number. There is no reason to use type Integer today, except in rare circumstances of very large arrays, where size matters. In particular, there is no longer any performance benefit of Integer v. Long in modern computers. 5. countFrac = countFrac + 1 [....] totalCC = ... countVals + countFrac / 3 Your original implementation has: CountVals = CountVals + (1 / 3) I don't know why. But it is unreliable to add 1/3 iteratively. It is more reliable to count the number of times (n) we would add 1/3, then add n/3 at the end. For example: Sub testit1() Dim x As Variant, i As Long x = 1 For i = 1 To 3: x = x + 1 / 3: Next MsgBox (x = 2) End Sub displays False(!) even though we add 1/3 three times. The reason is complicated; it has to do with non-integer representation and arithmetic. The problem is compounded by using type Long for countVals, as I do. In my case, adding 1/3 and storing into a non-Double variable each iteration is the same as adding zero(!). (You used implicit type Variant. So VBA will coerce countVals to type Double after adding 1/3.) 6. TotalCC = WorksheetFunction.Round(...) Again, I do not know why you add 1/3 sometimes. But by doing so in your original implementation, CountVals (type Variant) might be a non-integer. So when you do TotalCC = CountVals, VBA implicitly rounds CountVals, since TotalCC is type Integer. If that is your intent, it is arguably better to use WorksheetFunction.Round. The issue is: VBA Round(2.5) is not the same as Excel ROUND(2.5,0). If you want Excel rounding in VBA, as most people do, use WorksheetFunction.Round. Alternatively, you might have intended: totalCC = Int(countVals + countFrac/3) or totalCC = WorksheetFunction.RoundUp(countVals + countFrac/3, 0) An aside, no longer relevant.... 7. If IsMissing(ShiftNum) Then Else Previously, I suggested that alternative because I assumed there is some code between the If-Then and Else statements in your full implementation, omitted from the posted code snippet. If not, of course you should simply write: If Not IsMissing(ShiftNum) Then followed by everything you put after the Else statement. There is no need for an Else statement. Whew! Hope all that helps. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optional argument as Variant not acting like number
Yes! Very helpful. Obviously some rookie coding so I greatly appreciate this.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Argument not optional | Excel Programming | |||
Optional Variant | Excel Programming | |||
Argument not optional Error 449! Need Help | Excel Programming | |||
Don't understand why I'm getting an argument not optional | Excel Programming | |||
optional argument in a function | Excel Programming |