Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > Returning VBA array to Excel2003 cells


Reply
 
Thread Tools Display Modes
  #1  
Old 04-16-2012, 11:44 AM
woody2 woody2 is offline
Regular
 
Join Date: Jan 2008
Posts: 79
Question 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
Reply With Quote
  #2  
Old 04-16-2012, 03:31 PM
passel's Avatar
passel passel is offline
Sinecure Expert

Super Moderator
* Guru *
 
Join Date: Jun 2003
Location: Upstate New York, usa
Posts: 7,910
Default

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.
Reply With Quote
  #3  
Old 04-16-2012, 03:47 PM
Cerian Knight's Avatar
Cerian Knight Cerian Knight is offline
Multi-Technologist

Super Moderator
* Expert *
 
Join Date: May 2004
Location: Michigan
Posts: 3,881
Default

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.
__________________
Are your apps high DPI ready?
Reply With Quote
  #4  
Old 04-16-2012, 03:53 PM
woody2 woody2 is offline
Regular
 
Join Date: Jan 2008
Posts: 79
Question

Quote:
Originally Posted by Cerian Knight View Post
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.
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
Reply With Quote
  #5  
Old 04-16-2012, 03:53 PM
passel's Avatar
passel passel is offline
Sinecure Expert

Super Moderator
* Guru *
 
Join Date: Jun 2003
Location: Upstate New York, usa
Posts: 7,910
Default

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.
Reply With Quote
  #6  
Old 04-16-2012, 03:57 PM
woody2 woody2 is offline
Regular
 
Join Date: Jan 2008
Posts: 79
Default

Quote:
Originally Posted by passel View Post
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
Reply With Quote
  #7  
Old 04-17-2012, 07:05 AM
Cerian Knight's Avatar
Cerian Knight Cerian Knight is offline
Multi-Technologist

Super Moderator
* Expert *
 
Join Date: May 2004
Location: Michigan
Posts: 3,881
Default

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).
__________________
Are your apps high DPI ready?
Reply With Quote
  #8  
Old 04-17-2012, 07:25 AM
woody2 woody2 is offline
Regular
 
Join Date: Jan 2008
Posts: 79
Default

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
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->