Excel Range Parameters for Function

[A1Defiant]

All I need is to create a VB Sub command and have the parameter which will be "A1" or "Z3" or what ever cell reference is then put that reference into the Function so I can change the value of it.

So excel reads
=MyFunction(A1)

And VB

Sub MyFunction(CurrentPos)
CurrentPos.Value="Hello New Value"
End Sub

P.S. This formula isn't what I want exactly but from this I can work the rest myself.

 

[A1Defiant]

I realise that this is probably easy but you now beginners.

Guessing myself it maybe something like the following:


Sub PullD(MyPos As Range)
Range("MyPos&quot.Value = "Hell"

End Sub

This would then change the value to what ever cell I place into the formula to "Hell"

so =PullD(B3)
Will change value B3 to "Hell"

 

[k2w]

You would start by creating excel.application, excel.workbook and excel.worksheet objects. Then open or create a new .xls file.

If you want to get the active cell...

row = objWorkSheet.Cells.Row
col = objWorkSheet.Cells.Column
objWorkSheet.Cells(row, col) = "Hello New Value"

or you could just specify

objWorkSheet.Cells(1,2) = "Hello New Value"

Is this what you are looking for?

 

[A1Defiant]

Cheers K2w for your feedback.
Unfortunately no.

I would like to do this
objWorkSheet.Cells(1,2) = "Hello New Value"
but without 1,2 in there any value I want.
so

objWorkSheet.Cells(A1)
or
objWorkSheet.Cells(F5)
except I've got a hundred different cells all in different Cols and Rows so don't want to write VB for every cell.

What I'd like is to have the A1 or F5 just represented as in (Whatever Cell)
Then have the one peice of code just place in whatever value I want.

I've got a working option on this so far just by looping around my code with different values "objWorkSheet.Cells(DifferentValuesHere)" but should be easier to do than this.

 

[johnwm]

You're almost there:

Sub test(a, b)
Cells(a, b) = "Hell"
End Sub

Then in your other code:

test 2,1

will put the string Hell into Row 2 Col 1

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[rvBasic]

Dim strCellAddress as String

strCellAddress = "A1" 'or whatever address suits you
objWorkSheet.Range(strCellAddress) = "Hello New Value"

_________________________________
In theory, there is no difference between theory and practice. In practice, there is. [attributed to Yogi Berra]

 

[A1Defiant]

Cheers RV & All

With your help I think this should work though;

''''''''''''''''''''''''''''''''''''''''''
sub ChangeValue(instrCellAddress as String)

Dim strCellAddress as String
strCellAddress = instrCellAddress
objWorkSheet.Range(strCellAddress) = "Hello New Value"

End Sub
''''''''''''''''''''''''''''''''''''''''''

May not need to create extra string though inside function
the following maybe enough

''''''''''''''''''''''''''''''''''''''''''
sub ChangeValue(instrCellAddress as String)
objWorkSheet.Range(instrCellAddress) = "Hello New Value"
End Sub
''''''''''''''''''''''''''''''''''''''''''

 

[rvBasic]

Extra string inside function is not needed. However you may want to add the cell's value as a parameter to the function:

Public Sub CellContent(strCellAddress as String,strCellValue as String)
objWS.Range(strCellAddress)=strCellValue
end Sub

Using the function is now relatively easy:
CellContent "A1","Hello New Value"

_________________________________
In theory, there is no difference between theory and practice. In practice, there is. [attributed to Yogi Berra]

 

[A1Defiant]

No sorry, this still isn't sorted.

CellContent "A1","Hello New Value"
would call the function
Sub CellContent(strCellAddress as String,strCellValue as String)

Which is ok but supose I don't know what the cellContent values are going to be until they're added into the Excel Sheet and not into the VB code.

So my excel sheet I want a numpty non-programmer to type in a value X, then for that value X to be passed to the Cellcontent function.

So I have all the VB code out of sight of the excel user and for the value X to be added to a cell (say "A1&quot and to have a function within cell A1 that takes whatever value is added to the cell to pass that to a preprogrammed function "Cellcontent" in our example.