variant data type dependencies

Question

I am using variant data type in my small VBA app to use both string and integer data's under same function. However, only my Integer calculations are seem to work and the string related parts just gives #value! error.

In my app, I am using both strings and integer under same function defining the single variable as variant. Taken steps to specify string within "" as well.

Could anyone please advice me if i need to differentiate in any other means between string and int when i use variant data type ?

i am sharing my app here for reference as i am unable to copy the piece of code due to lot of dependencies. (Sheet: Forecast) VBA_App

You don't need to do anything special (same thing than when defining them as Integer or String). Logically, you cannot treat a variant with String content as Integer and vice versa; this is most likely what is provoking your error. The whole spreadsheet as a reference is certainly helpful, but it is pretty big; can you please be more precise on the exact variables/conditions where you get the errors? — varocarbas, Sep 22 '13 at 11:06
@varocarbas Issue is with the variable "fcOption" under Forecast sheet/module. — Fenixs, Sep 22 '13 at 13:00
It is unclear what this function does and why its calculations should be wrong. If you perform the number-related actions just with numbers and the string ones with strings you shouldn't have any problem; but from this code is not clear the kind of inputs the fcOption gets in each scenario. If you want further (and accurate) help you would have to tell the specific conditions (e.g., when I call this function with bettype = "" and fcOption = "", I get "" and I would like to get ""). — varocarbas, Sep 22 '13 at 13:11

Dima · Answer 1 · 2013-09-22T15:51:04.810

2

If I properly understood, you pass a numerical value to function in 2 formats - integer and string. Of course, you can use variant and before using this value, check it:

IsNumeric(yourVariant) gives you True or False:
IsNumeric("222") - True
IsNumeric(222) - True
IsNumeric("222abc") - False

After that to be sure you can convert your Variant to Integer(http://msdn.microsoft.com/en-us/library/s2dy91zy.aspx): CInt(yourVariant)

if IsNumeric(yourVariant) then
    someIntVariable = CInt(yourVariant)
else
    MsgBox "bla bla bla"
EndIf

I looked some your code, you can use Select Case Statement (http://msdn.microsoft.com/en-us/library/cy37t14y.aspx) or keep If-Else-If. Discussion in comments helps you choose.

if my answer is incorrect, please give a more specific piece of your code. I found it hard to understand what is going on your application.

For example I try analyze code step by step running function fcComm from cell E68, sheet Forecast. reaching function setFcResult we have next:

Function setFcResult(bettype As String, fcOption)
' bettype = "FT.OU"
' fcOption = "HF.HL"
setFcResult = True

bettype = UCase(bettype)
fcOption = UCase(fcOption)

If bettype = "FT.OU" Then
    If fcOption >= 0 Then  ' you compare "HF.HL" with 0. It returns true. You can verify this by yourself.
        hfs = 5
        afs = fcOption - 5  ' here you perform "HF.HL" - 5. It returns error and function terminates.
    Else 
        setFcResult = False
    End If
ElseIf bettype = "HT.OU" Then
...

Table Odds looks like:

enter image description here

Bet Type search:

bettype = Application.WorksheetFunction.VLookup(oddsId, odds.DataBodyRange, 3, False)

oddsId - 13
odds.DataBodyRange $A$3:$BT$47
bettype = 13

You search by oddsId, VLookup searches oddsId in first column of odds.DataBodyRange - it's column A, but in column A you have TransId.

enter image description here

So you have incorrect bettype for your fcOption variable.

edited Sep 22 '13 at 15:51

answered Sep 22 '13 at 11:12

Dima

514
2
7
18

Why is exactly an (endless) Select Case better than an (endless) set of conditions? Bear in mind that in SO (and, ideally, in life too) when you claim something you have to given some kind of reason supporting it ("it's better" does not sound as a valid reason; mainly when some people, like myself, think that this is not the case). – varocarbas Sep 22 '13 at 11:31
1

http://www.blackwasp.co.uk/SpeedTestIfElseSwitch.aspx – Dima Sep 22 '13 at 11:38
1

1. select case faster. 2. select case easier to understand what's going on in the case of large chains. – Dima Sep 22 '13 at 11:39
I said, you can. I didn't say you must or should. So, it's advice and no more. – Dima Sep 22 '13 at 11:45
... I prefer conditions because allow you to do anything you want and change its structure as much as you wish. Select Case is a much more rigid structure and thus, IMHO, something which sholdn't be recommended as a solution (I insist in this point: I am not saying that you shouldn't use Select Case; I am saying that you shouldn't recommend to someone with lots of conditions, just converting those into Select Case). – varocarbas Sep 22 '13 at 11:54
1

"I said, you can. I didn't say you must or should" -> you are here to advise. The reason for my comment was considering that your advice wasn't too right; not only because of thinking that this recommendation shouldn't be given under these conditions, but because of the lack of justification. Now it is much better (this is an open discussion; the OP or any future reader can agree with my opinion or with yours): next time, better try to support all your statements with reasons (not necessarily references, but reasons: for example "it is quicker"). – varocarbas Sep 22 '13 at 11:56
1

Ok. Thank you, varocarbas. You are right, I must have confirmation if I give advice. I corrected my answer and did not make any claims. Give food for thought :) – Dima Sep 22 '13 at 12:00
No problem (all we are here to learn). I will delete all my comments above and write down a small summary to let (my) ideas clear. – varocarbas Sep 22 '13 at 12:03
Thanks for the clarifications. Regarding your link, I am afraid that it is not too relevant (actually, not relevant at all): 1 million of iterations is a too big scenario (for conditions or Case Select); the testing conditions are not the best ones (.NET, instead of VBA; quite a few time ago with close-to-outdated versions (hardware, Windows and VS); not a big difference (0.1/5 seconds best/worst scenario). If the OP has an endless anything, the best advice (IMO) is converting it into a better approach (e.g., a loop), not recommending a migration to something more or less equivalent. – varocarbas Sep 22 '13 at 12:07
... you might do your own testing under better conditions and write down your conclusions here, if you wish (it would be a more solid support to your claim). In any case, for "smaller situations", I personally don't like Select Case because of being a more rigid structure than what a set of conditions is. – varocarbas Sep 22 '13 at 12:10
@stepler I jus tried to highlight the piece of code where i hit the problem. Attached specific function in seperate .bas file in the same dropbox location. Thanks again for your help. – Fenixs Sep 22 '13 at 12:15
I have +1ed this answer because the attitude you showed (wanting to learn/improve) + having a somehow-useful discussion which perhaps someone might find interesting. But I don't think that you are providing a valuable help to the OP. You should stop talking generally and understand properly the exact problems before continue suggesting things which are not useful. Before proposing things you should understand the exact conditions (and what your proposals provoke); first thing to know are the expected inputs. Please, ask/understand before intending to solve. – varocarbas Sep 22 '13 at 13:17
okay. Let me point out things clearly. For example, bettype = FT.HDP always use the fcOption as Integer and the bettype = "DC" always uses fcOption as String. ElseIf bettype = "FT.HDP" Then If fcOption < 1 Then afs = 500 hfs = 500 + fcOption ElseIf bettype = "DC" Then If fcOption = "1" Then hfs = 50 afs = 30 I have therefore, separated the data type as 1 and "1" for int and string. – Fenixs Sep 22 '13 at 13:25
varocarbas, I'm in a hurry to solve the problem, it's true :) But at least I found a place where function terminates with error. – Dima Sep 22 '13 at 13:28
user159087, please, look at last part of my answer. I edit it and add some comments to piece of code. – Dima Sep 22 '13 at 13:57
@stepler ... fcOption gets the input from Forecast sheet cell (formula) and its a constant. For example cell f27 contains (=fcComm(A27,B27,C27,D27, 11)) in which 11 is the fcOption. I am comparing this 11 in the piece of code you just edited. Another example is G68 which has (=fcComm(A68,B68,C68,D68, "HF.AL") here fcOption is the string "HF.AL" and I am comparing this in the code like this -> ElseIf bettype = "FGLG" Then If fcOption = "HF.HL" Then fg = "H" lg = "H". – Fenixs Sep 22 '13 at 14:27
btw, jus curious to know, how do you say that I am comparing "HF.AL" with 0 (in FT.OU). Because it is completely wrong as the string "HF.AL" is completely irrelevant to FT.OU. – Fenixs Sep 22 '13 at 14:38
Here `bettype = Application.WorksheetFunction.VLookup(oddsId, odds.DataBodyRange, 3, False)` you find bettype. For cell E68 oddsId = 13, If you look at Odd sheet, you can see that 13 TransId has Bet Type "FT.OU". – Dima Sep 22 '13 at 14:49
Here bettype = Application.WorksheetFunction.VLookup(oddsId, odds.DataBodyRange, 3, False) you look in odds.DataBodyRange by oddsId, but odds.DataBodyRange address is $A$3:$BT$47. So you search oddsId in TransId Column. – Dima Sep 22 '13 at 14:56

variant data type dependencies

1 Answers1