Is VBA Object Oriented? Passing values into a function

[mwhassan]

I read somewhere that VBA is an object Oriented version of BASIC, but I can't seem to find out how to pass values into a function. For example, I just finished writing a program that took forever in VBA. I had to write it in a top down manner, in C++ I could have wrote three functions passed the values into them and been done in 15 min tops, Instead I did a lot of cutting and pasting and my code is very hard to debug since it is the same code over and over again. Can anyone help me, I have enclosed some sample code. (Ignore the asterisks)

'CREATE ****** COLUMN
Columns("O:O").Select
Selection.Insert Shift:=xlToRight
Range("O2").Select
ActiveCell.FormulaR1C1 = "*****"
Range("O3").Select

'CREATE ****** COLUMN
Columns("X:X").Select
Selection.Insert Shift:=xlToRight
Range("X2").Select
ActiveCell.FormulaR1C1 = "*******"
Range("X3").Select

 

[MrKowz]

Try using:

'CREATE ****** COLUMN
Columns("O:O").Insert Shift:=xlToRight
Range("O2").Value = "*****"
 
'CREATE ****** COLUMN
Columns("X:X").Insert Shift:=xlToRight
Range("X2").Value = "*******"

Depending on the consistency of your requirements, you could create a loop that will handle the task since the actions it does are identical.

 

[mwhassan]

Thanks for the streamlined code, still using macro recorder as a but of a crutch.

What I was hoping for would be something where I would only have to write the function once and pass the column letter/position. I have over 20 different columns I am manipulating with three different functions. 20 times three different functions gives me a total of 60 functions when I would much rather have just the three and pass the different variables into the function. I plan on doing so more complicated programming and would hate to have to repeat code over and over

Thanks

 

[Andrew Poulsom]

You can pass arguments to Procedures:

http://www.cpearson.com/Excel/byrefbyval.aspx

 

[gaj104]

Passing parameters are no real different to C++

In VBA, using your example

Function Main()

Call CreateColumn("O")
Call CreateColumn("X")

End Function


Function CreateColumn(myCol As String)

'CREATE ****** COLUMN
Columns(myCol & ":" & myCol).Select
Selection.Insert Shift:=xlToRight
Range(myCol & "2").Select
ActiveCell.FormulaR1C1 = "*****"
Range(myCol & "3").Select

End Function

Also note you don't need to select and then do something. This can be done in the same line (which is what object orientation is all about)

Not sure if you've put an additional * in the X column example. If not then you can pass an additional parameter to handle this.

 

[mwhassan]

That's exactly what I was looking for thank you very much!!!