CODEWITHSUNDEEP
excelvb.net
Marc Kane
Marc Kane

Reputation: 13

How to display value from excel sheet?

I'm writing code that will select a random row from excel and then display values from every column in that row. The problem is that all the values in msgbox turn out to be 0 even if they are defined differently in excel. I'm probably missing some point in my code.

xlApp = New Excel.Application
xlWorkbooks = xlApp.Workbooks.Open("C:\Values.xlsx")
xlsheet = CType(xlWorkbook.Sheets("Sheet1"), Excel.Worksheet)
xlsheet.Activate()
xlApp.Visible = True

Dim firstrow As Long, lastrow As Long, Rndrow As Long
firstrow = 2
lastrow = GetFirstEmptyRow(xlsheet) - 1
Rndrow = Int((lastrow - firstrow + 1) * Rnd() + firstrow)
xlCurRow = Rndrow

minA = xlsheet.Range("B" & xlCurRow).Value
MessageBox.Show(minA)
maxA = xlsheet.Range("C" & xlCurRow).Value
MessageBox.Show(maxA)
minB = xlsheet.Range("D" & xlCurRow).Value
MessageBox.Show(minB)
maxB = xlsheet.Range("E" & xlCurRow).Value
minC = xlsheet.Range("F" & xlCurRow).Value
maxC = xlsheet.Range("G" & xlCurRow).Value

Private Function GetFirstEmptyRow(xlsheet As Worksheet, Optional sColName As String = "A") As Long
    GetFirstEmptyRow = xlsheet.Range(sColName&xlsheet.Rows.Count).End(XlDirection.xlUp).Row + 1
End Function

Upvotes: 0

Views: 175

Answers (1)

djv
djv

Reputation: 15772

Don't use Rnd(). Well if you must, then you must also call Randomize() first

Randomize()
Rndrow = Int((lastrow - firstrow + 1) * Rnd() + firstrow)

otherwise you will get the same value every run. You were getting 0. I was getting 12. It will be different on different machines.

Instead use the Random class

Dim r As New Random()
Rndrow = Int((lastrow - firstrow + 1) * r.NextDouble() + firstrow)

Also, your code doesn't compile with Option Strict On. You should put that at the top of your file first. (Try not to make your code do implicit conversions whenever you can provide the correct types. It adds unnecessary processing.) Then make these changes

' return an Integer instead of Long
' Range.Row is Integer so this can never be Long unless you have 2 billion rows
Private Function GetFirstEmptyRow(xlsheet As Worksheet, Optional sColName As String = "A") As Integer
    ' use the VB.NET syntax to return, not VBA style
    ' concatenate non-string values with interpolation which performs ToString() implicitly
    Return xlsheet.Range($"{sColName}{xlsheet.Rows.Count}").End(XlDirection.xlUp).Row + 1
End Function

Dim r As New Random()
Dim firstrow = 2
Dim lastrow = GetFirstEmptyRow(xlsheet) - 1
' must cast to some integral number here, Integer is fine
Dim xlCurRow = CInt((lastrow - firstrow + 1) * r.NextDouble() + firstrow)

' again string concatenation when dealing with non-strings
Dim minA = xlsheet.Range($"B{xlCurRow}").Value
' minA is an object
' MessageBox.Show(text As String) takes a string, not an object
MessageBox.Show(minA.ToString())
' or MessageBox.Show($"{minA}")

I guess you're coming from VBA or VB6. The Rnd class and returning from a function by assigning to the function name will work in VB.NET, but are not recommended.

Upvotes: 3

Related Questions