vba code is treating a constant stored as a defined name as a string

[Rob Webster]

Hi all,

I have a constant stored in my workbook as a defined name, the constant isn't in a cell.

i'm trying to use the constant as the start number, from which to count the number of times that a loop is performed. so that I have a record of how many times the loop has passed in total in the file (regardless of how many times it has been opened saved closed etc). the ending number (when the code has finished) is then used/saved as the constants new value for next time the code runs.

the object is to count the total number of times that the loop has run regardless of how many times the sub procedure that the code is in has been run and to store it in the file, but not in a cell so that it doesn't get overwritten by mistake.

the problem is that on first run, the value in the name is zero. visual basic seems to read this as "=Empty" as a string rather than zero. so cant add the numbers to it. if Istart the counter at =1, again visual basic reads it as a string. I've tried to use the worksheet function.substitute, to remove the "=" and then cLng to convert the remainder of the string to its value but with no luck. can any one offer any suggestions?

sorry new to forum and trying to get to grips with how to tag code

Dim IDstr As String
Dim IDtxtno As String
Dim IDCount As Long
Dim IDLen As Long

  IDstr = ActiveWorkbook.Names("VAL_ID").Value
  IDLen = Len(Names("VAL_ID").Value)

  If IDstr = "=0" Then

  IDCount = 0

  Else


  IDtxtno = WorksheetFunction.Right(IDstr, (IDLen - 1))
  IDCount = WorksheetFunction.Value(IDtxtno)

  End If

 

[Hui]

Rob

Not really sure what your trying to do but your confusing yourself with nomenclature

Try:

Sub h()

Dim IDstr As String
Dim IDtxtno As String
Dim IDCount As Long
Dim IDLen As Long

IDstr = Right(ActiveWorkbook.Names("VAL_ID"), Len(ActiveWorkbook.Names("VAL_ID")) - 1)
IDLen = Len(IDstr)


If IDstr = "0" Then
  IDCount = 0
Else
  IDtxtno = Right(IDstr, (IDLen - 1))
  IDCount = IDstr

End If
End Sub

ps: I don't recommend storing constants like this, it is much simpler to simply store them in a cell, on a hidden worksheet if required

 

[Rob Webster]

Thanks for your reply Hui,

maybe didn't explain it very well sorry.
the line you used below, doesn't work because the value isn't in a range. I've used a name to store a numeric constant. rather than putting it in a cell.

IDstr = Range("VAL_ID").Value
[/code/

however I thank you anyway because on reviewing your suggestion I was able to work out a solution. which I add below.

[code]
  Dim IDstr As String
  Dim IDtxtno As String
  Dim IDCount As Long
  Dim IDLen As Long
 
 
  IDstr = ActiveWorkbook.Names("VAL_ID").Value
  IDLen = Len(Names("VAL_ID").Value) - 1
 
  If IDstr = "=0" Then
 
  IDCount = 0
 
  Else

  IDtxtno = Right(IDstr, IDLen)
  IDCount = CLng(IDtxtno)
 
  End If

this is a small section of the overall code which provides a starting number from which to count from.

the counter ultimately provides a unique identifier for a list of transactions in a table but the entries are added to it randomly, (not always just to the bottom. so the code loops through the whole list, leaving the ones with identifiers as they are, and adding a new id to the new ones.

once the whole list has been checked the last number used in IDcount, is stored as the new value of VAL_ID, so that the next time the routine is run it doesn't start again at zero.

this helps to sort them into order that they were added to the list and also to spot if any have been deleted as the numbers are not re-used.

I didn't want to store the number in the worksheet itself as it is too easy for other users to accidentally change it.

thanks very much for your reply and I hope this clears up the confusion of what I was trying to do.

 

[Debaser]

Wouldn't it be easier to use:

IDCount = [VAL_ID]

 

[Hui]

My formula's don't refer to a range ?

 

[Rob Webster]

Wouldn't it be easier to use:

IDCount = [VAL_ID]

yes it would, (and is! thank you) that's precisely what I was trying to achieve. when I tried first time around it wouldn't accept the number as a number it was treating it as a text string i.e "=39" not 39, so the rest of my code couldn't add 1 to it. I missed the square brackets, didn't know you could refer to ranges in that way.

thanks very much for your repy.

 

[Rob Webster]

My formula's don't refer to a range ?

sorry the line of code that you used refers to the name as if it is a "range" held in a cell on a worksheet. I tried to use it but it failed at that line. I do usually store constants in cells but wanted this particular one more difficult to spot/change/ delete in error - by casual users of the sheet.

the IDcount was intended to = the Val_id name but I couldn't get it to read it as a number hence the convoluted method I used to get there.

you have both been a great help