Type Mis-Match on Variant - Excel 2007 VBA

Posted on 2013-05-15
Last Modified: 2013-05-20
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

Question by:bikeski
  • 3
  • 2
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility

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



Author Comment

Comment Utility
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 100 total points
Comment Utility
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 .
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.


Accepted Solution

bikeski earned 0 total points
Comment Utility
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)

LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 100 total points
Comment Utility
You're welcome (I think) :)

Author Closing Comment

Comment Utility
MartinLiss and Fanpages, thanks for your suggestions. I'll have to revisit the Variants usage some other day.

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now