Variables in VBA

[prajul89]

Is there a Variable that can contain both a string and a Value?
Also Dose BYVal coverts a String say '01 in Value i.e, 1?

 

[VoG]

You can use the Variant data type.

ByVal refers to the way of passing variables - see http://www.cpearson.com/excel/byrefbyval.aspx

 

[kpark91]

1) I don't know what you mean by containing both a string and a value.
2) No, it doesn't. Look into functions such as CDbl(), CInt() and such..

 

[kpark91]

1) If you mean something like struct then look up for "TYPE" in MS Help where you can store one or more elements.

 

[prajul89]

Thanks I will give it a try

 

[Hermanito]

A type that can contain any other value type is Variant. Most of the time it is better to avoid using it, it tends to promote sloppy programming, and it performs much slower than dedicated types too.

The ByVal would not change a string 01 to 1, if the parameter is defined as a string, e.g.:

Public Function DoSomething(ByVal WithThis As String) As String
End Function

If the parameter is defined as Variant however, it depends on what you do with it inside the function, if there are any implicit conversions applied.

The ByVal indicates that the parameter is passed as a copy, instead of a reference to the original variable, the opposite keyword is ByRef. So, in itself, ByVal has nothing to do with value or string...

 

[Jonmo1]

Is there a Variable that can contain both a string and a Value?
Also Dose BYVal coverts a String say '01 in Value i.e, 1?

You'll need to clarify that first question a bit.
To the best of my knowledge, a String IS a Value.
Do you mean both String and NUMBER ?
If so, no.
A variable can be a String OR a Number, but not both at the same time.

 

[kpark91]

A type that can contain any other value type is Variant. Most of the time it is better to avoid using it, it tends to promote sloppy programming, and it performs much slower than dedicated types too.

The ByVal would not change a string 01 to 1, if the parameter is defined as a string, e.g.:

Public Function DoSomething(ByVal WithThis As String) As String
End Function

If the parameter is defined as Variant however, it depends on what you do with it inside the function, if there are any implicit conversions applied.

The ByVal indicates that the parameter is passed as a copy, instead of a reference to the original variable, the opposite keyword is ByRef. So, in itself, ByVal has nothing to do with value or string...

Thanks alot for the tip but why would it be considered sloppy programming?

and aren't structs in C also 'slower' than dedicated types as all the other programmings as structs are used for organization/easiness of the code?

Please and thank you!
kpark.

 

[Hermanito]

When I say 'sloppy programming', I mean it is almost never necessary to have a variable that, for example, at one point in your program stores a string, and at another point stores a double.
It will be a debugging nightmare if you are calling functions with such variables, and the function needs a string, but you actually pass a double. You might even get away with it because VB(A) does lots of implicit conversions behind the screens, but more often than not, they are not what you meant...

Therefore I say that using Variants (or trusting, knowingly or unknowingly, on implicit conversions) is sloppy programming

The only case where I use Variants in VBA is when passing an array ByRef for a function that fills the array with the results from an SQL-query and where the data can contain strings, integers, datetimes, doubles, etc. To be able to make such functions as generic as possible, I have to use Variant. In that particular case, it's not sloppy, it's clever

 

[kpark91]

Ahh yes. That is very true. So, my excuse will be I didn't know what the questioner was coding?! xD

Thanks alot of the answer.
Cheers,