Refering to a variable using another variable - possible in Excel VBA?

[oleppedersen]

Hi, I have searched for this to no avail, any help greatly appreciated:

I run through several huge datasets in order to analyze some KPI's. The output is natural langage text.

In the text variable that contains the full analysis, placeholder text e.g. 'xxSNITTxx'<snitt<'<name>, <total> etc are placed. Before writing the analysis to the database, these placeholder texts (there can be MANY in one final text) are replaced with the correct values based on the same number of variables as there are placeholder variations.

The problem is, this leads to a VERY long code where I look for ALL placeholders separately:

            ant = InStr(aD(i, 3), "xxSNITTxx<snitt<<snitt>")
            If ant > 0 Then
                aD(i, 3) = Left(aD(i, 3), ant - 1) & snitt & " " & Right(aD(i, 3), Len(aD(i, 3)) - ant - 9)
                If ant > 1 Then
                    GoTo erstattkode
                    Else: ant = 0
                End If
            End If

With possibly 50 placeholders, this will be 400 lines of code by itself. In this example, xxSNITTxx <snitt>is replaced by the value contained by the variable snitt. This works great.

I am still thinking: There must be a way to keep all placeholders info in an array? However, how do I handle the VARIABLE that the placeholder is replaced with? (In the example, 'snitt')

for x = 1 to 50
  txtExp = aPlaceholder(x,1)
  valVar = aplaceholder(x,2)
  valLength = aPlaceholder(x,3)
repeatcode: ' in case there are several instances of the same variable in the same text
            ant = InStr(aD(i, 3), txtExp) ' this is ok
            If ant > 0 Then
                aD(i, 3) = Left(aD(i, 3), ant - 1) & valVar & " " & Right(aD(i, 3), Len(aD(i, 3)) - ant - valLength) ' I can't get this to work
                If ant > 1 Then
                    GoTo repeatcode
                    Else: ant = 0
                End If
            End If
next x

When I try this, the valVar is just replaced by the variable NAME, not the value of the variable.

Just to be clear: I definitely need the placeholder text system. Natural text is written by analyst outside the code, and the writer only concerns her-/himself with adding the placeholders. This is great, and more important to keep, than to solve my little, but annoying problem.</snitt></snitt<<snitt></total></snitt<'<name>

 

[Peter_SSs]

Would a structure like this (expanded of course) be of use to you?

Sub Test()
  Dim x As Long
  Dim aPlaceholder(1 To 2, 1 To 2)
  Dim TestString As String
  
  aPlaceholder(1, 1) = "xxSNITTxx"
  aPlaceholder(1, 2) = "abc"
  aPlaceholder(2, 1) = "xxYYYYxx"
  aPlaceholder(2, 2) = "John Jones"
  
  TestString = "Some text xxSNITTxx then more xxYYYYxx then more"
  
  For x = LBound(aPlaceholder) To UBound(aPlaceholder)
    TestString = Replace(TestString, aPlaceholder(x, 1), aPlaceholder(x, 2), 1, -1, vbTextCompare)
  Next x
    
  MsgBox TestString

End Sub

 

[oleppedersen]

Would a structure like this (expanded of course) be of use to you?

Sub Test()
  Dim x As Long
  Dim aPlaceholder(1 To 2, 1 To 2)
  Dim TestString As String
  
  aPlaceholder(1, 1) = "xxSNITTxx"
  aPlaceholder(1, 2) = "abc"
  aPlaceholder(2, 1) = "xxYYYYxx"
  aPlaceholder(2, 2) = "John Jones"
  
  TestString = "Some text xxSNITTxx then more xxYYYYxx then more"
  
  For x = LBound(aPlaceholder) To UBound(aPlaceholder)
    TestString = Replace(TestString, aPlaceholder(x, 1), aPlaceholder(x, 2), 1, -1, vbTextCompare)
  Next x
    
  MsgBox TestString

End Sub

Thank you for your suggestion, but the problem is that aPlaceholder(2,2) (as an example) really is another variable that refers to a value that changes for each instance. So "John Jones" in your example is really a string variable or a number variable (well, it could exclusively be a string if that helps) that also needs to be replaced with the actual value for the precise case.

The xxSNITTxx I referred to in my example, is a value that changes every time, depending on whether another variable connected with the KPI change.

Thanks, anyway!

Ole

 

[Peter_SSs]

More like this then?

Sub Test()
  Dim x As Long
  Dim dVar As Double
  Dim aPlaceholder(1 To 2, 1 To 2) As Variant
  Dim TestString As String, sVar As String
  
  dVar = 32.55
  sVar = "John Jones"
  
  aPlaceholder(1, 1) = "xxSNITTxx"
  aPlaceholder(1, 2) = dVar
  aPlaceholder(2, 1) = "xxYYYYxx"
  aPlaceholder(2, 2) = sVar
  
  TestString = "Some text xxSNITTxx then more xxYYYYxx then more"
  
  For x = LBound(aPlaceholder) To UBound(aPlaceholder)
    TestString = Replace(TestString, aPlaceholder(x, 1), aPlaceholder(x, 2), 1, -1, vbTextCompare)
  Next x
    
  MsgBox TestString

End Sub