C# thowing NullReferenceException when trying to access Excel cells

I am trying to access data in cells from excel and put it into an array in C#. I do this for two different sheets and use the exact same syntax for both. One works perfectly fine and loads all the data correctly. The other throws a NullReferenceException saying that the range value is null. It seems to be getting the sheet assigned correctly and the col and row values are valid that have data in them on the spreadsheet so I hav no idea how one function can work and the other can't. The code is attached below.
public void loadForwardVolArray(ref Microsoft.Office.Interop.Excel.Workbook treeBook)
        {
            Microsoft.Office.Interop.Excel.Worksheet forwardSheet;
            forwardSheet = (Microsoft.Office.Interop.Excel.Worksheet)treeBook.Worksheets.get_Item("Forward Volatility");
            string name = forwardSheet.Name;
            int index = forwardSheet.Index;
            int row = 5;
            int col = 2;
            for (int k = 0; k < 120; k++)
            {
               
                    _forwardVolArray[k].adjusted = Convert.ToDouble(((Microsoft.Office.Interop.Excel.Range)forwardSheet.Cells[row, col + 3]).Value2.ToString());
                    _forwardVolArray[k].choppy = Convert.ToDouble(((Microsoft.Office.Interop.Excel.Range)forwardSheet.Cells[row, col + 1]).Value2.ToString());      //Choppy
                    _forwardVolArray[k].month = Convert.ToInt32(((Microsoft.Office.Interop.Excel.Range)forwardSheet.Cells[row, col]).Value2.ToString(), 10);         //Month
                    _forwardVolArray[k].smoothed = Convert.ToDouble(((Microsoft.Office.Interop.Excel.Range)forwardSheet.Cells[row, col + 2]).Value2.ToString());    //Smoothed
                    
               
                row++;
            }
        }

Open in new window

kbreaux25Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
imitchieConnect With a Mentor Commented:
good on you
0
 
imitchieCommented:
try this pattern instead

                    _forwardVolArray[k].adjusted = Convert.ToDouble(((Microsoft.Office.Interop.Excel.Range)forwardSheet.Cells[row, col + 3]).Text);
saves you doing a tostring
0
 
kbreaux25Author Commented:
I tried the changes instead of null showing up when i put the cursor over it with the old syntax the correct value shows up, yet it is still throwing the exception. Any ideas?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
imitchieCommented:
Actually, the TEXT is being cast to a Range..
try this instead

        _forwardVolArray[k].adjusted = Convert.ToDouble( ( forwardSheet.Cells[row, col + 3] ).Text );
0
 
kbreaux25Author Commented:
When i try that syntax I get an error that 'object' does not contain a definition for 'Text'. I still don't understand my old syntax would work for one sheet but not a different one, is there another issue to consider?
0
 
imitchieCommented:
>>I tried the changes instead of null showing up when i put the cursor over it with the old syntax the correct value shows up, yet it is still throwing the exception. Any ideas?

Is it still the same exception or another one? I'm assuming that you are making the same change to all 4 statements. Try this one

string dummy;
    dummy = ((Microsoft.Office.Interop.Excel.Range)forwardSheet.Cells[row, col + 3]).Text;
    _forwardVolArray[k].adjusted = dummy != "" ? Convert.ToDouble( dummy ) : 0;

and check dummy after assignment.
0
 
kbreaux25Author Commented:
the above syntax correctly assigns the value to dummy, but when i try to assign it to the array i get an exception
0
 
kbreaux25Author Commented:
just substituting in _forwardVolArray[k].adjusted = 5.0  throws the same error so its not a syntax problem. I just can't seem to figure out why this is occurring I've initialized the array and everything.
0
 
imitchieCommented:
I think your last comment is quite enlightening:
_forwardVolArray[k].adjusted = 5.0  << error
That puts the blame squarely on _forwardVolArray.  I'm guessing k is outside the range of the array.  Check the array length
0
 
kbreaux25Author Commented:
I wasn't initializing the array correctly.I'm new to c# and didnt' realize I needed to initialize every object in an array separately. Now it is working correctly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.