PDA

View Full Version : Solved: Can a Constant be used as function parameter?



BenD
02-06-2008, 02:05 PM
I have created a multi-lingual Excel application. It handles UserForm, menu, VBA messages and worksheet cell translations. It retrieves the translations from an Access database.
All works pretty well. However, I am not particularly happy the way I handled the VBA messages to users. Let me explain:

All the user-exposed texts (e.g. messages, error messages, status bar text, etc.) are defined as string Constants:


' Public string constant for status bar:
Public Const gsSTATUS_RECALCULATING As String = "Recalculating model"


The following code tries to retrieve a translated value:


' Get status bar message from Access database:
gsVBATrans = mclsTranslate.sGetVBAText("gsSTATUS_RECALCULATING")
' If no translation available, use the text value of the string constant :
If gsVBATrans = "" Then gsVBATrans = gsSTATUS_RECALCULATING

With mclsProgBar
.Text = gsVBATrans & gsPOINT_PENDING
.Show
End With


The function "sGetVBAText" is given a KeyID (that is in fact the value of the string constant) and tries to retrieve a translation from the Access database. If the routine returns an empty string it means that for whatever reason no translation is available (e.g. perhaps the link to the Access database could not be made). At that point in time it should use the string value of the constant as value to disclose to the user.

My application uses a lot of these constructions to handle VBA text visible to users. And it really clutters a lot of my routines.


So far a description on how it works. Now the "issue" I have => this would be much more elegant & efficient if I could assign a Constant as a parameter to a function. In that case something like the second line in the code below would suffice in all my calling subroutines if I wanted to retrieve a translated piece of text:


With mclsProgBar
.Text = mclsTranslate.sGetVBAText("gsSTATUS_RECALCULATING", gsSTATUS_RECALCULATING)
.Show
End With


It throws up both the Constant and the string KeyID for the translation. If the KeyID is not found, the routine would just return the value of the Constant.

As far as I am aware, you can not use a Constant as a function parameter. Questions I have:

Am I correct in this assumption.
If not, please can you let me know how I would do that?
If I am correct, would you have a suggestion that would deliver what I (tried to) explain(ed)?Would be very much interested in your opions. I liked a lot of the routines I written, but when I added the multi-lingual feature to all of them, I became compelled to find a more elegant alternative.

mikerickson
02-06-2008, 04:44 PM
I'm not sure about your specific case, but the following shows "my special constant"

Const myConst As String = "my special constant"

Sub test()
MsgBox myFtn(myConst)
End Sub

Function myFtn(inString As String) As String
myFtn = inString
End Function

BenD
02-06-2008, 05:47 PM
I'm not sure about your specific case, but the following shows "my special constant"

Const myConst As String = "my special constant"

Sub test()
MsgBox myFtn(myConst)
End Sub

Function myFtn(inString As String) As String
myFtn = inString
End Function

Thanks for your reaction, but I am afraid this does not work for my case.
What I need is:

To first provide the Constant value to a function (a string) i.e. "Recalculating model".
If that function can not do anything based on the value, the function should then be able to refer to the Constant itself, i.e. gsSTATUS_RECALCULATING .Obviously the tricky part of my question is that I alternatively need to refer to either the value of the Constant or to the Constant itself.

mikerickson
02-06-2008, 06:52 PM
maybe something like this?

Public Const myConstValue As Double = 3.14

Function Reciprical(inValue As Double) As Double

If inValue = 0 Then
Reciprical = myConstValue
Else
Reciprical = 1 / inValue
End If
End Function

Sub trial()

MsgBox Reciprical(2)
MsgBox Reciprical(0)
End Sub
Declaring the constant as Public makes it avaliable in all proceedures.

BenD
02-07-2008, 03:07 AM
I forgot to mention that most of the Constants I use are actually Public...

I am afraid this approach does not do it either. There are at least a hundred messages that need to be handled in the translation routine, which would mean I would need to create as much If-Then-Else (or Select Case) constructions as there are messages to be handled.

But I appreciate your suggestion!

rory
02-07-2008, 08:53 AM
I don't understand your problem. As far as I can tell, you want a function to which you pass a string, it tries to look it up in a database to find a translation, and if it can't find it, it returns that original string. Where is the problem, given that the funciton was passed the information it needs in the first place? What did I miss?

BenD
02-07-2008, 10:54 AM
I don't understand your problem. As far as I can tell, you want a function to which you pass a string, it tries to look it up in a database to find a translation, and if it can't find it, it returns that original string. Where is the problem, given that the funciton was passed the information it needs in the first place? What did I miss?

I understand that what I am trying to say is difficult to express. And your remarks also showed me I made a mistake in an earlier explanation. So let me try again:

What you might have missed is the fact that the string I pass to the function is the name of a Constant (not its value like I indicated earlier). That Constant name is used as an identifyer to look up a corresponding translation from a table/array. If that table can not be referenced for whatever reason (e.g. no link to Access database because the file is not present) than the actual value of the constant should be returned.

To correct a previous explanation of the issue:
What I need is:

To first provide the Constant name to a function (as a string) e.g. "gsSTATUS_RECALCULATING".
If that function can not do anything based on this string value, the function should then be able to refer to the Constant itself. By referring to the Constant I can assign its value as a return value of the function, e.g. "Recalculating model" (which is the default English translation of the text I was trying to get a translation for).Obviously the tricky part of my question is that I alternatively need to refer to both the (string) name (as argument of the function) and the Constant itself so I can retrieve its value within that function.

I have difficulty explaning it differently. Perhaps some extra code might help:

The following is part of a class module (mclsTranslate):

'-------------------------------------------------------------------------------
' PROCEDURE : sGetVBAText
' Date : 17/01/2008
' Author : Bennie Douma
' Description : Retrieves the VBA text translation that corresponds to a KeyID.
' Returns :
' Parameters : sKeyID - The KeyID of the translation text that needs to be retrieved.
' The KeyID is the defined constant name within parenthesis!
' (e.g. '"gsSTATUS_LOADING_LANG_LABELS"')
'
' Notes : Two static arrays with KeyID's and text need to be loaded before
' this routine can work properly.
' The arrays are 0-based!!!
'
' Date Action
'-------------------------------------------------------------------------------
' 17/01/2008 Initial version
'
Public Function sGetVBAText(ByRef sKeyID As String, Optional ByRef vPlaceValues As Variant) As String
Dim lRecordNo As Long

Const sSOURCE As String = "sGetVBAText()"
On Error Resume Next

' Find the record number in which the KeyID can be found:
lRecordNo = Application.Match(sKeyID, mavVBAKeyIDs(), False)

If Err.Number = 0 Then
' Check if the entry actually does contain a translation:
If Not IsNull(mavVBAText(0, lRecordNo - 1)) Then
' Get the corresponding translation from the text array:
sGetVBAText = CStr(mavVBAText(0, lRecordNo - 1))
' Check if some place holders need to be replaced:
If Not IsMissing(vPlaceValues) Then
sGetVBAText = sReplaceHolders(sGetVBAText, vPlaceValues)
End If

Else

' No translation => Let the receiving routine know that no translation is available:
sGetVBAText = ""
' This is actually the place where I would like to refer to a Constant that is declared with the
'name that is the same as the sKeyID passed on to this function.
'If I can do this, I can let the function return the value of the Constant.

End If

Else
' Also here I would like to do the same as above.
' No matching KeyID => use the default text from the declared constant:
sGetVBAText = ""
Err.Clear
End If

ErrorExit:
Exit Function
ErrorHandler:
If Err.Number <> glHANDLED_ERROR Then Err.Description = Err.Description & " (" & sSOURCE & ")"
If bCentralErrorHandler(msMODULE, sSOURCE) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Function

The calling procedure could look like this:

' Constant name is same as KeyID in table with translations, and stores default English translation as value:
Public Const gsYes as String = "Yes"

Public gsVBATrans As String


Public Sub sGetTranslatedText()


Dim sText as String

gsVBATrans = mclsTranslate.sGetVBAText("gsYes")
If gsVBATrans = "" Then gsVBATrans = gsYes
sText = gsVBATrans
MsgBox sText


End Sub


If the calling function could provide either the translated text or the default English text, than all my calls from the main code (a lot) could replace the construction above as following:
MsgBox mclsTranslate.sGetVBAText("gsYes", gsYes)

But this would only work if the second parameter in the function could be declared as a constant. And that is not possible as far as I am aware. A workaround is something I am looking for.

Not sure if this makes things any clearer?

rory
02-08-2008, 02:40 AM
That's what confuses me. If you call a function like this:
mclsTranslate.sGetVBAText("gsYes", gsYes)
then all the function has to do is return the second argument (which is the value) if it can't lookup a value for the first argument. So your sGetVBAText function looks something like:

Function sGetVBAText(strConstName As String, strDefault As String) As String
Dim strLookup As String

strLookup = LookupValue(strConstName)
If strLookup = "" Then
sGetVBAText = strDefault
Else
sGetVBAText = strLookup
End If
End Function

BenD
02-09-2008, 05:12 AM
Rory, you finally opened my eyes!:bow:

Like with so many things, if you are too close to a subject you overlook the obvious. I was so focussed on the need to declare a variable as a Constant in the function, that I overlooked the obvious. Which is that the Constants I use for this routine are string values and I just need to declare a simple string variable as a parameter. How stupid one can be...

Do not ask me why I did not see this myself, I can not explain. But many thanks, this (now very simple to overcome) "problem" I thought I had is solved!