CODEWITHSUNDEEP
excelvba
Übel Yildmar
Übel Yildmar

Reputation: 491

How to rewrite the input data (range) of a function with a new variable (variant)?

I'd like to give new value to input data in my function: let data (range) be data2 (variant). How can I achieve this?

Here's my code:

Function test(data As Range)

Dim i As Integer
Dim data2() As Variant

ReDim data2(data.Columns.Count * 4)

For i = 1 To (4 * data.Columns.Count)
data2(1 + (i - 1) * 4, 1) = 0
data2(2 + (i - 1) * 4, 1) = 0
data2(3 + (i - 1) * 4, 1) = 0
data2(4 + (i - 1) * 4, 1) = data(i)
Next i

data = data2 'this is where it fails
' ...
'50 rows of code
' ...

test End Function

This is data:

enter image description here

I define data2 = [0,0,0,1,0,0,0,4,0,0,0,3]. Then, I'd like to give new values to data:

data = data2

(I don't want to input these data2 values, I just need data2 for my calculations.)

Upvotes: 0

Views: 93

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57683

Assume the following data

enter image description here

And the following test function calling your procedure MyProcedure. 

Option Explicit    

Public Sub test()
    MyProcedure Range("A1:C1")
End Sub

The thing is, that a range always consists of rows and columns even if you only have one row like Range("A1:J1") your array NewData needs to reflect that by it's dimensions NewData(rows, columns).

Public Sub MyProcedure(ByRef InputData As Range)
    Dim NewData() As Variant
    ReDim NewData(1 To InputData.Rows.Count, 1 To InputData.Columns.Count * 4)

    Dim iCol As Long
    For iCol = 1 To InputData.Columns.Count * 4
        If iCol Mod 4 = 0 Then
            NewData(1, iCol) = InputData(1, iCol / 4)
        Else
            NewData(1, iCol) = 0
        End If
    Next iCol

    'InputData data needs to be resized otherwise it cannot take all data from NewData!
    InputData.Resize(ColumnSize:=InputData.Columns.Count * 4).Value = NewData
End Sub

The result will be:

enter image description here

I recommend always to activate Option Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration.

Upvotes: 1

Damian
Damian

Reputation: 5174

Functions are meant to give you something back, a value, string or whatever. When you are modifying something from another procedure just use a Sub:

Option Explicit
Sub test(data As Range)

    Dim C As Range

     For Each C In data
        C = 1
     Next C

End Sub

Upvotes: 1

Related Questions