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

oleppedersen

New Member
Joined
Mar 23, 2013
Messages
13
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:

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

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

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Would a structure like this (expanded of course) be of use to you?
Code:
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
 
Last edited:
Upvote 0
Would a structure like this (expanded of course) be of use to you?
Code:
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
 
Upvote 0
More like this then?
Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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
Back
Top