Function (array argument, range argument, string argument) vba

[Witek]

Hi,

Long time .... ok no tears.

Problem:
I would like to write Function with many argument:
array, range, string, double etc,

Public newarray() as String

Function fillArray(varArray1() as Variant, _
optional select1 as Range, arg1 as Single)

dim i as single
for i=0 to 5000
newarray(i)=i+arg1
next i
end Function

I call this function with Sub Procedure
sub firstsub()
fillArray fillArray,
end sub
I'm siting many weeks and nothing, I read many FAQ,
but all post have only one (array) argument.
tx for help
Witek

 

[Dana DeLouis]

Not sure I understand, so I'll just throw this out. Are there any ideas
here that would help?

Option Explicit
Public NewArray() As String

Function FillArray(ParamArray v())
Dim n As Long
Dim p As Long

' How many elements?
n = UBound(v) - LBound(v) + 1
ReDim Preserve NewArray(1 To n)

'// Keep track of 1-Based vs. 0-Based Arrays
For p = 1 To n
NewArray(p) = v(p - 1)
Next p
End Function

Sub First_Sub()
FillArray "Cat", "Dog", 3.14, Sqr(2), "More_Text", "etc"
End Sub

 

[Witek]

thanku you Dana. It's really help. I have more difficult (for me
problem)

I would like write Function which fills Array (NewArray) from Selection,
Range etc. This Function take Public Array as argument (or name this
Array) and fill Array from Selection, rng1 or other Range (regular
Range). It will uniwersal Function for all my Arrays

Public NewArray() Ss String

Sub test()
fillArray(NewArray(), Cells(1,2), Selection, True)
End Sub

Public Function fillArray(varArray1() As String, Optional
CellsWihtSelection As Range, Optional Rng1 As Range, Optional isHead As
Boolean = True) As String()
If Not Rng1 Is Nothing then
varArray=rng1
Elseif
CellsWihtSelection.CurrentRegion.Select
varArray=Selection
End If
End Function

When it is not posible, how take name of array as string? I think it's
stupied why to solve this problem, but I try

 

[Tom Ogilvy]

Most of you problem has to do with syntax errors and not mistyping. this
worked fine for me in Excel 97


Option Explicit
Public NewArray As Variant

Sub test()
Dim i As Long, j As Long
fillArray NewArray, Cells(1, 2), Selection, True
For i = LBound(NewArray, 1) To UBound(NewArray, 1)
For j = LBound(NewArray, 2) To UBound(NewArray, 2)
Debug.Print i, j, NewArray(i, j)
Next
Next
End Sub

Public Function fillArray(varArray1 As Variant, _
Optional CellsWithSelection As Range, _
Optional Rng1 As Range, _
Optional isHead As Boolean = True)
If Not Rng1 Is Nothing Then
If Rng1.Count = 1 Then
ReDim varArray1(1 To 1, 1 To 1)
varArray1(1, 1) = Rng1.Value
Else
varArray1 = Rng1.Value
End If
Else
If Not CellsWithSelection Is Nothing Then
CellsWithSelection.CurrentRegion.Select
If Selection.Count = 1 Then
ReDim varArray1(1 To 1, 1 To 1)
varArray1(1, 1) = Selection.Value
Else
varArray1 = Selection
End If
End If
End If
End Function