[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

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
0
bikeski
Asked:
bikeski
  • 3
  • 2
3 Solutions
 
[ 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
 
Martin LissRetired ProgrammerCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bikeskiAuthor 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:
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now