CODEWITHSUNDEEP
vbaexcel
hdk857
hdk857

Reputation: 81

Static Variables in VBA

I have an excel workbook where the user imports text files into a "Data Importation Sheet". The number of files imported is dependent on how files the user wants to import. So far my workbook works great but I have hit one bump. When the user imports a file an identifier (i.e. 1, 2, 3, etc) gets assigned to that data set. Then the user selects an option from a dropdown box and calculations and plots will automatically be produced. The user also has the option to "Clear all data" where when this is selected all worksheets are cleared and if the user imports new files (after clicking the "clear all data" button) the identifier value restarts at 1. Here is my code for the identifier/counting how many files have been imported..

Public Sub Macro(Optional reset As Boolean = False)

Static i As Integer
If reset Then
i = -1
i = i + 1
Exit Sub
End If

i = i + 1

Worksheets("Hidden").Cells(i + 1, 1).FormulaR1C1 = "=" & i

Worksheets("Hidden").Cells(2, 2).FormulaR1C1 = "=" & i

End Sub

The problem I have ran into now is data will need to be imported into this sheet at a later date so when I save this file and reopen it then import more files the identifier/count for file imports restarts at 1 which I do not want to happen. I want to be able to just keep adding more files and have the code continue, I do not want to have to clear all the imported data and restart. Any ideas as to how I can do this? TIA.

Upvotes: 0

Views: 2435

Answers (3)

Kostas K.
Kostas K.

Reputation: 8518

You could also create a CustomDocumentProperty to save the sequence number. You can pass a boolean to the method to reset:

Lastly, a helper function will check if the property exists, in order to be added if not.

Public Sub SequenceNumber(Optional ByVal Reset As Boolean = False)
    If Not PropertyExists("Identifier") Then
        ThisWorkbook.CustomDocumentProperties.Add Name:="Identifier", _
                                                  LinkToContent:=False, _
                                                  Type:=msoPropertyTypeNumber, _
                                                  Value:=0
    End If

    Dim p As Object
    Set p = ThisWorkbook.CustomDocumentProperties("Identifier")

    If Reset Then p.Value = 0 Else p.Value = p.Value + 1
End Sub

'Property Exists?
Private Function PropertyExists(ByVal propertyName As String) As Boolean
    Dim p As Object
    For Each p In ThisWorkbook.CustomDocumentProperties
       If p.Name = propertyName Then
           PropertyExists = True
           Exit Function
       End If
    Next p
End Function

To call it:

SequenceNumber
SequenceNumber Reset:=True

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166331

I'd create a standalone function to manage the sequence. Store the value in a Workbook Name entry.

Note - if you had to manage multiple sequences you could promote the name of the sequence to a parameter instead of using a Constant within the Function.

Function NextSequence(Optional reset As Boolean = False)
    Const COUNTER_NAME As String = "NM_COUNTER"
    Dim nm As Name, i

    On Error Resume Next
    'is the name already created?
    Set nm = ThisWorkbook.Names(COUNTER_NAME)
    On Error GoTo 0

    If nm Is Nothing Then
        'not there yest - create it...
        Set nm = ThisWorkbook.Names.Add(COUNTER_NAME, 0)
    End If

    If Not reset Then
        i = Evaluate(nm.RefersTo)
        i = i + 1
        nm.RefersTo = i
    Else
        nm.RefersTo = 0 
        i = 0 '<< or 1 if you want NextSequence(True) to
              '   return the first sequence value
    End If

    NextSequence = i
End Function

Usage:

Public Sub Macro(Optional reset As Boolean = False)

    Dim i 
    i = NextSequence(reset)
    If reset Then Exit Sub

    With Worksheets("Hidden")
        .Cells(i + 1, 1).Value = i
        .Cells(2, 2).Value = i
    End With

End Sub

Upvotes: 1

MisterBic
MisterBic

Reputation: 307

A quick fix for this would be to store the value of the identifier/count inside a cell and hide/lock the cell. The value inside the cell won't change upon restart yet you still can manipulate it inside VBA.

Very quick feel of how it should look like (probably innacurate as I don't have every info I need.)

Public Sub Macro(Optional reset As Boolean = False)

Static i As Integer
i = ActiveWorkBook.Sheets("Ressource").Range("A1").Value
If reset Then
i = -1
i = i + 1
Exit Sub
End If

i = i + 1

Worksheets("Hidden").Cells(i + 1, 1).FormulaR1C1 = "=" & i

Worksheets("Hidden").Cells(2, 2).FormulaR1C1 = "=" & i

End Sub

Upvotes: 0

Related Questions