Filling an excel range from a vb function
in [Visual Basic]
|
From: lightdreamer on 5 Mar 2010 05:13 Ok guys, so i've found tons of threads that explain how to fill an excel range from a sub in vb, but none that explains how to do so from a function Suppose the following function (even if what i have to work with is much more complicated... and it should fill 3 ranges with 3 different arrays...): [code] Option Base 1 Function fill(v As Range) As Double Dim a(5, 1) As Variant For i = 1 To 5 a(i, 1) = i Next i v.Resize(5, 1).Value = a fill=0 End Function [/code] So, this little program takes the range v, and i want to fill it with values from vector a. It doesn't work, it returns only #VALUE. Someone knows why, and more important how to make it work? A similar code for a sub (without input, selecting a range inside the sub and filling it) works...
From: Horst Heinrich Dittgens on 5 Mar 2010 07:55 > Ok guys, so i've found tons of threads that explain how to fill an excel > range from a sub in vb, but none that explains how to do so from a > function In this case learning the difference between a sub and a function might be of some use <g>
From: lightdreamer on 5 Mar 2010 08:45 Well, i know the difference between the two. What i want to know is how can i make it work, so that i can select a range when calling the function and filling it. My problem is even more general, because the function calls a dll, that returns 3 arrays that must be saved in three distinct ranges. So, any useful tips now? "Horst Heinrich Dittgens" wrote: > > Ok guys, so i've found tons of threads that explain how to fill an excel > > range from a sub in vb, but none that explains how to do so from a > > function > > In this case learning the difference between a sub and a function might be > of some use <g> > > > . >
From: Norm on 5 Mar 2010 08:56 "lightdreamer" <lightdreamer(a)discussions.microsoft.com> wrote in message news:4F07AF4D-7C85-4EE9-BBB4-1A4D5544ACFD(a)microsoft.com... > Ok guys, so i've found tons of threads that explain how to fill an excel > range from a sub in vb, but none that explains how to do so from a > function > > Suppose the following function (even if what i have to work with is much > more complicated... and it should fill 3 ranges with 3 different > arrays...): > > > [code] > Option Base 1 > Function fill(v As Range) As Double > > Dim a(5, 1) As Variant > > For i = 1 To 5 > a(i, 1) = i > Next i > > v.Resize(5, 1).Value = a > > fill=0 > > End Function > [/code] > So, this little program takes the range v, and i want to fill it with > values > from vector a. It doesn't work, it returns only #VALUE. > Someone knows why, and more important how to make it work? > > A similar code for a sub (without input, selecting a range inside the sub > and filling it) works... I am not an expert at all, but how are you passing the Excel object. Have you tried ExcelObject.v.Resize(5,1).Value = aq
From: lightdreamer on 5 Mar 2010 09:19
Thanks for the suggestion, but it's not working... "Norm" wrote: > > > "lightdreamer" <lightdreamer(a)discussions.microsoft.com> wrote in message > news:4F07AF4D-7C85-4EE9-BBB4-1A4D5544ACFD(a)microsoft.com... > > Ok guys, so i've found tons of threads that explain how to fill an excel > > range from a sub in vb, but none that explains how to do so from a > > function > > > > Suppose the following function (even if what i have to work with is much > > more complicated... and it should fill 3 ranges with 3 different > > arrays...): > > > > > > [code] > > Option Base 1 > > Function fill(v As Range) As Double > > > > Dim a(5, 1) As Variant > > > > For i = 1 To 5 > > a(i, 1) = i > > Next i > > > > v.Resize(5, 1).Value = a > > > > fill=0 > > > > End Function > > [/code] > > So, this little program takes the range v, and i want to fill it with > > values > > from vector a. It doesn't work, it returns only #VALUE. > > Someone knows why, and more important how to make it work? > > > > A similar code for a sub (without input, selecting a range inside the sub > > and filling it) works... > > I am not an expert at all, but how are you passing the Excel object. > Have you tried > > ExcelObject.v.Resize(5,1).Value = aq > |