PDA

View Full Version : Any workaround for assigning values to multiple cells by excel formula or function



VictorCC
01-19-2009, 06:17 AM
Hi All,
I have a problem that makes me crazy! :banghead:
My task is to develop an excel function/formula that fetch a list of data(could be 2 dimensional array) from database and assign those data to a range starting from the CallingCell(ActiveCell).
For example,
you have a function called LoadMatrix() that fetch a matrix from Database such as,
1 2 3 4
5 6 7 8
And you call the fucntion on Cell("A1"), then the result would be,
__ __ A B C D
Row1 1 2 3 4
Row2 5 6 7 8
I have searched the issue in internet for a while and I know that it is technically impossible by Excel function/formula since by doing so, excel is unable to know the recalculating order.
But I also know there should be some solution working around such as using ActiveX Control.
My problem is I don't know how to do it?
Anyone here is familiar with this problem, please give me some advice. Any advice is welcome!!!
I would be appreciated for your reply in advance.
Thank you!

Jan Karel Pieterse
01-19-2009, 06:50 AM
I have seen that solution you mentioned indeed.
Why are you looking for this solution? You could perhaps do this differently altogether. WHy not a database query?

VictorCC
01-19-2009, 08:08 AM
Hi Jan,

Thanks for you reply!

My colleagues right now are using a third party addin(DLL file) for their Excel workbooks.
By using this addin, they can input a function(a data request) in a certain cell and get an array of data that are assign to the range starting with the activecell.
I am currently developing an alternative solution for them to replace this third party addin.
And they certainly would like to have the similar function implemented in my solution too.
So that they don't need change their Excel Workbooks too much when migrate from the third party solution to the alternative solution.

Like you suggest, I certainly use sql query in function LoadMatrix(). But the problem is that I can not create a function that can modify cells except the ActiveCell. Excel only allows Function or Formula modify the Corresponding ActiveCell.
Back to my example,let's say,we assign the formula to Cell("A1") like Cell("A1") = LoadMatrix(). By calculating Cell("A1") I want the result like below,
__ __ A B C D
Row1 1 2 3 4
Row2 5 6 7 8

I have some unclear idea:
I would like to have an ActiveX addin object. It has a function db2excel(startcell_address) that loads array of data from database and pastes the data to the worksheet starting from the cell specified by startcell_address. Function LoadMatrix only needs to call the function db2excel by giving the starting cell address.(probably need to trigger an event.)

I know it is possible to work around but unfortunately I don't know how to do it?

If you know the solution or other suitable solution, please let me know.
Thank you in advance.

lucas
01-19-2009, 02:13 PM
Can the matrix be in an excel file for the database?

mdmackillop
01-19-2009, 02:26 PM
Can you do anything with this methodology? Enter your function and run Test on the cell. It could also be triggered by a Change event.
Dim arr(3, 5)
Sub test()

If UCase(ActiveCell.Formula) = "=LOADMATRIX()" Then

For i = 0 To 3
For j = 0 To 5
arr(i, j) = Int(100 * Rnd)
Next
Next

End If

x = UBound(arr, 1) + 1
y = UBound(arr, 2) + 1

ActiveCell.Resize(x, y) = arr

End Sub

VictorCC
01-20-2009, 01:11 AM
Hi lucas,
It is not an excel file in databaase and it can not be.

VictorCC
01-20-2009, 01:22 AM
Hi mdmackillop,

Is the code below what you mean?


Option Explicit
Function LOADMATRIX()
Call test
LOADMATRIX = 1
End Function
Sub test()
Dim arr(3, 5) As Variant
Dim i As Integer
Dim j As Integer
Dim x As Integer
Dim y As Integer
If UCase(ActiveCell.Formula) = "=LOADMATRIX()" Then

For i = 0 To 3
For j = 0 To 5
arr(i, j) = Int(100 * Rnd)
Next
Next

End If

x = UBound(arr, 1) + 1
y = UBound(arr, 2) + 1

ActiveCell.Resize(x, y) = arr

End Sub


And in cell, I call function LOADMATRIX then it triggers sub test.
I have tried this method and it doesn't work.