• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • 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 LissOlder 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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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