Pass wsSorting as an argument...like below
Public Function Sorting(wsTemp As workSheet, myArray() As Double) As Double
'Sorting the permutations and identifying the rank of the observed value
For idx = 1 To UBound(myArray)
wsTemp.Cells(idx, 1).Value = myArray(idx)
wsTemp.Cells(idx, 2).Value = idx
Next idx
wsTemp.Range("A:B").Sort Key1:=wsTemp.Cells(1, 1),
Order1:=xlAscending
For idx = 1 To UBound(myArray)
wsTemp.Cells(idx, 3).Value = idx
Next idx
wsTemp.Range("A:C").Sort Key1:=wsTemp.Cells(1, 2),
Order1:=xlAscending
End Function
If this post helps click Yes
---------------
Jacob Skaria
"KAH" wrote:
> It was declared as a worksheet earlier:
> Public wsSorting As Worksheet
> But now I think I see the problem - I did not set it as anything. Is it
> possible to just keep wsSorting as a virtual worksheet where I can just add
> values?
>
> "Jacob Skaria" wrote:
>
> > Sheets("Sheet1").cells(1,1).value = 5 will work. So your code should also
> > work.
> >
> > Is wsSorting referred to a sheet; and when this is used wihtin a function is
> > this delcared as a global variable..
> > Dim wsSorting as Worksheet
> > Set wsSorting = Workbooks("filename.xls").Sheets("sheetname")
> >
> >
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "KAH" wrote:
> >
> > > I wrote the function below and received the error message 'Object variable or
> > > With block variable not set' for the line:
> > > wsSorting.Cells(idx, 1).Value = myArray(idx)
> > > I think I haven't written this proplery. How do I put a value (number) into
> > > the cell of a worksheet?
> > > I also have a general question about using worksheets in VisualBasic. Are
> > > they all the same or can they be virtual or real? I am using a worksheet to
> > > sort and rank numbers but it may take too long if the worksheet is not
> > > virtual. I also do not want to show the worksheet in Excel. I hope this makes
> > > sense.
> > > Thanks for any help.
> > >
> > >
> > > Public Function Sorting(myArray() As Double) As Double
> > > 'Sorting the permutations and identifying the rank of the observed value
> > > For idx = 1 To UBound(myArray)
> > > wsSorting.Cells(idx, 1).Value = myArray(idx)
> > > wsSorting.Cells(idx, 2).Value = idx
> > > Next idx
> > > wsSorting.Range("A:B").Sort Key1:=wsSorting.Cells(1, 1),
> > > Order1:=xlAscending
> > > For idx = 1 To UBound(myArray)
> > > wsSorting.Cells(idx, 3).Value = idx
> > > Next idx
> > > wsSorting.Range("A:C").Sort Key1:=wsSorting.Cells(1, 2),
> > > Order1:=xlAscending
> > >
> > > End Function
|
Similar Threads
