title: How to get/set unique id for cell in Excel via VBA
body: <p>I want to have/define a unique id for each data row in my Excel data sheet - such that I can use it when passing the data onwards and it stays the same when rows are added/deleted above it.</p>
  
  <p>My thoughts are to use the ID attribute of Range (<a href="http://msdn.microsoft.com/en-us/library/bb213537.aspx" rel="noreferrer">msdn link</a>)</p>
  
  <p>So, I have a user defined function (UDF) which I place in each row that gets/sets the ID as follows:</p>
  
  <pre><code>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 &amp; currCell.id
      Exit Function
  
      rbGetId_Error:
      rbGetId = "!ERROR:" &amp; Err.Description
  End Function
  </code></pre>
  
  <p>But this fails at the line mentioned with </p>
  
  <blockquote>
    <p>"Application-defined or object-defined error"</p>
  </blockquote>
  
  <p>I thought perhaps its one of those limitations of UDFs, but I also get the same error if I try it from code triggered from a ribbon button...</p>
  
  <p>Any other suggestions on how to keep consistent ids - perhaps I should populate the cells via my ribbon button, finding cells without IDs and generating/setting the cell value of those...</p>
  
  <p>EDIT:
  As Ant thought, I have the sheet protected, but even in an unlocked cell it still fails.  Unprotecting the sheet fixes the problem.... but I have used "Protect UserInterFaceOnly:=True" which should allow me to do this.  If I manually allow "Edit Objects" when I protect the sheet it also works, but I don't see a programmatic option for that - and I need to call the Protect function in AutoOpen to enable the UserInterfaceOnly feature...</p>
  
  <p>I guess I need to turn off/on protect around my ID setting - assuming that can be done in a UDF... which it seems it cannot, as that does not work - neither ActiveSheet.unprotect nor ActiveWorkbook.unprotect  :(</p>
  
  <p>Thanks in advance.
  Chris</p>