Passing syntax to function

[Bryhni]

Hi,
I can't figure out how to do this (there probably are many ways, but anyway):
I have a large Excel workbook that needs to work for many different projects and so I have user forms and named ranges to set and store config parameters.

One of the parameters that is stored is a string defining a syntax for one particular VBA function. If the string is i.e. "Z"&PROD&"-"&SYS then I would like the function to concatenate values like it is indicated in the "syntax string" and return the concatenated string.
The function call from cell F6 can be =FormatTag(F6) but here is perhaps another issue.

Function FormatTag(MyCell As Range)       
   Syntax = Range("Syntax_Tag")  ' example: "Z"&PROD&"-"&SYS
   PROD = Range(MyCell).Offset(0,3)
   SYS = Range(MyCell).Offset(0,2)
   FormatTag = Syntax
End Function

Any help for a newbie is appreciated!

Best regards,
Terje

 

[AlphaFrog]

Try this...

[COLOR=darkblue]Function[/COLOR] FormatTag(MyCell [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]Dim[/COLOR] syntax [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    syntax = Range("Syntax_Tag")  [COLOR=green]' example: "Z"&PROD&"-"&SYS[/COLOR]
    syntax = Replace(syntax, "PROD", MyCell.Offset(0, 3))
    syntax = Replace(syntax, "SYS", MyCell.Offset(0, 2))
    FormatTag = Evaluate(syntax)
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]

 

[Bryhni]

Much appreciated!
I was banging my head to the desktop at 2 am last night and wake up to a sunny day with the solution right at hand! Here is the final code, with some quotation marks which were required to make it run:

Function FormatTag(MyCell As Range)
    Dim Syntax As String
    Syntax = Range("Syntax_LineTag")  ' example: "Z"&PROD&"-"&SYS
    Syntax = Replace(Syntax, "PROD", Chr(34) & MyCell.Offset(0, [Col_Prod] - [Col_Tag]) & Chr(34))
    Syntax = Replace(Syntax, "SYS", Chr(34) & MyCell.Offset(0, [Col_Sys] - [Col_Tag]) & Chr(34))
    FormatTag = Evaluate(Syntax)
End Function

Thanks AlphaFrog!

Terje

 

[AlphaFrog]

You're welcome.

I had used numbers for PROD and SYS which didn't need the quotes. Glad you figured it out.