how can I create a Variable on the fly

mdlp
mdlp used Ask the Experts™
on
This is much like my last question, I am sorry, for asking questions that I should be able to find the answers to, but I do not quite know the correct descriptors to get them.  

So I have 20 variables;
dim  sP1FieldName ,  sP2FieldName,  sP3FieldName  to sP20...
dim i as integer
i = 1

Do While i < 21
 sCurrValue = getDateField(i)
   ' getDateField() is a function that gets the date for field 1 this all works correctly
   ' now I want to recreate the correct variable that I created above with the text
   'and the i number

 "sP" & str(i) & "FieldName"  = sCurrValue  'This is where I am having the problem

   i = i + 1
   Loop

Thank you experts

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,

You are very close in your thinking - let me nudge you in the right direction.

Instead of using named variables, use an array of variables! An array will give you exactly what you're looking for - the ability to dynamically add/remove information and can also be easily shrunk or enlarged.

Here is your code formatted for an array:
Dim sPxFieldName(20)
Dim i As Integer

i = 1

Do While i < 21
    sCurrValue = getDateField(i)
    ' getDateField() is a function that gets the date for field 1 this all works correctly
    ' now I want to recreate the correct variable that I created above with the text
    'and the i number
    sPxFieldName(i) = sCurrValue
    i = i + 1
Loop


' To keep things tidy, you could also do this:

For i = 1 To 20
    sCurrValue = getDateField(i)
    sPxFieldName(i) = sCurrValue
Next i

' Even shorter, if you are not using sCurrValue for anything other than
' intermediary storage:

For i = 1 To 20
    sPxFieldName(i) = getDateField(i)
Next i

Open in new window


One word of caution: be careful for "off by one" bugs: where your counter value (i) may exceed the bounds of your array! In VBA, arrays elements start at zero an go do your "size - 1", unless you use the Option Base statement. Thus, an array declared as Dim X(20) technically has elements numbered: X(0), X(1) ... X(19). VBA is somewhat lenient and will actually allow you to use X(20). Just keep an eye on this in your future endeavors!
Top Expert 2014
Commented:
In addition, you can specify both the lower and upper bounds of an array
Dim sPxFieldName(1 to 20)
Dim sPxFieldName(16 to 35)

Open in new window



You can also dynamically resize the array.  In this case, the array is being resized to the number of days in the current month.
Dim sPxFieldName()




ReDim sPxFieldName(1 to Day(Dateadd("m", 1, Date) - Day(Date)))

Open in new window

Author

Commented:
Thank you experts, I see that this can work, but I am trying to get my hands around the use of an array and not a variable, it is a reflection on my skill set not on your answers.  Please do not post any other answers until I can get this to work in my code.  I hate splitting point because it always seems that I get it wrong.

I am back and working on this project this morning.
Top Expert 2009

Commented:
Here are a few examples of using arrays:
Public Sub WriteCountiesToLinkTable()
'Created by Helen Feddema 24-May-2009
'Last modified 25-May-2009

On Error GoTo ErrorHandler
  
   Dim intCountyNo As Integer
   Dim intUBound As Integer
   Dim rstMaster As DAO.Recordset
   Dim rstCounties As DAO.Recordset
   Dim strCounties As String
   Dim strCountyArray() As String
   Dim strSearch As String
   Dim strCountyName As String
   
   'Clear table
   'strSQL = "DELETE * FROM tlnkCounties;"
   'DoCmd.SetWarnings False
   'DoCmd.RunSQL strSQL
   
   Set dbs = CurrentDb
   Set rstMaster = dbs.OpenRecordset("tblMasterDatabase")
   Set rstCounties = dbs.OpenRecordset("tlnkCounties")
   
   'Get counties from each record in master table, and write
   'them to link table
   ReDim strCountyArray(76)
   
   Do While Not rstMaster.EOF
      If Nz(rstMaster![County]) = "" Then
         GoTo NextRecord
      Else
         'Create an array of counties for this record
         strCounties = rstMaster![County]
         'Debug.Print "Counties: " & strCounties
         strCountyArray = Split(expression:=strCounties, _
            delimiter:=",", _
            limit:=-1, _
            Compare:=vbTextCompare)
         intUBound = UBound(strCountyArray)

         For intCountyNo = 0 To intUBound
            If strCountyArray(intCountyNo) = "" Or _
               strCountyArray(intCountyNo) = "Various statewide" Then
               GoTo NextRecord
            Else
               strCountyName = Trim(strCountyArray(intCountyNo))
               'Debug.Print "County name: " & strCountyName
            End If
            strSearch = "[COUNTY] = " & Chr(39) _
               & strCountyName & Chr(39)
            'Debug.Print "Search string: " & strSearch
            Debug.Print "About to add a record for Record ID " _
               & rstMaster![RecordID] & " and County ID " _
               & DLookup(expr:="[CountyID]", _
               domain:="tlkpCounties", criteria:=strSearch)
            rstCounties.AddNew
            rstCounties![RecordID] = rstMaster![RecordID]
            rstCounties![CountyID] = Nz(DLookup(expr:="[CountyID]", _
               domain:="tlkpCounties", criteria:=strSearch))
            rstCounties.Update
         Next intCountyNo
      End If

NextRecord:
      rstMaster.MoveNext
   Loop
   
ErrorHandlerExit:
   rstMaster.Close
   rstCounties.Close
   
   Exit Sub

ErrorHandler:
   If Err.Number = 3058 Then
      strTitle = "Name wrong"
      strPrompt = "Please check the county name; " & strCountyName _
         & " not found in counties table"
      MsgBox prompt:=strPrompt, _
         buttons:=vbExclamation + vbOKOnly, _
         Title:=strTitle
      GoTo ErrorHandlerExit
   Else
      MsgBox "Error No: " & Err.Number _
         & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If
   
End Sub

Public Sub SplitSKUs(strSKUs As String)

   Dim strSKUArray() As String
   Dim intUBound As Integer
   Dim i As Integer
   
   'Set up array of split items
   strSKUArray = Split(strSKUs, "*", -1, vbTextCompare)
   intUBound = UBound(strSKUArray)
   
   For i = 0 To intUBound
      Debug.Print strSKUArray(i)
   Next i
   
End Sub

Open in new window

Author

Commented:
Thank you, I hate to say it but I could not get it to work for me, but you have brought arrays back to the "front of mind" for me.  I have used arrays in the past but only to pass data through.

Helen thank you for the additional information, but since I posted I feel it would be wrong to award you points even thought you gave great input.  

Thank you all  again,
mdlp

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial