vba "Type Mismatch"

[Fixador]

A 'sheet' function , trying to call from within vba code

Hi All,

I have gotton into a bizarre mess - it shouldnt have happened - but it has, Its not supposed to happen - but it has .............

This below is a custom function , which i have been calling from within a sheet , for ages - and never any problem ! However , calling from within VBA ........ I am doing something wrong

Anyone ??? please .................................. my code ( a trival , below )

Code: Select all

Option Explicit

Const K_IN_BOUNDS = 1
Const K_WIN = "W"
Const K_LOSS = "L"
Const K_ERROR = "E"

Public Function AvgWinOdds(ByVal rngOutcome As Range, ByVal rngInBounds As Range, ByVal rngOdds As Range, ByVal nMinRequired As Integer, Optional ByVal nMaxRequired As Integer = 10, Optional ByVal nRowTop As Integer = 6) As Double
'
' Returns the current Strike Rate as a decimal number (eg 0.4 = 40% winners)
' -1 returned if strike rate could not be calculated.
'
' rngOutcome = cell containing most recent Outcome to be used.
' rngInBounds = cell containing most recent indicator for whether row in bounds or not (1 or 0).
' rngOdds = cell containing Winner's odds.
' nMinRequired = minimum number of qualifying races to use in calculation
' nMaxRequired = maximum number of qualifying races to use in calculation (optional, default = 10)
' nRowTop = top race row (optional, default = 6)
'
AvgWinOdds = -1

Dim iRowOutcome%: iRowOutcome = rngOutcome.Row
If nMinRequired > iRowOutcome - nRowTop + 1 Then Exit Function ' Insufficient race data available for calc.

Dim i%, j%, nW%, nL%, tW#
Const K_WIN = "W"

j = 0
For i = iRowOutcome To nRowTop Step -1

If rngInBounds.Offset(j).Value = K_IN_BOUNDS Then

If rngOutcome.Offset(j).Value = K_WIN Then
nW = nW + 1
tW = tW + rngOdds.Offset(j).Value
End If

End If

If nMaxRequired <= nW Then Exit For ' sufficient data collected already.


j = j - 1
Next

If nMinRequired > nW Then Exit Function ' Insufficient data available for calc.

AvgWinOdds = tW / (100# * CDbl(nW))

End Function


And a trivial code to test this is....

Code: Select all

Private Sub CommandButton1_Click()
Dim outcome As String, InRange As Integer, Odds As Single, Nmin As Integer, Nmax As Integer

For i = 4 To 13 Step 1
outcome = Cells(i, 1)
InRange = Cells(i, 2)
Odds = Cells(i, 3)
Nmin = Cells(i, 5)
Nmax = Cells(i, 5)
Cells(i, 8) = AvgWinOdds(outcome, InRange, Odds, Nmin, Nmax, 4)

Next i

End Sub


On execute, it highlights the first variable inside the function , and a popup box says "Type Mismatch "

[PeteB]

outcome = Cells(i, 1).Value

[PeteB]

Sorry too hasty! that line is fine

The Dim is wrong - should be
Dim outcome As Range

[Fixador]

Hello PeteB

I have carried on messing around with it , and got

Code: Select all

Private Sub CommandButton1_Click()


For i = 4 To 13 Step 1
   
    'ByVal removed
    Cells(i, 9) = AvgWinOddsX(Cells(i, 1), Cells(i, 2), Cells(i, 3), Cells(i, 5).Value, Cells(i, 6).Value, 4)
    Cells(i, 10) = AvgWinOddsX(Cells(i, 1), Cells(i, 2), Cells(i, 3), Cells(i, 5), Cells(i, 6), 4)
   
    'with ByVal
    Cells(i, 8) = AvgWinOdds(Cells(i, 1), Cells(i, 2), Cells(i, 3), Cells(i, 4).Value, Cells(i, 5).Value, 4)
Next i

End Sub


see, i could only get a tune out of it , by removing the Dim statement completely !

This is sloppy ........ and unreadable

As an aside : Both of these produce identical results WHICH ARE CORRECT , BUT, they differ from that from that of the sheet function - bizarre !

[Fixador]

Tried your method, had to keep changing to "as range" for each input variable - as kept getting "Type Mismatch"

so then got:-

Code: Select all

Dim outcome As Range, InRange As Range, Odds As Range, Nmin As Integer, Nmax As Integer


but then got a weird warning:-

Runtime error 91
Object variable or With block variable not set

which on debug pointed to:-

Code: Select all

iRowOutcome = rngOutcome.Row


which , if but mouse over either of these 2 variables, returned the warning above

[PeteB]

Yes I was only pointing out the first error - sorry - I figured you'd work out the rest from there!

So yes you need to Dim everything in the test function to be the type that is expected by the AvgWinOdds function - your new Dim is correct for this

(It works without a Dim, because then the variables default to type Variant - but you would be better putting Option Explicit at the top of your test code too, and having the Dim statement - makes it harder for sneaky errors to creep in)

Next problem is one of those annoying reasons I hate VBA - to assign an object you need to use Set, but it doesn't tell you this at compile time!!
You probably also want to say whihc sheet the cells are on - so this should do it (change the shaeet name from "Sheet1" if your test sheet is called something else!)

Code: Select all

Option Explicit

Private Sub CommandButton1_Click()
Dim outcome As Range, InRange As Range, Odds As Range, Nmin As Integer, Nmax As Integer
Dim i As Integer

For i = 4 To 13 Step 1
With Worksheets("Sheet1")
Set outcome = Cells(i, 1)
Set InRange = Cells(i, 2)
Set Odds = Cells(i, 3)
Nmin = Cells(i, 5)
Nmax = Cells(i, 5)
Cells(i, 8) = AvgWinOdds(outcome, InRange, Odds, Nmin, Nmax, 4)
End With

Next i

End Sub


[Fixador]

Hi PeteB,

It is unlike me to leave out Option Explicit ! - actually , its in the module with function ......... can you confirm , that i need it in each of the sheets as well ?

PeteB - my VB is from the 1970's so the twists in vba are a surpise to me - ie, "With End With" and "Set" , so could i leave out "With WorkSheets("Sheet1)" , and instead insert:-

outcome = WorkSheets("Sheet1).Cells(i, 1)

does this make sense ?

Agh ! no this doesnt work "Object not supported this property or method"

Interesting yr last fix produced results that are diffent from ( and incorrect ) that produced calling the function from within the sheet , AND ,different from the occassion when "ByVal" was removed in Function header( produced correct answer ) , and yr fix produces same answer when ByVal was retained - Ouch !

Is there some kind of 'rounding up' implicit in yr fix ?

Cripes - I am going to have to Dim as varient in order to get the correct numeric values

PeteB - i am going to have to go through this all carefully again - I havent the time this morning
cheers

[Fixador]

Ouch - time for a valium !

To recap : There ARE TWO FAULTS HERE

(1) My method of calling the function

(2) The function returns incorrect values - WHEN called correctly ?


Re (2) : Remeber , i have discovered here , that when the function is called from within a cell , ie =AvgWinOdds(........) it returns the wrong values.

Now, with your last fix for calling the function:-

Code: Select all

Option Explicit

Private Sub CommandButton1_Click()
Dim outcome As Range, InRange As Range, Odds As Range, Nmin As Integer, Nmax As Integer
Dim i As Integer

For i = 4 To 13 Step 1
With Worksheets("Sheet1")
Set outcome = Cells(i, 1)
Set InRange = Cells(i, 2)
Set Odds = Cells(i, 3)
Nmin = Cells(i, 5)
Nmax = Cells(i, 5)
Cells(i, 8) = AvgWinOdds(outcome, InRange, Odds, Nmin, Nmax, 4)
End With

Next i

End Sub



The method of calling is impeccable ! but the returned values are incorrect.

PeteB :Could you have a look at the contents of the function ?

cheers

Paul

[PeteB]

Paul,

Option Explicit - yes it only applies to the piece of code it is at the top of, so you need it everywhere for full protection

Yes With / End With works as you suspect - so

Code: Select all

With Worksheets("Sheet1")
Set outcome = Cells(i, 1)
End With


is the same as

Code: Select all

Set outcome = Worksheets("Sheet1").Cells(i, 1)


Note that you still need Set whenever you are assigning to an object (you may also have had a problem with a missing quote after Sheet1?)

I'm not sure why you are getting different values when you run the code in different ways.
How I would proceed is to put a breakpoint on the first code statement within the AvgWinOdds function - i.e. put a breakpoint on line

Code: Select all

AvgWinOdds = -1


And then use the Watch window, or just hover the mouse over the various inputs to the function, and check they are what your expect.

One thing I notice - in the test function you have

Code: Select all

Nmin = Cells(i, 5)
Nmax = Cells(i, 5)


Should that maybe be

Code: Select all

Nmin = Cells(i, 4)
Nmax = Cells(i, 5)


??

If that's not it, I'm happy to have a look at the code - let me know.

Pete.

[PeteB]

Just seen as well that you say it gives incorrect values when called from a cell - do you have a version of the code which gives correct values when called from a cell?? If so, I would go back to that, and then only make changes to the test code

[Fixador]

Re Option Explicit - thanks didnt know that

Oh blast , dash , dot dash dash dash dot ..........

Everything Works when - and the RIGHT VALUES drop out WHEN

Code: Select all

Nmin = Cells(i, 5)
Nmax = Cells(i, 6)


In the code

Code: Select all

Option Explicit
Private Sub CommandButton3_Click()


Dim outcome As Range, InRange As Range, Odds As Range, Nmin As Integer, Nmax As Integer
Dim i As Integer

For i = 4 To 13 Step 1
With Worksheets("Sheet1")
    Set outcome = Cells(i, 1)
    Set InRange = Cells(i, 2)
    Set Odds = Cells(i, 3)
    Nmin = Cells(i, 5)
    Nmax = Cells(i, 6)     'note change here - owwww !
    Cells(i, 12) = AvgWinOdds(outcome, InRange, Odds, Nmin, Nmax, 4)
End With

Next i

End Sub


And now - in a code writing manner that i am familar with:

This too produces the right numbers as well :-

Code: Select all

Private Sub CommandButton4_Click()
Dim outcome As Range, InRange As Range, Odds As Range, Nmin As Integer, Nmax As Integer
Dim i As Integer

For i = 4 To 13 Step 1

Set outcome = Worksheets("Sheet1").Cells(i, 1)
Set InRange = Worksheets("Sheet1").Cells(i, 2)
Set Odds = Worksheets("Sheet1").Cells(i, 3)
 Nmin = Worksheets("Sheet1").Cells(i, 5)
 Nmax = Worksheets("Sheet1").Cells(i, 6)
Cells(i, 13) = AvgWinOdds(outcome, InRange, Odds, Nmin, Nmax, 4)


Next i
End Sub


But the function called from the sheet - STILL returns the wrong values until it does Line 13 ! ( and yes i have checked the inputs ! )

But too conclude : I dont care , because my objective is too call the function from within vba - WHICH I CAN NOW !

Thanks PeteB , i got into a right muddle there !

Must read up on Set and With/End With

[Mitch]

Hi,

My head's not clear enough to study the code in detail, and I know this is an old thread now so you probably got it figured out anyway, but just in case you didn't or anyone else reads this looking for answers...

When you use With....End With, you need to put a "." in front of everything that you want the range after With to apply to.

For example, instead of

Code: Select all

With Worksheets("Sheet1")
    Set outcome = Cells(i, 1)
    Set InRange = Cells(i, 2)
    Set Odds = Cells(i, 3)
    Nmin = Cells(i, 5)
    Nmax = Cells(i, 6)     'note change here - owwww !
    Cells(i, 12) = AvgWinOdds(outcome, InRange, Odds, Nmin, Nmax, 4)
End With

...you'd have

Code: Select all

With Worksheets("Sheet1")
    Set outcome = .Cells(i, 1)
    Set InRange = .Cells(i, 2)
    Set Odds = .Cells(i, 3)
    Nmin = .Cells(i, 5)
    Nmax = .Cells(i, 6)     'note change here - owwww !
    .Cells(i, 12) = AvgWinOdds(outcome, InRange, Odds, Nmin, Nmax, 4)
End With

That would explain why your call to the function returned the wrong answer, if it were sending values from the sheet that the code was written in, instead of the sheet called "Sheet1".