Const Variable in "Type"-Statement vba

Question

I want to sum up my Const Variables in a VBA Makro like this:

Private Type Company
   Public Const CompanyNameColumns As String = "14"
   Public Const CompanyNameStartRow As Integer = 5
Type End

I can't run this code. I think the problem is, that there is no possibility of defining a Const within a Type Statement. Has anybody a workaround for that?

Answer 1

So, it took me a second, but then it smacked me like a ton of bricks what's going on here.

TL;DR: You can't assign values during definition of a type, but there is a proper way to do what you're trying to do.

The reason you can't assign to it is because you're defining a type. I know that sounds cyclical and redundant, but that's exactly what is going on. You're trying to assign values in a place that is meant to define a data structure. That's all a UDT does. It defines a structure. It makes no sense to assign values to a structure.

As you've found, one solution is to create a new module and store your constants there.

Constants.bas

Public Const CompanyNameColumns As String = "14"
Public Const CompanyNameStartRow As Integer = 5

Which then gets called like this...

Constants.CompanyNameColumns
Constants.StartNameRow

And this is fine, but runs the risk of becoming a catch all. It would be much better to move these into a class module along with the logic they relate to. That way all of the related logic and data are in one place instead of scattered about multiple *.bas files in your project.

If you're going the class route, but the classes only hold this data structure, without any real state, you might want to consider making them global default instances. This is similar to what is known as "static" classes in other languages.

Answer 2

I worked around this Problem now by just defining a new Module. Named it Company and put the Consts in there!