I have a problem assigning to a variable a value from a prefixed list. I'm going to explain better. In the "Module8" I put the following varibles with constant values:
Public Const Europe = 0.12, _
Middle_East= 0.12, _
Africa = 0.185
that should be visible also to other modules. In the "Module5" I have the following lines:
Sub CalcPop()
Sheets("Region").Select
Data = Range("I7:I15").Value
Geo_Region = Data(1, 1)
Population = Data(3, 1)
Extension = Data(4, 1)
S = Data(7, 1)
S = S * (1 - Geo_Region)
Now the code present in the "Module5" reads the Sheet called "Region" in which are present the regions (i.e. Europe, Africa, etc.) as strings, so the variable "Geo_Region" is valorized as Europe, Africa, etc. The problem is that when it comes at the line S = S * (1 - Geo_Region), it gives me the run-time error '13' "Type Mismatch". I guess because the code read the strings in the sheet, but it is not able to associate the string to the values present in the public constants that are in the "Module8". Could you suggest me on how to proceed in order to associate the value present in the public list with the string in the sheet?
Thank you in advance!
You can rather use a collection for that.
In Module8:
Dim Geo_Region_List As Collection '<-- declaring the variable as global will allow you to access it any time all over your code
In Workbook open method (in order to initialize the constants since the beginning of your program):
Set Geo_Region_List = New Collection
With Geo_Region_List
.Add 0.12, "Europe"
.Add 0.12, "Middle East"
.Add 0.185, "Africa"
End With
... and then, in your Module5 you access the value associated to the key:
S = S * (1 - Geo_Region_List(Geo_Region))
(I'm assuming that Geo_Region is a string like Europe and you want in return the associated value 0.12)