function call paramater question

[Guest]

currently am using code:

Dim intMaterial As Integer
Dim strMaterial As String
strMaterial = ActiveCell.Offset([0], [-15])
intMaterial = ConvertMaterial(strMaterial)

the above works, but was going to try to reduce by two lines to:

Dim intMaterial As Integer
intMaterial = ConvertMaterial(ActiveCell.Offset([0], [-15]))

ConvertMaterial is my own function that takes in a string and returns an int.
Any ideas how to do this

 

[Guest]

first, drop the square brackets. they add nothing but extra work.
You could change your function to accept a range.

or you could do

Dim intMaterial As Integer
intMaterial = ConvertMaterial(ActiveCell.Offset(0, -15).Address(0,0))

 

[Guest]

This returns the cell reference, in this case B2, what I want is the value in
the cell, AL, a string. any ideas?

:

you could do

 

[Guest]

Dim intMaterial As Integer
intMaterial = ConvertMaterial(ActiveCell.Offset(0, -15).Value)


then