Type Mis-Match on Variant - Excel 2007 VBA

Hello Experts,

I am working on a transaction/traffic conversion rate. I've written VBA funtions which gather the traffic count and transaction count. Each function works fine individually, but when I try to divide one by the other, I recieve an Run-time error'13': type mismatch .

Here are the main pieces of code:

    Dim TrafficCount As Variant

    TrafficCount = TrafficOut(BegDate, EndDate)
    Range(Cell).Value = TransactionCount(BegDate, EndDate) / TrafficCount    
    ...

Function TrafficOut(BegDate1, EndDate1) As Variant
...

Function TransactionCount(BegDate1, EndDate1) As Variant
....

Thanks
bikeskiAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
bikeskiConnect With a Mentor Author Commented:
BFN, your question prompted me to revisit the previous solution. I just needed to add (0,0) to my function call return.

    TrafficOut = rs.GetRows(-1, 1, "TrafficOuts")(0, 0)


Thanks:)
0
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

Why are you using Variant data types for TrafficCount, & the two functions?

If you define these as Long (or Double) data types, does this result in a different error (or do you see the required outcome)?

BFN,

fp.
0
 
bikeskiAuthor Commented:
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
Martin LissConnect With a Mentor Older than dirtCommented:
Something else is going on because this works.
Sub test()

Range("A1").Value = TrafficOut(2) / TransactionCount(2)

End Sub
Function TrafficOut(x) As Variant
TrafficOut = x * 25
End Function
Function TransactionCount(x) As Variant
TransactionCount = x * 5
End Function

Open in new window

Let me also add to what fanpages said. Variants should only be used when absolutely necessary because they are larger and slower than any other variant type, so not only should your functions return appropriate data types but the parameters passed to the functions should also be given .
0
 
[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:
You're welcome (I think) :)
0
 
bikeskiAuthor Commented:
MartinLiss and Fanpages, thanks for your suggestions. I'll have to revisit the Variants usage some other day.
0
All Courses

From novice to tech pro — start learning today.