PDA

View Full Version : [SOLVED] from a string, how to get the equivalent constant value?



ValerieT
02-03-2014, 07:26 AM
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
02-03-2014, 07:31 AM
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
02-03-2014, 07:53 AM
Thanks that's what I thought. Once I have defined name for each columns, how how can I search for it with VBA?

Aflatoon
02-03-2014, 07:56 AM
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
02-03-2014, 08:12 AM
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
02-03-2014, 08:13 AM
I don't see how named ranges aren't user friendly? If you want to search for text, I'd use Application.Match

lecxe
02-03-2014, 08:24 AM
... 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
02-03-2014, 08:38 AM
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...