[RESOLVED] Error on filling ComboBox from Excel named range.

I'm trying to fill a Combobox with values from a named range in excel and can't figure out why I'm getting an error in the For loop. Here's my code:

Code:

---

  Public Sub FillAlbumStatusCombo(ByVal Excel_VSA_NEW As Class_Excel_Files)

        ComboBox_VsaStatus.Items.Clear()

        Excel_VSA_NEW.OWS = Excel_VSA_NEW.OWBa.Worksheets("INTERNAL")

        For Each cell As Excel.Range In Excel_VSA_NEW.OWS.Range("rng_VsaStatus_main").Cells
            ComboBox_VsaStatus.Items.Add(cell.Value.ToString)
        Next

    End Sub

---

I also tried selecting the range which also gives an error.

Code:

---

Excel_VSA_NEW.OWS.Range("rng_VsaStatus_main").Select()

---

Attachment 179959

Error messages are text and should be posted as text. We shouldn't have to resize an image and try to read the blurry screenshot when, if I'm not mistaken, the Exception Assistant window even has a Copy link on it.

When any exception that you're not sure about is thrown on a compound statement, the very first thing to do is to break up the statement into parts and see exactly where the exception is thrown. That would mean getting the Range on one line, then getting the Cells from that on the next, then doing the For Each loop.

I don't really do Excel Automation but it seems strange to me that you would loop through the Cells and get Range objects. Are you sure that's correct?

The error:

System.Runtime.InteropServices.COMException
HResult=0x800A03EC
Message=Exception from HRESULT: 0x800A03EC
Source=<Cannot evaluate the exception source>
StackTrace:
<Cannot evaluate the exception stack trace>

Quote:

---

Originally Posted by jmcilhinney

When any exception that you're not sure about is thrown on a compound statement, the very first thing to do is to break up the statement into parts and see exactly where the exception is thrown. That would mean getting the Range on one line, then getting the Cells from that on the next, then doing the For Each loop.

I don't really do Excel Automation but it seems strange to me that you would loop through the Cells and get Range objects. Are you sure that's correct?

---

All other actions I do on a named range while using column and row works fine. So this is like your "break up" suggestion. I am now looping because I want to fill a combo box with all the values of the range (which I haven't done before)

So, for example, this works fine and I'm able to SET a single value to a cell within the named range:

Code:

---

                    With Excel_VSA_NEW.OWS
                        .Cells(RowUsed, .Range("rng_VsaStatus_main").Column) = AlbumStatus
                    End With

---

But if I try to break up what you said, this for example, throws the same error:

Code:

---

MsgBox(Excel_VSA_NEW.OWS.Range("rng_VsaStatus_main").Cells(1, 1).ToString)

---

UPDATE:

If I gave an absolute range address - it works. So, this works and fills the combo box fine:

Code:

---

    Public Sub FillAlbumStatusCombo(ByVal Excel_VSA_NEW As Class_Excel_Files)

        ComboBox_VsaStatus.Items.Clear()

        Excel_VSA_NEW.OWS = Excel_VSA_NEW.OWBa.Worksheets("INTERNAL")

        For Each cell As Excel.Range In Excel_VSA_NEW.OWS.Range("F2:F11")
            ComboBox_VsaStatus.Items.Add(cell.Value.ToString)
        Next

    End Sub

---

So I just need to figure out why it's throwing an error when I use the named range "rng_VsaStatus_main" instead of the absolute address.

SOLVED:

Sorry guys, stupid mistake.

I was using the wrong named range. I was using rng_VsaStatus_main which is in the MAIN worksheet.
Changed it to rng_VsaStatus_internal (from the INTERNAL worksheet) and it works fine.

I just wish that the error message would have mentioned something like "no such range found" or something more clear.

Thank you for trying to help.