Need help returning an array from VBA into Excel

Question

I would like to return the value of compZ into my excel data sheet. If you could tell me how to do so, I would really appreciate it.

The code is attached below:

Option Explicit
Option Base 1

Public Function valZ120() As Double

'declare variables

Dim equation As Double
Dim x As Double
Dim randint As String
Dim compZ(476) As Double
Dim derEq As Double
Dim valX As Double
Dim nvalX As Double
Dim varA As Double
Dim varB As Double
Dim i As Double

'loop from i=25 to i=500

For i = 25 To 500
   varA = (0.538083613 * 13736.62195 * i) / ((8.3145 ^ 2) * (120 ^ 2))
   varB = (2.528160538 * i) / (8.3145 * 120)
   x = 0
   nvalX = 0.2

   'a Do While loop

   Do While Abs(nvalX - x) >= (0.01)
      x = nvalX
      equation = (x ^ 3) - (x ^ 2) + (x * (varA - varB - varB ^ 2)) - (varA * varB)
      derEq = (3 * x ^ 2) - (2 * x) + (varA - varB - varB ^ 2)
      nvalX = (x) - ((equation) / (derEq))
   Loop

   'once the do while loop is complete, the value obtained is stored in the first space in the array and so on
   compZ(i - 24) = nvalX
   'this was just to test the code to make sure it was working (it is)
   MsgBox compZ(i - 24)
Next

'how do I bring the values of this array into excel?

End Function

Thank You

Welcome to the site! Is this to an existing spreadsheet or do you just need to open it up in Excel? I push a lot of things to csv files — Jimmy Smith
– Jimmy Smith, Commented Feb 20, 2020 at 21:42
Does this answer your question? Excel VBA function to print an array to the workbook — slightly drifting
– slightly drifting, Commented Feb 20, 2020 at 21:57
Hi @abdullahq7 - you might want to have a look at some of these pages for inserting an array into excel. Since VBA is hooked up to excel, it should not be complicated. stackoverflow.com/questions/6063672/… excel-easy.com/vba/array.html — slightly drifting
– slightly drifting, Commented Feb 20, 2020 at 22:00
Take a look at Chip Pearson's VBA Arrays and Worksheet Ranges — Ron Rosenfeld
– Ron Rosenfeld, Commented Feb 21, 2020 at 1:09

slightly drifting · Accepted Answer · 2020-02-20 22:20:50Z

0

It looks like you have a 1 dimensional array. Should be easy right?

First check the bounds of your array, then run a for-loop through the array to output to a cell.

Dim arrayLength as int
arrayLength = UBound(compZ) - LBound(compZ) + 1

For i = 1 To arrayLength      
       Cells(2,i).Value = compZ(i)  
Next i

answered Feb 20, 2020 at 22:20

slightly drifting

3321 gold badge5 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Need help returning an array from VBA into Excel

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related