0

I am trying to create a do loop that will go to the range of my sheet ("A") and retrieve the value and put it to i variable. I want to run this loop until I hit an empty row. Right now I am using Is "", if I use Do Until unique_numbers.Range("A" & i) = Empty I get this error (). I am copying over a VBA script to vb for efficiency purposes (very new to this).

Dim xlNewSheet = DirectCast(worksheets.Add(worksheets(1), Type.Missing, Type.Missing, Type.Missing), Excel.Worksheet)
        xlNewSheet.Name = "Unique Numbers"
        Dim unique_numbers As Object
        unique_numbers = xlWorkBook.Sheets("Unique Numbers")

Dim i As Integer
i = 2 'Starting from two since the first row is the header
Do Until unique_numbers.Range("A" & i) Is ""
unique_numbers.Range("A" & i) = convert_to_acronym(unique_numbers.Range("A" & i))
Loop

The sub function I am trying to call in order to manipulate data in column A:

Private Sub convert_to_acronym(hex_value As String)

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim convert_to_acronym
        Dim sym As Object
        sym = xlWorkBook.Sheets("sym")

        Dim k As Integer : k = 0
        Dim found As Integer : found = 0
        Dim tmp As String
        Dim current_string As String
        Dim name_start As Integer

        'If the trace has 0, then convert it to "0000" which is a better format for parsing the data
        If hex_value = "0" Then
            hex_value = "0000"
        End If

        'If the first character of the string is < "F", then the message is point.
        'reconstruct the string by taking the first two characters and adding "FE" before comparing it with sym.
        If LBound(hex_value.ToArray, 1) < "F" Then
            hex_value = LBound(hex_value.ToArray, 2) & "FE"
        End If

        Do While found = 0
            k = k + 1
            current_string = InStr(sym.Range("A"), k)
            name_start = InStr(current_string, hex_value)
            If name_start = 6 Then
                found = 1
            End If
            If k = 32766 Then
                'To do: current_string was not found
                found = 1
            End If

        Loop

        tmp = sym.Range("A" & k - 1)
        tmp = Replace(tmp, "[", "")
        tmp = Replace(tmp, "]", "")

        convert_to_acronym = tmp

    End Sub

When my application crashes it actually highlights the second line with the title of this question. I assume the Do Until line is causing this, I could be wrong.(convert_to_acronym) references a Module I have created that contains logic to convert HEX value to an Acronym.

Lord45
  • 65
  • 8
  • Are you using VB.NET, VB6, or Excel VBA? Also, do you know about the WorkSheet.UsedRange property? – SSS May 24 '22 at 00:48
  • @SSS, I am using vb.net. Originally I created a macro (Excel VBA), but it would crash my computer due to sheets having a lot of rows - so I am in the middle of rewriting in vb within visual studios. I do not know about the WorkSheet.UsedRange - I will look this up! – Lord45 May 24 '22 at 02:17
  • 1
    Where is `unique_numbers` defined? – Étienne Laneville May 24 '22 at 02:35
  • Does this answer your question? [What is a NullReferenceException, and how do I fix it?](https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) – Craig May 24 '22 at 13:23
  • @ÉtienneLaneville, I have added the code where I defined unique_numbers. It is a new sheet I created in a workbook and added data to column A. Now I am trying to manipulate that data and return the value in column B. convert_to_acronym is the module I have. – Lord45 May 24 '22 at 13:40
  • @Craig, this was helpful. I think the issue is translating my code from vba to vb. The second line with convert_to_acronym which points to my private sub function is not returning a value (yet). I don't get an error with vba, but vb requires definition. I am attaching my sub function, this might allow people to help me with my logic on manipulating data in column A and returning value in column B. There is probably an easier way in vb, I am just trying to figure that out. – Lord45 May 24 '22 at 14:04
  • If you're getting a nullrefexception for `unique_numbers` in the do loop, then it seems like there may be a problem with getting the worksheet. If you step through the code in the debugger, you can confirm whether you get an object back when you ask for the worksheet. – Craig May 24 '22 at 14:16
  • I don't understand, though, why it is that you're re-retrieving the sheet when you already have a reference to it in `xlNewSheet`. – Craig May 24 '22 at 14:16

0 Answers0