MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VBA and Arrays/Ranges

  Asked By: Sunil    Date: Jan 10    Category: MS Office    Views: 1922

I'm trying to write a function that will take a range of
numbers, take the log() value of each number, and output those results
as its own range. I know that I can do this as a ctrl+shift+enter
array if I want it in the workbook, but I don't want that. I want to
use the new range (the log() range) in another VBA function that I've

So my question is: is there a way to do the ctrl+shift+enter thing in
VBA to automatically give me a new range (or array is all that I
really need) and output that to another function? Or, is there a way
to use a For Each...Next loop to give me what I want? Or am I going
about this entirely wrong?

Here are the functions that I have been playing with most of the
afternoon, but when called from Excel, it puts the log of only the
first number in the Range as all of the numbers in the new array.

'This function takes the base 10 log of a number (VBA's default LOG()
'function is actually the natural log function
Function Log10(X)
Log10 = Log(X) / Log(10#)
End Function

'This function will convert a range of data to a range of the log of
'the data
Function LogArray(original_data)
Dim new_array() ' Initialize New Array
ReDim new_array(original_data.Rows.Count) 'Dimension new array
to be equal in length to the range
index = 0

For Each data_point In original_data.Cells
new_array(index) = Log10(data_point.Value) 'Call Log base 10
index = index + 1
Next data_point

LogArray = new_array
End Function






8 Answers Found

Answer #1    Answered By: Ellen Simpson     Answered On: Jan 10

: 'This function  will convert  a range  of data  to a range of the log  of
: 'the data
: Function LogArray(original_data)

How is this function called? Is it being used as a user
defined function on the worksheet or is it being called
from within the VBA program. Can you show an example of the
function call?

Answer #2    Answered By: Patricia Johnson     Answered On: Jan 10

The goal is to call  it from another VBA function, but for now I'm
testing it out by calling it in Excel to see if I'm writing it correctly.

Basically, I have a function  (call it MainFunc) that, depending on
certain conditions, needs to use the log  of the range  as opposed to
the range itself. So I'm trying to create a separate function (call
it LogFunc) that will take in the range of data, apply the log to each
value in the range, and then return the new range back to MainFunc.

Function MainFunc(range_data)
if <condition>
end if
End Function

Function LogFunc(range_data)
<this is the problem function>
End Function

Answer #3    Answered By: Calandre Bernard     Answered On: Jan 10

I think I grasp that, but I am trying to repeat your testing
results and don't see how you are getting the results you claimed
in the first message.

For Each Value In new_array
Debug.Print Value
Next Value

When I add the following to the LogArray function  (just before
it ends), I get this in the immediate window (CTRL+G will reveal it),
so I don't understand why you are only seeing the first value, four


Answer #4    Answered By: Alyssa Campbell     Answered On: Jan 10

I think perhaps he s using it as a worksheet function  in each of four
cells, like



2 =MainFunc(H2:H5)

3 =MainFunc(H2:H5)

4 =MainFunc(H2:H5)

5 =MainFunc(H2:H5)


I think he's trying to learn how to return an array  of values from a
function into a Range in some way. I'm not sure how to do this myself...

Answer #5    Answered By: Shelia Wells     Answered On: Jan 10

1. It's assumed that the problem is to determine the range  of values
to be calculated, calculate and write  them back to a worksheet in a
different range.
2. In worksheet LogData the source data  starts at cell B2, the
results at C2.
3.1 Get rngSource, passing it to the calculation sub.
3.2 Put rngSource into arrLog, calculating the LOG of the source
3.3 Pass arrLog back to the calling function, assigning arrLog to
3.4 The results will automatically  appear in column C.
Public Sub getSourceData()
Dim rngSource As Range, ctSource As Integer, rngColB As Range
Dim rngResults As Range, arrCalcResults As Variant
Set rngColB = Worksheets("LogData").Range("$B:$B")
ctSource = Application.WorksheetFunction.Count(rngColB)
With Worksheets("LogData")
Set rngSource = .Range("$B$2:$B$" & ctSource + 1)
Set rngResults = .Range("$C$2:$C$" & ctSource + 1)
End With
arrCalcResults = calcLogOfRange(rngSource)
rngResults = arrCalcResults
Set rngSource = Nothing: Set rngResults = Nothing
End Sub

Public function  calcLogOfRange(rngSource As Range) As Variant
Dim arrLog As Variant, i As Integer
arrLog = rngSource
For i = 1 To UBound(arrLog)
arrLog(i, 1) = Application.WorksheetFunction.Log(arrLog(i, 1))
calcLogOfRange = arrLog
Set rngSource = Nothing
End Function

Answer #6    Answered By: Roop Kapoor     Answered On: Jan 10

I think your solution works perfectly and provides what François

The key was that you can set a Range equal to an Array.

In your code, calling calcLogOfRange(someRange) will return a Range with the
correct Log values. You can then set another Range equal to this returned
value, and the proper values will appear on the spreadsheet. Or you could
then manipulate the values directly in code.

Answer #7    Answered By: Abasi Massri     Answered On: Jan 10

If you array-enter (CTRL+SHIFT+ENTER) your function  [logarray(A1:A4)]
into a HORIZONATL range  (e.g. C5:F5), it will work perfectly.

BUT, if you want to use a VERTICAL range (e.g. B1:B4), you should
change the code line:

LogArray = new_array


LogArray = Application.Transpose(new_array)

I tried both cases separately, and it worked for both types of
ranges. The key point is you get a horizontal array, and transpose it
to fit into a vertical range. It is a little modification. You do not
have to rewrite your code from scratch.

Answer #8    Answered By: Aylin Kaya     Answered On: Jan 10

What posted does work and has led me to my
final goal with the Main calling function. I was on the right track
yesterday, but as Hany pointed out, I was trying to CTRL+SHIFT+ENTER
an array  into a column without transposing it. The array stuff isn't
quite as intuitive as I would like, but it works nonetheless.

Also, this project taught me how to use the Immediate Window finally.
Before, I could never quite figure out how to get it to debug properly.

Didn't find what you were looking for? Find more on VBA and Arrays/Ranges Or get search suggestion and latest updates.