Passing syntax to function

Bryhni

New Member
Joined
Nov 15, 2012
Messages
7
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.

Code:
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,434
Try this...

Code:
[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]
 
Last edited:

Bryhni

New Member
Joined
Nov 15, 2012
Messages
7
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:

Code:
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,434
You're welcome.

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

Forum statistics

Threads
1,141,310
Messages
5,705,677
Members
421,404
Latest member
Mikecollo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top