from a string, how to get the equivalent constant value?

[ValerieT]

From multiple worksheets list I calculate totals into "Total Population". All positioning of this sheet (columns and rows) are set by public constants.

Public Const ABCD= 5

After a lot of different calculation, each line to be accounted for has a new cell giving me the name of the constant, just have to read it:

My_Position = Cells(1,1)      (Let's say = "ABCD")

How can I "translate" the string "ABCD" read from the cell, into the constant value = 5 ? in order to end up with something like that:

Sheets("Total Population").Cells(Ou, My_Position) = My_Total

If it is not possible or too complicated, I could defined each column NAME (from formula/define name) by a string to be search (ABCD), but how can I search/find/lookup or anyfind else into it ?

Sorry it may be obvious for you but I am struggling . I started with the rows positioning with no problem because rows titles contain the searched string. But it is not true for columns header

[Aflatoon]

It's not possible. I think you would be better off using defined names anyway by the sound of it.

You could also use properties in an object module (you can't declare a Public constant in one) to return the value and use CallByName but defined names are simpler, and probably easier to alter/maintain.

[ValerieT]

Thanks that's what I thought. Once I have defined name for each columns, how how can I search for it with VBA?

[Aflatoon]

It is probably simpler to name the actual ranges - or at least the first cell in each - then you can just refer to Range("range name") in your code.

[ValerieT]

The problem : it is not user friendly. It is meant to be published.

Is "Find" or "Search" would be working OK on a hidden row? If Yes, I can go for hidden not friendly naming.

[Aflatoon]

I don't see how named ranges aren't user friendly? If you want to search for text, I'd use Application.Match

[lecxe]

... How can I "translate" the string "ABCD" read from the cell, into the constant value = 5 ? in order to end up with something like that ...

Hi

An easy way is to define a function that will work kind of as an enum whose constants you can call by name, or kind of a static hash table.

Ex.:

Public Function MyConstants(sConst As String) As Variant

Select Case sConst
    Case "ABCD": MyConstants = 3
    Case "EFGH": MyConstants = 5
    Case "IJKL": MyConstants = "Hi there!"
    Case Else: MyConstants = "#N/A"
End Select
End Function

Now you can, for ex., use:

Sheets("Total Population").Cells(Ou, MyConstants(My_Position)) = My_Total

Remarks:

This is just an answer to your vba syntax question, "how can I ...."

There are other solutions, like Aflatoon posted, some dynamic, for ex. using a dictionary or a collection.


Maybe you can redesign the solution and not need this at all. Aflatoon's suggestion of using Names seems a good way to go.

[ValerieT]

Hello both

Sorry Aflatoon, my answer was not clear.

I am adding a new calculation to something already in use. So I can't really change all the logic of it..

All that constants stuff is meant to be maintainable by someone not knowing VBA once I've left. I've teached her how to add/ remove/ change constants at the top of a module, but that's about it...

I'll take a shortcut and add column header in a hidden row, in order to search for text.... Not fun, but more secure and as soon as I've got a minute, I try your solution, as it is very close to what she already knows.

Thank you both for your help...