Returning VBA array to Excel2003 cells - Xtreme Visual Basic Talk

04-16-2012, 11:44 AM

woody2

Regular

Join Date: Jan 2008

Posts: 79

Returning VBA array to Excel2003 cells

Hi

I"m obviously missing something extremely basic here so I'd appreciate some help.

I'm trying to put an array of values into a range of cells. I'd like to have this as part of a UDF ( not a Macro or Sub )

I"ve gotten this far :

This code works fine when I do a MACRO/RUN ( I lifted it from http://vba-corner.livejournal.com/3349.html )

Code:

Sub WriteRange()
Dim myArray() As String
Dim myRow As Integer
Dim myColumn As Integer
  
  'fill array with values
  ReDim myArray(1 To 6, 1 To 3)
  For myRow = 1 To 6
    For myColumn = 1 To 3
      myArray(myRow, myColumn) = myRow & "," & myColumn
    Next
  Next
  'write array to worksheet
  Range("b5:d10").Value = myArray()
End Sub

BUT, when I convert it to a Function like this :

Code:

Function WriteRange2()

Dim myArray() As String
Dim myRow As Integer
Dim myColumn As Integer
  
  'fill array with values
  ReDim myArray(1 To 6, 1 To 3)
  For myRow = 1 To 6
    For myColumn = 1 To 3
      myArray(myRow, myColumn) = myRow & "," & myColumn
    Next
  Next
  'write array to worksheet
  Range("b15:d20").Value = myArray()

End Function

now it just returns #VALUE! ... several hundred bad words

I've tried making the function return a VARIANT, but that doesn't help

As I say, I'm probably missing something very simple, so I'd appreciate a hand.

regards
woody

04-16-2012, 03:31 PM

passel

Sinecure Expert

Super Moderator
* Guru *

Join Date: Jun 2003

Location: Upstate New York, usa

Posts: 7,714

I'm not a VBA person, but if I google for changing a range in a UDF, I find this:

"A UDF can only return a value to the cell(s) whence it was called -- it must not modify the contents or formatting of any cell and must not modify the operating environment of Excel. If you attempt to change anything, the function will terminate immediately and return a #VALUE error to the calling cell."
http://www.cpearson.com/excel/writin...ionsinvba.aspx

__________________
There Is An Island Of Opportunity In The Middle of Every Difficulty.
Miss That, Though, And You're Pretty Much Doomed.

04-16-2012, 03:47 PM

Cerian Knight

Multi-Technologist

Super Moderator
* Expert *

Join Date: May 2004

Location: Michigan

Posts: 3,739

If I might interject, how are you calling the function and what code immediately precedes the call.

I've got your code working (in XL2003 SP3) both as a macro (1st example) and (from the same code module) as a function (2nd example) called within a command button event on the target sheet without any changes to the active sheet that would interfere with the Range.

__________________
"May the code that you write never work in ways that you didn't expect; and may the code that you didn't write never require you to maintain it". - Ancient Chinese Proverb

04-16-2012, 03:53 PM

woody2

Regular

Join Date: Jan 2008

Posts: 79

Quote:

Originally Posted by Cerian Knight

thanks, Cerian, appreciate the help

As I just told passel, I've since learned that I MUST use a sub, and CANNOT use a function.

I don't want to have to manually trigger this thing ( like a macro ), I want it to go automatically ( like a UDF ). I'm now trying to figure out how to trigger a SUB automatically ( I'm investigating ChangeProcedure ).

thanks very much

04-16-2012, 03:53 PM

passel

Sinecure Expert

Super Moderator
* Guru *

Join Date: Jun 2003

Location: Upstate New York, usa

Posts: 7,714

overlapped posting: This is not in regards to the last post, but Cerian Knight's. I'll back away at this point since I don't know anything anyway.

My assumption, for a UDF, would be in the spreadsheet cell itself, using =WriteRange2()

If you trigger it from an event, then there is no issue.
I tried setting up a timer to call WriteRange from the timer event, but while it worked fine from the VBA environment, the =function didn't cause an error any longer, but the function was apparently never schedule on the timer either.

__________________
There Is An Island Of Opportunity In The Middle of Every Difficulty.
Miss That, Though, And You're Pretty Much Doomed.

04-16-2012, 03:57 PM

woody2

Regular

Join Date: Jan 2008

Posts: 79

Quote:

Originally Posted by passel

My assumption, for a UDF, would be in the spreadsheet cell itself, using =WriteRange2()

If you trigger it from an event, then there is no issue.
I tried setting up a timer to call WriteRange from the timer event, but while it worked fine from the VBA environment, the =function didn't cause an error any longer, but the function was apparently never schedule on the timer either.

Thanks, passel

can you show me an example of how to trigger it from an event ??

not sure I understand your second sentence... did it work fine and put the values into the range of cells, or did it not because it was never scheduled ??

appreciate the help

04-17-2012, 07:05 AM

Cerian Knight

Multi-Technologist

Super Moderator
* Expert *

Join Date: May 2004

Location: Michigan

Posts: 3,739

If you need a UDF then it would normally be a Function, as there is usually an input and output specification. If you need a Sub then there is no difference between a Sub and a Macro, as either can be triggered by an Event or Manually.

A simple trigger looks like this:

Code:

'place code on sheet and WriteRange2 is in a module
Private Sub CommandButton1_Click()
WriteRange2
End Sub

Any event could be substituted for the CommandButton (e.g., Worksheet_SelectionChange, etc.).

You should be very specific about exactly which code you are using and where you have placed it. If you are referencing this code as a function from a cell formula, you should be very specific about what you are putting in the cell formula. Also, be very clear about why you are trying to do this and exactly what you have tried. I don't see how we can mix metaphors over UDFs, Subs and Functions... they all have a very specific mathematical purpose and usage (granted the line can blur sometimes while working in Excel).

__________________
"May the code that you write never work in ways that you didn't expect; and may the code that you didn't write never require you to maintain it". - Ancient Chinese Proverb

04-17-2012, 07:25 AM

woody2

Regular

Join Date: Jan 2008

Posts: 79

Thanks, Cerian

WE don't mix metaphors over UDF's, Subs, and Functions - I was mixing them up... thanks to all the help I"ve received, I'm now closer to an understanding.
I do have my thing working, and the solution was much as you have suggested above.

appreciate everybody's help