VBA Variable types?

[Jarodjp42]

I was hoping someone can shed some more light on variable types in VBA for me.

I understand that integer & long integer are for numbers only but when it comes to the rest like string, fixed string, variant, boolean, object, & Byte I am a little more confused.

I have written a formula before and used a variable type as string but was getting an error message. I changed the variable type to variant and the code worked. Not sure why it worked and why I needed to use the variant type in the code.

How do you know which variable type to use in your VBA coding?

Please help if you can, Thanks!

 

[Stiuart_W]

This Microsoft article gives you a lot more detail about the data types available to you

https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/data-types/data-type-summary

This also is a good guide on declaration or not of data types

https://powerspreadsheets.com/vba-data-types/

 

[Joe4]

It all depends on what you expect to return with your variables, and the integrity of your data. If there is a possibility to return errors (because you have bad data), you may need to use Variant.
However, your don't want to use it all the time. These errors can actually help you identify data issues (i.e. if you expect to get a number and return text instead).
Generally, you want to use the data type that you expect to return, and add error handling to your code to handle errors.

I have written a formula before and used a variable type as string but was getting an error message.

If you can provide an example of this, we may be able to tell you why or where you may have gone wrong.