by Ryan Rentfro (1 Submission)
Category: Microsoft Office Apps/VBA
Compatability: Visual Basic 3.0
Difficulty: Advanced
Date Added: Wed 3rd February 2021
Rating: (7 Votes)
Retrieves Excel spreadsheet cell information based on a set of parameters sent to the function. It then sets the value of the variable assigned when retrieveing the information.
Inputs
Row value, column value, and file path.
Assumes
Object programming and undertanding of functions.
Code Returns
The value of the set cell
Side Effects
If you use this function to retrieve massive amounts of data it is best to have excel open when doing so. I have computated 7 years of stock values and then averaged all values retrieved from excel. It takes about 75% longer to retrieve information when excel is closed.
Function getExcel(rowval As Integer, columnval As String, excelfile As String)
Dim excelSheet As Object 'Excel Sheet object
'Create an instance of Excel by file name
Set excelSheet = CreateObject(excelfile)
mycell$ = columnval & rowval
getExcel = excelSheet.activesheet.range(mycell$).Value
'Retrieve the result using the cell by row and column
Set excelSheet = Nothing 'release object
End Function