Need help writing an Input Function

[WWXX]

Hi, I just start to learn about the powerful VBA, and when I start writing my own spreadsheet, I ran into one problem which need the help from you guys. (See my attached Excel)

I have write a function call "readinput" and another function call "aa"

Function readinput(req)
Application.Volatile
a = Range("B3")
b = Range("B4")
c = Range("B5")
Select Case req
Case 1
readinput = a
Case 2
readinput = b
Case 3
readinput = c
End Select
End Function

Function aa(req)
'Application.Volatile
Select Case req
Case 1
aa = readinput(1) + readinput(2) + readinput(3)
Case 2
aa = readinput(1) * readinput(2) * readinput(3)
End Select

End Function

When (at cell B8) aa(1) it give the sum of cell B3 + B4 + B5,
and (at cell C8) aa(2) is equal to cell B3 x B4 x B5

It works perfectly in one sheet, but when I update the answer in sheet1, and goes to sheet2, the sheet2 answer is same as sheet1....

Everytime i will need to click on a cell in sheet2 to make the answer fits sheet 2, but goes back to sheet1, the answer in sheet1 shows sheet2 answer....

The problem is the "readinput" function, as it always refer to the "Sheet" that was modify perviously.

How can I improve the "readinput" function so that I can get rid of this silly problem...??

[Bob Phillips]

The problem is caused by the fact that your UDF isn't anchored to any cell that keeps it using values on the sheet it is on.

This will work, but IMO it is better to pass the cells to be manipulated as arguments to the UDF



Function readinput(req As Long, CalledFrom As Object)

Application.Volatile

a = CalledFrom.Parent.Range("B3")
b = CalledFrom.Parent.Range("B4")
c = CalledFrom.Parent.Range("B5")

Select Case req
Case 1
readinput = a
Case 2
readinput = b
Case 3
readinput = c
End Select

End Function


Function aa(req)
Dim CalledFrom As Object
'Application.Volatile

Set CalledFrom = Application.Caller

Select Case req
Case 1
aa = readinput(1, CalledFrom) _
+ readinput(2, CalledFrom) _
+ readinput(3, CalledFrom)
Case 2
aa = readinput(1, CalledFrom) _
* readinput(2, CalledFrom) _
* readinput(3, CalledFrom)
End Select

End Function

[WWXX]

Just wanna know what is the use of the following sentance?

" Dim CalledFrom As Object "

What is "Dim"?? I see most of the program has it.

[Aussiebear]

The use of the vba keyword dim is to allow the user to define something as something. For example in your line of code:

Dim CalledFrom as Object

VBA now knows that whenever you use the term CalledFrom it is to be treated as an object whilst it runs that particular section of code.

[Bob Phillips]

You should also declare the other variables using Dim statements, in fact you should use Option Explicit to ensure that you do so.

[WWXX]

Because I am writing a much longer vba spreadsheet for my work, which inside there is a lots of variables and UDF.

So does it mean that it is good practice to define all the variables using "Dim" statement.

So in this case, do it need to define "a" , "b" and "c" as long , or as double???

[Aussiebear]

As Bob suggested in Post#5 the Use of Option Explicit is a very good programming practise to become used to. It forces all varibles to be declared, and cleans up any potential errors.

[Bob Phillips]

It is okay to define them as Long if you know they will be pure integers, else define them as Double.