Geek Answers Handbook

How to get / set a unique identifier for a cell in Excel via VBA

I want to have / define a unique identifier for each row of data in an Excel data sheet, so that I can use it when transferring data forward, and it stays the same when rows are added / deleted above it.

My thoughts is to use the ID Range attribute ( msdn link )

So, I have a user-defined function (UDF) that I put on every line that gets / sets the identifier as follows:

Dim gNextUniqueId As Integer

Public Function rbGetId(ticker As String)
    On Error GoTo rbGetId_Error
    Dim currCell As Range
    'tried using Application.Caller direct, but gives same error
    Set currCell = Range(Application.Caller.Address)
    If currCell.id = "" Then
        gNextUniqueId = gNextUniqueId + 1
        'this line fails no matter what value I set it to.
        currCell.id = Str(gNextUniqueId)
    End If
    rbGetId = ticker & currCell.id
    Exit Function

    rbGetId_Error:
    rbGetId = "!ERROR:" & Err.Description
End Function

But this fails in the line specified in

"User Defined or Object Error"

I thought maybe this is one of these UDF limitations, but I also get the same error if I try it from code run using the ribbon button ...

, - , , / ...

EDIT: Ant, , . ... "Protect UserInterFaceOnly: = True", . " ", , , - Protect AutoOpen, UserInterfaceOnly...

, / - , UDF... , , , - ActiveSheet.unprotect, ActiveWorkbook.unprotect: (

.

+5
5

, "Protect DrawingObjects: = False", UDF . .

.

0

...

, , , .

, , UDF. UDF ; , , , , . . - Microsoft.

, . :

  • UDF, Worksheet_Change ID
  • UDF, ID , ID

UDF , -, , .

, UDF, "" , . ( ).

Public Function CellMark()

    Dim currCell As Range
    Set currCell = Range(Application.Caller.Address)

    Dim myId As String
    ' must be text; using .value will cause the formula to be called again
    ' and create a circular reference
    myId = currCell.Text

    If (Trim(myId) = "" Or Trim(myId) = "0") Then
       myId = "ID-" & Format(CStr(gNextUniqueId), "00000")
       gNextUniqueId = gNextUniqueId + 1
    End If

    CellMark = myId

End Function
, . . . ( , ENTER), - .

, Worksheet_Change - , . , . . ​​ , .

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim currCell As Range
    Set currCell = Target.Cells(1, 1)

    Dim currId As String
    currId = currCell.ID

    If Trim(currCell.ID) = "" Then
        Target.Parent.Unprotect
        currCell.ID = CStr(gNextUniqueId)
        Target.Parent.Protect
        gNextUniqueId = gNextUniqueId + 1
    End If

End Sub

; ID reset, ( , , ).

, .

+4

Ant - Excel 2003 SP3.

:

Set currCell = Application.Caller
If Application.Caller.ID = "" Then
    gNextUniqueId = gNextUniqueId + 1
    'this line fails no matter what value I set it to.
    currCell.ID = Str(gNextUniqueId)
End If

! , .

, , ONCE . - . , Application.Caller.ID , Range ( "A1: B9" ). ID Application-defined or object-defined error.

Range(Application.Caller.Address) "", currCell.ID.

+1

, , , , . -, -, , , , .. Application.Caller . , , . -, / , , / . , , . ( ), , . ( Application.Caller.)

+1

Application.Caller.

, . .

Visual Basic, .

Caller -

  • , - Range, ,
  • , - Range, ,
  • Auto_Open, Auto_Close, Auto_Activate Auto_Deactivate macro -
  • , OnDoubleClick OnEntry. ( ), .
  • ( "" ) , . #REF!

, , , Application.Caller curCell . , . , curCell, . curCell = Range ( " 2023" ). , , , .

...

  • , VBA Range (Application.Caller.Address). , .

  • , Application.ActiveCell, , , , . , ( "A1" ) (1,1). Application.Caller.Address .

  • Option Explicit. , curCell, , Range (Application.Caller.Address) , , curCell.

+1

More articles:


All Articles