Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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

written.

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

function

index = index + 1

Next data_point

LogArray = new_array

End Function

SAMPLE DATA:

1.52

2

2.5

2.98

EXPECTED RESULTS:

0.182

0.301

0.398

0.474

ACTUAL RESULTS FROM FUNCTION:

0.182

0.182

0.182

0.182

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>

LogFunc(range_data)

end if

End Function

Function LogFunc(range_data)

<this is the problem function>

End Function

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

times.

0.181843587944773

0.301029995663981

0.397940008672038

0.474216264076255

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

cells, like

A B C D

1

2 =MainFunc(H2:H5)

3 =MainFunc(H2:H5)

4 =MainFunc(H2:H5)

5 =MainFunc(H2:H5)

6

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...

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

data.

3.3 Pass arrLog back to the calling function, assigning arrLog to

rngResults.

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))

Next

calcLogOfRange = arrLog

Set rngSource = Nothing

End Function

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

needs.

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.

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

to:

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.

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.

Related Topics:

- Reference range row and column in VBA
- Dump a range to an array
- Values of a Range in an Array
- Insert Rows, based on data in range, using VBA
- Array and Range
- Formula using range name in VBA code
- Using VBA to name Ranges
- How to obtain minimum in a range with VBA
- Reference range row and column in VBA
- Using a Defined Variable Range in a VBA SumIf
- VBA variable range in a formula
- VBA Code for pasting a set of formulas down a data range
- VBA Excel Coding for dynamically changing function range
- Multidimensional array problem in Excel VBA
- VBA or array formula?
- VBA Array - return value to a cell
- sorting an array in VBA without writing it in excel sheet
- highlight a range using a named range
- Cannot use the VBA add table command of MS word in MS Excel VBA
- Convert code from excel 2000 vba to excel 2003 vba
- writing vba code to generate vba code
- Cannot use the VBA add table command of MS word in MS Excel VBA
- writing vba code to generate vba code
- Placing VBA Code "Live" Into Another WkBook Via VBA
- Array to array direct transfer in VB