How to apply value returned by a vba sub to the active cell in an Excel 2010 workbook
Posted on 2015-02-24
Using: Excel 2010
I have a subroutine which correctly displays the value I need through the following command:
Instead of this value being displayed in (of course) a Message Box, how can I assign the value returned by 'ExecuteExcel4Macro(returnedValue)' to the active cell.
Another related piece of info:
At the moment (I'm still developing), I'm hard coding the external workbook name in the sub which is passed to ExecuteExcel4Macro. I just wanted to see if I could get the sub to return a cell value from a closed workbook on a network drive. It does, but as it's coded, it's returning the value in a msgBox as noted above. I need it to go into a cell in the workbook.
I have a master workbook that calls this sub, which creates a table by pulling in several hundred workbook filenames (let's say they are in Column A and the pulled-in workbooks are called: extWB1.xlsx, extWB2.xlsx, etc.) from a folder on the network drive and hyperlinks the filenames. The workbooks all have identical structures but each contain different data.
Now, and this is where I'm stuck, I would like for example cell B2 in my master workbook to use the workbook name in cell A2, say 'extWB1.xlsx', and to fetch a value, say in cell X1, of that workbook (keep in mind that extWB1.xlsx is a closed workbook) and assign that value to cell B2.
I've figured out how to strip the URL text from the hyperlinked filename.
I've figured out how to get a sub to return the value in cell X1, but as mentioned above, I only know how to display that value in msgBox, not apply it to a cell.
I also don't know how to pass the URL text to the sub which fetches the value from X1 in extWB1.xlsx.
I'm wondering if my sub should instead be converted to a function, say, Function GoFetch(argument1) where I pass the URL text through 'argument1' and using =GoFetch (URLtext) in cell B2 of the master workbook returns the value which is presently being sent back in the msgBox by my existing sub.
The idea of course is to use the Table properties in the master workbook to use the same function in B3, B4, B5 etc to return the X1 values of the workbooks named in A3, A4, A5, etc.