Using Add-in worksheet with Function VBA

[somkiat]

How to use worksheet inside add-in file with its add-in function?

I have a very big calculation table created with Excel functions. It is very difficult to revise all formulas to VBA procedure.

Can we use Function VBA to pass inputs to worksheet inside add-in file,

then use formula and functions within add-in worksheet to calculate,

then return answer back to VBA function?

Thanks,

Somkiat
www.ExcelExpertTraining.com
xls@i.am
Thailand

 

[Colo]

Hello Somkiat,

Did you write the add-in? If so, go to VBE and select that add-in > Thisworkbook in the project window.
You can see IsAddin property at the properties window, then turn it as True. You can see the worksheet of the add-in.
And you can specify that sheet from the add-in code something like that.

Thisworksbook.Sheets(1).range("A1")

 

[touchie]

Colo,

I think somkiat has mean to link in own add-in file not another file.

And I want to know too..

 

[somkiat]

Thanks but it is not my need.

It seems that there is impossible to utilize worksheet of add-in file except copy its worksheet to new file. My Need goes furthur and may not be possible as far as I know. Let see this example,

My Add-Ins is MyAddin.xla
Sheet1 of MyAddin.xla has huge table of formula.
Cell A1 to A3 are input cells.
Cells B1:G100 are cells that have formula to calculate.
Cell A4 is answer or output cell.

Inside MyAddin.xla Module has a Function Procedure named MyFunction.

Function MyFunction(nPut1,nPut2,nPut3)

End Function

How to use UDF MyFunction in other workbook to pass inputs to MyAddin.xla Sheet1 Cells A1 to A3

then let formula and function in MyAddin.xla Sheet1 Cells B1:G100 calculate

then UDF MyFunction return answer from MyAddin.xla Sheet1 Cell A4 to cell in other workbook?

So far as I know, there is no book or any one mentions on this matter. It just impossible to do this job!!

Thanks,
Somkiat

 

[Juan Pablo González]

Do you need to do this from VBA or directly in Excel ?

 

[somkiat]

Hi Juan,

I want to use it as normal UDF in Excel worksheet but this UDF is from another Add-in file.

If it is possible then it will no need to write VBA cose in Module. We can use formula in cells instead then Function procedure will be very short. Just for receive input then calculate with cells formula then return answer back.

Please feel free to answer with technical term. I know Excel and VBA quite well and have my own Excel forum at http://xls.how.to

I will come to this forum just for very very difficult question that normally it is impossible to do it. Your guys are very smart.

Somkiat

 

[tusharm]

I don't see how you can do what you want. A UDF is restricted to returning a result value. It cannot change the contents of anything else within the XL environment.

 

[Jay Petrulis]

Hi Juan,

I want to use it as normal UDF in Excel worksheet but this UDF is from another Add-in file.

If it is possible then it will no need to write VBA cose in Module. We can use formula in cells instead then Function procedure will be very short. Just for receive input then calculate with cells formula then return answer back.

Please feel free to answer with technical term. I know Excel and VBA quite well and have my own Excel forum at http://xls.how.to

I will come to this forum just for very very difficult question that normally it is impossible to do it. Your guys are very smart.

Somkiat

Very interesting question. Without more details, I agree with Tushar -- I don't think it is possible, but will certainly give it a try.

Can you let us know what is on the add-in sheet? Depending on the flexibility you need, it may be that you will need to extend the MyFunction to do the calculations within a VBA array, rather than Excel.

That way, the MyFunction UDF is really a self contained spreadsheet model that will return a value.

Please provide more insight as to the specifics of what you are doing. The major problem I see is that it may be impossible to to store an Excel formula (not function) with reference arguments inside the array.

 

[somkiat]

Thank you all very much. I have solved this problem using Range Name to send data back to worksheet inside Add-in.

Please download file from
http://www.excelexperttraining.com/forum/download.php?id=296

Function nPut1()
    Application.Volatile
    nPut1 = ActiveWorkbook.ActiveSheet.Range("nPut1")
End Function

Function nPut2()
    Application.Volatile
    nPut2 = ActiveWorkbook.ActiveSheet.Range("nPut2")
End Function

Function nPut3()
    Application.Volatile
    nPut3 = ActiveWorkbook.ActiveSheet.Range("nPut3")
End Function

Function nPut4()
    Application.Volatile
    nPut4 = ActiveWorkbook.ActiveSheet.Range("nPut4")
End Function

Function oPut()
    Application.Volatile
    oPut = ThisWorkbook.Sheets(1).Range("Total")
End Function

Please load MyUDF.xla then open MyUDFTest.xls

Somkiat

 

[tusharm]

Nice! A creative way to work around the limitation of UDFs. But...

For the specific and limited case, it sure would yield a working solution. Unfortunately, just like other techniques suggested as detours around the XL UDF limitation, this too is extremely fragile.

Not to take anything from your imaginative solution, but here are two ways to mess it up.

Suppose this is used in Sheet1 of a workbook. Now, go to sheet2 and edit any cell. Switch back to Sheet1. The oPut result will be #VALUE!

Suppose this is used in Sheet1 of a workbook. Now, on sheet2 of the workbook, define the same names, local to sheet2. Set up the oPut on sheet2.

Now, Sheet1 and Sheet2 oPut() cells *both* will be the *same* value -- it will be the last value returned. So, either Sheet1 is correct or Sheet2 is correct, but not both.

Extend this to multiple workbooks and you will quickly realize that this technique can be used only *once* across all open workbooks. Otherwise, the results will be data corruption in all instances but one!

Thank you all very much. I have solved this problem using Range Name to send data back to worksheet inside Add-in.
{snip}