Dynamic code Generation

**Bazman2** · 08-18-2009, 11:45 AM

Hi there,

I need to write some code where I test various functions to see which matches some input data the best. I have 8 functions which can be linearly combined in one of 8^8 combinations.

You have to use 8 functions each time but the same function can be used 8 times (although this is unlikely to prove interesting).

So I have to generate a VBA function to test each test function against the data.

Clearly I can use a combination of for loops and case statements to generate each functional form as follows:


Function FunctionGenerator()

Dim func As String

func = "f(x) = "

For i = 1 To 8
    
    Select Case i
    
    Case 1
    
    func = func & " + (1-exp(-(x(i)*t))/(x(i)*t)"
    
    Case 2
    
    func = func & "+ (1-exp(-(x(i)*t))/(x(i)*t)-exp(x(i)*t"
    
    ...
    
    Case 8
    
    
    End Select
Next i

I then want to paste this function into a VBA function at run time to dynamically each function to be testetd. Is this possible?

If not is there a different method to generate all these functions without me having to type them all into VBA by have?

Baz

**shg** · 08-18-2009, 01:17 PM

Maybe you could use something like this as a point of departure:

Sub Demo()
    Dim iCase       As Long

    Randomize
    iCase = Int(Rnd * 1677216)
    MsgBox "Case " & iCase & vbLf & _
           "(" & Dec2Bin(iCase, 3) & ")" & vbLf & vbLf & _
           FunctionGenerator(iCase)
End Sub

Function FunctionGenerator(iCase As Long) As String
    Dim avFunc      As Variant
    Dim sFunc       As String
    Dim i           As Long
    Dim iFunc       As Long

    avFunc = Array(" + (1-exp(-(x(i)*t))/(x(i)*t)", _
                   " + (1-exp(-(x(i)*t))/(x(i)*t)-exp(x(i)*t", _
                   " + func2", _
                   " + func3", _
                   " + func4", _
                   " + func5", _
                   " + func6", _
                   " + func7")
    
    sFunc = "f(x) = " & vbLf
    For i = 0 To 7
        iFunc = (iCase And (7 * 8 ^ i)) / 8 ^ i
        sFunc = sFunc & avFunc(iFunc) & vbLf
    Next i

    FunctionGenerator = Left(sFunc, Len(sFunc) - 1)
End Function

Function Dec2Bin(ByVal iNum As Long, _
                 Optional ByVal iGrp As Long = 32) As String
    ' shg 2007-0807
    Dim i           As Long
    Dim n           As Long
    Dim sBin        As String

    If iGrp < 1 Then iGrp = 32

    n = 1
    For i = 1 To 30
        sBin = IIf(iNum And n, "1", "0") & sBin
        If (Len(Replace(sBin, " ", "")) Mod iGrp) = 0 Then sBin = " " & sBin
        n = 2 * n
    Next
    sBin = IIf(iNum And n, "1", "0") & sBin
    If (Len(Replace(sBin, " ", "")) Mod iGrp) = 0 Then sBin = " " & sBin

    If iNum < 0 Then
        Dec2Bin = "1" & sBin
    Else
        i = InStr(1, sBin, "1")
        Dec2Bin = Mid(sBin, IIf(i, i, Len(sBin)))
    End If
End Function

To see the results as a UDF, put this in a cell and format to wrap text:

=FunctionGenerator(INT(RAND()*8^8))

**Bazman2** · 08-18-2009, 01:35 PM

Wow thank you so so much for this!!!!

I need to use the generated function in a VBA fucntion in the following format:

Is it possible to pass the generated function in some way?

I only need to generate the function once per run, but the generated function will be called repeatedly.


Public Function MyFunction(params As Variant, x As Variant) As Variant
  a = params(1)
  b = params(2)
  c = params(3)
  d = params(4)
  m = UBound(x)
  ReDim function_values(1 To m)
  'For k = 1 To cols
  For i = 1 To m
    
    xi = x(i)
    
    function_values(i) = a + b * ((1 - Exp(-c * x(i))) / (c * x(i))) + d * ((1 - Exp(-c * x(i))) / (c * x(i)) - Exp(-c * x(i)))
    
  Next i
  'Next k
  MyFunction = function_values
End Function

**shg** · 08-18-2009, 03:09 PM

Sorry, I don't follow that.

**Bazman76** · 08-18-2009, 04:42 PM

What I mean is that each function only needs to be generated once during each run of the program.

After that though each generated function will be called multiple times during the program until the optimal set of parameters are found.

the example code below shows how one the function will be called during the optimisation part of the program


Public Function MyFunction(params As Variant, x As Variant) As Variant
  a = params(1)
  b = params(2)
  c = params(3)
  d = params(4)
  m = UBound(x)
  ReDim function_values(1 To m)
  'For k = 1 To cols
  For i = 1 To m
    
    xi = x(i)
    
    function_values(i) = a + b * ((1 - Exp(-c * x(i))) / (c * x(i))) + d * ((1 - Exp(-c * x(i))) / (c * x(i)) - Exp(-c * x(i)))
    
  Next i
  'Next k
  MyFunction = function_values
End Function

Is there some way to dynamically link the function generated by your code into the function above?

If not is there another way to achieve the same aim?

I have now added a copy of the spreadsheet where an example optimisation is being carried out. I need to figure out how to get the functions generated by your code into the optimiser. In the same way that "myfunction" in macro1 is being used. (or alternately any other way that works)

**shg** · 08-18-2009, 07:47 PM

You can't bind the variable names in the string to run-time variables.

I don't see the relationship between your code below and the summation of eight functions in some order. In fact, if all you were doing is summing some combination of 8 functions, order doesn't matter, so that's not what you're trying to do (I surmise).

You need to back off to 10,000 feet and explain what you're trying to do, without me having to dissect your workbook.