Optional argument as Variant not acting like number


M

mlthornton

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)
 
Ad

Advertisements

J

joeu2004

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.


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.


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 -----
 
M

mlthornton

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!
 
J

joeu2004

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.


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.


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.
 
Ad

Advertisements

M

mlthornton

Yes! Very helpful. Obviously some rookie coding so I greatly appreciate this.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top