[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Type Mis-Match on Variant - Excel 2007 VBA

Posted on 2013-05-15
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39169682

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

ID: 39169853
LVL 49

Assisted Solution

by:Martin Liss
Martin Liss earned 300 total points
ID: 39169861
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 .
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Accepted Solution

bikeski earned 0 total points
ID: 39169867
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 300 total points
ID: 39169874
You're welcome (I think) :)

Author Closing Comment

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

656 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