Can we pass dynamic value instead of hard-coded to VBA code

[SachAccess]

Hi,
I am referring below code. Have changed original values to dummy values.
As per my understanding below code is assigning value to Dummy based on the Case.
If Case if less than 25 then Dummy = ABC in this manner. Please correct me if am wrong.
I was thinking, is it possible to change hard-coded ABC to dynamic input. For example, if I have these ABC, BCD stored in a mapping table.
Can I pass values from the table instead of hard-coded string in the code itself. Kindly pardon my ignorance.

Option Compare Database
Public Function Dummy(MyIdYourId As Integer) As String
On Error GoTo Err_Dummy

    Select Case MyIdYourId
        Case Is < 25
            Dummy = "ABC"
        Case 50
            Dummy = "BCD"
        Case 75
            Dummy = "CDE"
        Case 100
            Dummy = "EFG"
        Case Else
            Dummy = "FGH"
        End Select
   
Exit_Dummy:
    Exit Function
   
Err_Dummy:
    MsgBox "Error #" & Err.Number & " - " & Err.Description, vbCritical, "An Error has occurred"
    Resume Exit_Dummy
End Function

 

[Gasman]

No Option Explicit I see?

 

[SachAccess]

No Option Explicit I see?

I agree with you. It is a mistake. Not written by me. I write Option Explicit by habit in my each code. Thanks.
Have a nice day ahead.

 

[CJ_London]

you can - either use a dlookup or use a recordset

may not even need the case statement if the mapped table is designed in a certain way

 

[SachAccess]

you can - either use a dlookup or use a recordset

may not even need the case statement if the mapped table is designed in a certain way

Thanks for the help. Yes, we can use DLookUp, have read about it. Now got the reference.
Just out of curiosity, is it possible to pass the value from Table, the way we do it with Excel VBA when we pass cell value.
Have a nice day ahead.

 

[Gasman]

I agree with you. It is a mistake. Not written by me. I write Option Explicit by habit in my each code. Thanks.
Have a nice day ahead.

No need for new modules, if you Set Require variable Declaration in VBA window. Tools/Options

 

[SachAccess]

No need for new modules, if you Set Require variable Declaration in VBA window. Tools/Options
View attachment 102796

This certainly helps! Thanks a lot.
Though I knew we have Options feature in the ribbon, never paid attention to the details.

 

[Gasman]

Thanks for the help. Yes, we can use DLookUp, have read about it. Now got the reference.
Just out of curiosity, is it possible to pass the value from Table, the way we do it with Excel VBA when we pass cell value.
Have a nice day ahead.

Of course it is, but makes it pretty clunky, you may as well code it in the module you would call it from.

Public Function Dummy(MyIdYourId As Integer,strLess25 as string, str50 AS string, str75 AS String, str100 as String, strElse AS String) As String
On Error GoTo Err_Dummy

    Select Case MyIdYourId
        Case Is < 25
            Dummy = strless25
        Case 50
            Dummy = str50
        Case 75
            Dummy = str75
        Case 100
            Dummy = str100
        Case Else
            Dummy = strElse
        End Select
  
Exit_Dummy:
    Exit Function
  
Err_Dummy:
    MsgBox "Error #" & Err.Number & " - " & Err.Description, vbCritical, "An Error has occurred"
    Resume Exit_Dummy
End Function
[code]

 

[SachAccess]

Thanks for the help! I agree with you, it is more cumbersome. However, I was thinking, if user need to change the criteria, he would do it in the table rather than editing the code. That might be more simple for the user. I might be wrong too.

Of course it is, but makes it pretty clunky, you may as well code it in the module you would call it from.

Public Function Dummy(MyIdYourId As Integer,strLess25 as string, str50 AS string, str75 AS String, str100 as String, strElse AS String) As String
On Error GoTo Err_Dummy

    Select Case MyIdYourId
        Case Is < 25
            Dummy = strless25
        Case 50
            Dummy = str50
        Case 75
            Dummy = str75
        Case 100
            Dummy = str100
        Case Else
            Dummy = strElse
        End Select
 
Exit_Dummy:
    Exit Function
 
Err_Dummy:
    MsgBox "Error #" & Err.Number & " - " & Err.Description, vbCritical, "An Error has occurred"
    Resume Exit_Dummy
End Function
[code]

 

[Gasman]

So just use a DLookUp() on the table, no need for the Case logic.

 

[MajP]

tblRanges tblRanges

RangeID	RangeLow	RangeHigh	ReturnValue
1​	0​	25​	ABC
2​	25​	50​	BCD
3​	50​	75​	CDE
4​	75​	100​	EFG
6​	​	​	FGH

Public Function GetReturn(id As Long) As String
  Dim caseElse As String
  caseElse = DLookup("Returnvalue", "tblRanges", "RangeLow is null")
  GetReturn = Nz(DLookup("returnValue", "tblRanges", "RangeLow <= " & id & " And RangeHigh > " & id), caseElse)
End Function


Public Sub TestIt()
  Debug.Print 10 & " " & GetReturn(10)
  Debug.Print 25 & " " & GetReturn(25)
  Debug.Print 30 & " " & GetReturn(30)
  Debug.Print 60 & " " & GetReturn(60)
  Debug.Print 76 & " " & GetReturn(76)
  Debug.Print 1234 & " " & GetReturn(1234)
End Sub

10 ABC
25 BCD
50 BCD
60 CDE
76 EFG
1234 FGH