variable used based on concatenation treated as a string

[rjudge]

The code below is so close but unfortunately the function included is using the string variable's name rather than accessing their values. I am using a loop to populate a series of string variables that only differ in the last 1 or 2 characters which are integers (e.g. txtQuestion1). So the question is, how do I get the function to use the variable's value rather than its name? Sorry - I am sure this is trivial.

CODE:
Dim NumStudentErrors As Integer
NumStudentErrors = 0
Dim tempQuestion As String
Dim txtQuestionxx As String
Dim tempAnswer As String
Dim answerxx As String

'Determine current folder and store text file there
If UCase(Right(ActiveDocument.Name, 1)) = "X" Then
'.docx
strFileName = Left(ActiveDocument.Name, Len(ActiveDocument.Name) - 5)
Else
'.doc
strFileName = Left(ActiveDocument.Name, Len(ActiveDocument.Name) - 4)
End If
pathname = ActiveDocument.Path & "\" & strFileName & "txt"
'write the txt file
Open pathname For Output As #1

' Loop to select sequential question and answer

For k = 1 To numQuestions

tempQuestion = "ActiveDocument.txtQuestion" & k & ".Value"
txtQuestionxx = tempQuestion
tempAnswer = "answer" & k
answerxx = tempAnswer

If Levenshtein(txtQuestionxx, answerxx) / Len(answerxx) > 0.25 Then NumStudentErrors = NumStudentErrors + 1
Print #1, k
Print #1, txtQuestionxx, answerxx
Print #1, Levenshtein(txtQuestionxx, answerxx)
Print #1, Levenshtein(txtQuestionxx, answerxx) / Len(answerxx)
Next k

[macropod]

Hints:

strFileName = Left(ActiveDocument.Name, InStrRev(UCase(ActiveDocument.Name), ".DOC") - 1)

pathname = ActiveDocument.Path & "\" & strFileName & ".txt"

tempQuestion = ActiveDocument.txtQuestion & k & .Value
(assuming .Value has some meaning in this context)

Levenshtein ???

PS: When posting code, please use the VBA tags.

[rjudge]

Hi Paul: thanks for the response. I am a total newbie and do not even know what you mean by VBA tags? I will figure that out and use them in the future. Levenshstein is a function for an algorithm that compares two strings and determines the minimum number of edits needed to make them identical: so it is an indication of similarity.
You hit the nail. The tempQuestion variable is a concatenation to create an existing variable that does have an existing value: a string that I want to compare. The problem is the function thinks the variable name is the string --- not its value.

[macropod]

I am a total newbie and do not even know what you mean by VBA tags? The 'post' window includes a button titled 'VBA'. Simply click on that to add the tags, then post your code between them. This formats the code, making it much easier to understand. For example, with your code (revised):
Dim NumStudentErrors As Integer
NumStudentErrors = 0
Dim tempQuestion As String, tempAnswer As String

'Determine current folder and store text file there
strFileName = Left(ActiveDocument.FullName, InStrRev(ActiveDocument.FullName, ".")) & "txt"
'write the txt file
Open strFileName For Output As #1

' Loop to select sequential question and answer

For k = 1 To numQuestions

tempQuestion = ActiveDocument.txtQuestion & k 'assuming .Value isn't relevant
tempAnswer = "answer" & k

If Levenshtein(tempQuestion, tempAnswer) / Len(tempAnswer) > 0.25 Then NumStudentErrors = NumStudentErrors + 1
Print #1, k
Print #1, tempQuestion, tempAnswer
Print #1, Levenshtein(tempQuestion, tempAnswer)
Print #1, Levenshtein(tempQuestion, tempAnswer) / Len(tempAnswer)
Next k

[rjudge]

Thanks Paul. It tried out the code you provided. The code for strFileName was far more elegant, but did not work and I have tried to work through why yet. I do see you are you are doing though - so thanks.

The core problem still exists. How do I get the variable's value into the function and not the variable's name?

If the variable's value is "ABC010" and the variable's name is txtQuestion01, the function should use "ABC010" and not "txtQuestion01". Yet that is what it does.

Thanks again for your assistance.

[gmaxey]

You have never provided the function. What is its code?

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim lngA As Long, lngB As Long
lngA = 2
lngB = 2
MsgBox SumOfVariables(lngA, lngB)
End Sub
Function SumOfVariables(ByRef A As Long, B As Long) As Long
SumOfVariables = A + B
End Function

[rjudge]

The function Levenshstein is as follows:


Public Function Levenshtein(s1 As String, s2 As String)
Dim i As Integer
Dim j As Integer
Dim l1 As Integer
Dim l2 As Integer
Dim d() As Integer
Dim min1 As Integer
Dim min2 As Integer
l1 = Len(s1)
l2 = Len(s2)
ReDim d(l1, l2)
For i = 0 To l1
d(i, 0) = i
Next
For j = 0 To l2
d(0, j) = j
Next
For i = 1 To l1
For j = 1 To l2
If Mid(s1, i, 1) = Mid(s2, j, 1) Then
d(i, j) = d(i - 1, j - 1)
Else
min1 = d(i - 1, j) + 1
min2 = d(i, j - 1) + 1
If min2 < min1 Then
min1 = min2
End If
min2 = d(i - 1, j - 1) + 1
If min2 < min1 Then
min1 = min2
End If
d(i, j) = min1
End If
Next
Next
Levenshtein = d(l1, l2)
End Function

[gmaxey]

I'm not sure what the issue is:

Sub ScratchMacro()
Dim str1 As String, str2 As String
str1 = "AAA"
str2 = "ZZZ"
MsgBox Levenshtein(str1, str2)
End Sub
Public Function Levenshtein(s1 As String, s2 As String)
Debug.Print s1 'Returns "AAA" which is the string value not the name.
Debug.Print s2 'Returns "ZZZ" which is the string value not the name.
End Function

[macropod]

Thanks Paul. It tried out the code you provided. The code for strFileName was far more elegant, but did not work and I have tried to work through why yet. I do see you are you are doing though - so thanks.
The line:
strFileName = Left(ActiveDocument.FullName, InStrRev(ActiveDocument.FullName, ".")) & "txt"
works fine for me. What do you get with:
Sub Test()
MsgBox Left(ActiveDocument.FullName, InStrRev(ActiveDocument.FullName, ".")) & "txt"
End Sub

[fumei]

So the question is, how do I get the function to use the variable's value rather than its name? Sorry - I am sure this is trivial.


tempQuestion = "ActiveDocument.txtQuestion" & k & ".Value"

May I point out that ActiveDocument.txtQuestion has quotes around it?

And BTW, while not relevant in THIS case, it is a good habit to be explicit. If you want the text from the textbox it is explicit to use .Text. It is best practice to not use .Value which in all controls is the DEFAULT.

[macropod]

Hi Gerry,

I agree that's a problem - which I corrected in my posts. You'll note that I too queried what '.Value' was supposed to represent (Given the paucity of detail, for all I knew it could have been an Excel cell value). On reflection, the code is probably attached to a userform, in which case what's required may well be:
tempQuestion = ActiveDocument.txtQuestion.Text & k

[fumei]

So...ummmmm, what happened??

[rjudge]

Sorry I started this and then disappeared. It has been a scramble for me the past few days and will be for awhile still. I greatly appreciate ALL your inputs and that you have taken your valuable time to provide me with direction. I am sure this thread has all the answers I need. I will post success or otherwise hopefully within a few days.

Best Regards

[rjudge]

Ok. Reread the comments and think I see the problem based on your inputs. I still can't get it to work though. First the code:

For k = 1 To numQuestions

tempQuestion = "ActiveDocument.txtQuestion" & k & ".Text"
tempAnswer = "answer" & k

If Levenshtein(tempQuestion, tempAnswer) / Len(tempAnswer) > 0.25 Then NumStudentErrors = NumStudentErrors + 1
Print #1, k
Print #1, tempQuestion, tempAnswer
Print #1, Levenshtein(tempQuestion, tempAnswer)
Print #1, Levenshtein(tempQuestion, tempAnswer) / Len(tempAnswer)


I think the reason everything works for you guys has to do with the tempQuestion. You are creating a string and running it into the function and it works - as it should. My problem is tempQuestion isn't supposed to create a string. It is needed to be able to create a sequence of variable names that reference values stored by a text box in Word. The function just treats tempQuestion as "ActiveDocument.txtQuestion1.text" rather than as the value stored in ActiveDocument.txtQuestion1.text. Below is a sample of the output.

1
ActiveDocument.txtQuestion1.Text answer1
29
4.14285714285714
2
ActiveDocument.txtQuestion2.Text answer2
29
4.14285714285714
3
ActiveDocument.txtQuestion3.Text answer3
29
4.14285714285714
4
ActiveDocument.txtQuestion4.Text answer4
29
4.14285714285714
5
ActiveDocument.txtQuestion5.Text answer5
29
4.14285714285714
6
ActiveDocument.txtQuestion6.Text answer6

You can see the Leveshtein result is the same each time because it is merely looking at "ActiveDocument.txtQuestion01.text" each time with the change in each iteration being the number at the end of txtQuestion.

[macropod]

Has anyone said you should have 'ActiveDocument.txtQuestion' in quotes???.

It appears you're trying to use to address controls on a userform - but you've never yet said what 'ActiveDocument.txtQuestion' is supposed to represent. Assuming it's for a userform, you can't do it that way. Try:
For k = 1 To numQuestions
tempQuestion = Me.Controls("txtQuestion" & k).Text

[rjudge]

The Word document is a classroom assignment where the students enter their answers into a textboxes next to each question. txtQuestion1, txtQuestion2,... are the variables that represent the data stored by those textboxes. With that said, here is the entire module (with the exclusion of the code for each textbox. I included only the first few of those at the end.


Sub gradeAssignment()
'
' Grading Macro
'
'CONSTANTS
Const numQuestions = 24

Const answer1 = "FA00"
Const answer2 = "100000"
Const answer3 = "Liquid Assets"
Const answer4 = "USD"
Const answer5 = "No"
Const answer6 = "A/P Sales Tax, exempt"
Const answer7 = "Document date"
Const answer8 = "Posting date"
Const answer9 = "ZGBS General Balance Sheet Accounts"
Const answer10 = "Yes"
Const answer11 = "no"
Const answer12 = "English, German"
Const answer13 = "Because GBI is a joint venture and multinational company between a German company and an American company"
Const answer14 = "FS00"
Const answer15 = "740000"
Const answer16 = "Profit & Loss Account"
Const answer17 = "Balance Sheet Accounts, Fixed Assets, Liquid Assets, Material Assets, Reconciliation Accounts"
Const answer18 = "USD"
Const answer19 = "Yes"
Const answer20 = "ZEXP (Expense Account)"
Const answer21 = "Screen Layout for document entry"
Const answer22 = "Revenue Accounts"
Const answer23 = "Outgoing Checks"
Const answer24 = "Displays in Cash Management"

Dim NumStudentErrors As Integer
NumStudentErrors = 0
Dim tempQuestion As String
'Dim txtQuestionxx As String
Dim tempAnswer As String
'Dim answerxx As String

'Determine current folder and store text file there
If UCase(Right(ActiveDocument.Name, 1)) = "X" Then '.docx
strFileName = Left(ActiveDocument.Name, Len(ActiveDocument.Name) - 5)
Else: '.doc
strFileName = Left(ActiveDocument.Name, Len(ActiveDocument.Name) - 4)
End If
pathname = ActiveDocument.Path & "\" & strFileName & "txt"

'write the txt file
Open pathname For Output As #1

'Loop to select sequential question and answer

For k = 1 To numQuestions

tempQuestion = "ActiveDocument.txtQuestion" & k & ".Text"
tempAnswer = "answer" & k

If Levenshtein(tempQuestion, tempAnswer) / Len(tempAnswer) > 0.25 Then NumStudentErrors = NumStudentErrors + 1
Print #1, k
Print #1, tempQuestion, tempAnswer
Print #1, Levenshtein(tempQuestion, tempAnswer)
Print #1, Levenshtein(tempQuestion, tempAnswer) / Len(tempAnswer)

Next k
Print #1, strFileName
Print #1, Number; missed And total; Points; by; student
Print #1, "Number of incorrect Answers = "; NumStudentErrors
Print #1, "Number of correct Answers = " & numQuestions - NumStudentErrors
Print #1, " "
Print #1, "Question #1 " & ActiveDocument.txtQuestion1.Text
Print #1, "Question #2 " & ActiveDocument.txtQuestion2.Text
Print #1, "Question #3 " & ActiveDocument.txtQuestion3.Text
Print #1, "Question #4 " & ActiveDocument.txtQuestion4.Text
Print #1, "Question #5 " & ActiveDocument.txtQuestion5.Text
Print #1, "Question #6 " & ActiveDocument.txtQuestion6.Text
Print #1, "Question #7 " & ActiveDocument.txtQuestion7.Text
Print #1, "Question #8 " & ActiveDocument.txtQuestion8.Text
Print #1, "Question #9 " & ActiveDocument.txtQuestion9.Text
Print #1, "Question #10 " & ActiveDocument.txtQuestion10.Text
Print #1, "Question #11 " & ActiveDocument.txtQuestion11.Text
Print #1, "Question #12 " & ActiveDocument.txtQuestion12.Text
Print #1, "Question #13 " & ActiveDocument.txtQuestion13.Text
Print #1, "Question #14 " & ActiveDocument.txtQuestion14.Text
Print #1, "Question #15 " & ActiveDocument.txtQuestion15.Text
Print #1, "Question #16 " & ActiveDocument.txtQuestion16.Text
Print #1, "Question #17 " & ActiveDocument.txtQuestion17.Text
Print #1, "Question #18 " & ActiveDocument.txtQuestion18.Text
Print #1, "Question #19 " & ActiveDocument.txtQuestion19.Text
Print #1, "Question #20 " & ActiveDocument.txtQuestion20.Text
Print #1, "Question #21 " & ActiveDocument.txtQuestion21.Text
Print #1, "Question #22 " & ActiveDocument.txtQuestion22.Text
Print #1, "Question #23 " & ActiveDocument.txtQuestion23.Text
Print #1, "Question #24 " & ActiveDocument.txtQuestion24.Text

Close #1
End Sub

Public Function Levenshtein(s1 As String, s2 As String)
Dim i As Integer
Dim j As Integer
Dim l1 As Integer
Dim l2 As Integer
Dim d() As Integer
Dim min1 As Integer
Dim min2 As Integer
l1 = Len(s1)
l2 = Len(s2)
ReDim d(l1, l2)
For i = 0 To l1
d(i, 0) = i
Next
For j = 0 To l2
d(0, j) = j
Next
For i = 1 To l1
For j = 1 To l2
If Mid(s1, i, 1) = Mid(s2, j, 1) Then
d(i, j) = d(i - 1, j - 1)
Else
min1 = d(i - 1, j) + 1
min2 = d(i, j - 1) + 1
If min2 < min1 Then
min1 = min2
End If
min2 = d(i - 1, j - 1) + 1
If min2 < min1 Then
min1 = min2
End If
d(i, j) = min1
End If
Next
Next
Levenshtein = d(l1, l2)
End Function


Code for Textboxes:

Private Sub txtQuestion1_Change()

Me.txtAnswer01.Value = Me.txtQuestion1.Value

End Sub

Private Sub txtQuestion2_Change()

Me.txtAnswer02.Value = Me.txtQuestion2.Value

End Sub

Private Sub txtQuestion3_Change()

Me.txtAnswer03.Value = Me.txtQuestion3.Value

End Sub

Private Sub txtQuestion4_Change()

Me.txtAnswer04.Value = Me.txtQuestion4.Value

End Sub


Sorry I am making this difficult for you to help me with this problem - it is not intentional - just my lack of knowledge and skills.

[fumei]

tempQuestion = "ActiveDocument.txtQuestion" & k & ".Text"


STOP using string literals!

tempQuestion = "ActiveDocument.txtQuestion" & k & ".Text"


MEANS - literally - ActiveDocument.txtQuestion, appended with k, appended with .Text

tempQuestion will ALWAYS return a value of "ActiveDocument.txtQuestion" & k & ".Text"

It is a string. NOT the value of the textbox. Stop putting quotes around it. You are stating that tempQuestion is - literally - "ActiveDocument.txtQuestion" & k & ".Text"

[fumei]

And I am with macropod on the question of what the heck txtQuestion actually is. A textbox in the document, and is that a legacy formfield. Is it a textbox on a userform. In either case the syntax is not good. And if it is on a userform it definitely is not good.