2

My excel sheet creates a map with various layers, showing a network made up of lines, squares, dots, and triangles. I have functions for each creation which take arguments such as

cellLocation As Range  '(takes a given cell location)
shapeType As String    '(oval, triangle, rectangle)
Color                  '(red, black, whatever)
sizeFactor As Double   '(factors the shape size as a function of cell's width)

I am just learning about classes now, but wondering if classes would be useful in this case, and how I could use them to simplify my code, rather than having functions with 6 different arguments and such.

Originally I had functions like this:

Function CreateWell(cellRng As Range, wellName As String)
'creates a square of particular color, size, etc in the cellRng and names it wellName

Function CreateCompressor(cellRng As Range, compName As String)
'creates an oval of particular color, size , etc. similar to other func.

Then, because I had about 5 of these where the only variations were color, size, shape, etc. I tried making an overall function:

 Function CreateShape(cellRng As Range, shpName As String, _
            shpColor As String, shpSize as double, shpType As string)

But this seems to be messy (too many arguments). How can employing classes clean up this type of code?

teepee
  • 2,620
  • 2
  • 22
  • 47
  • I'd say no. The reason being that VBa/VB6 does not have real support for classes in terms of inheritance, and for what you are doing, having generic functions to do this is fine. Also, maybe look at optional parameters so everything doesn't have to be passed in. – Jeremy Jan 27 '16 at 19:35
  • @Jeremy thanks for the insight. what do you mean about class inheritance, I'm not familiar with that. – teepee Jan 27 '16 at 19:50
  • you can check this article out. https://en.wikipedia.org/wiki/Inheritance_(object-oriented_programming) – Jeremy Jan 27 '16 at 20:03
  • Consider using an interface with the `Implements` keyword. http://stackoverflow.com/questions/19373081/how-to-use-the-implements-in-excel-vba – Kyle Jan 27 '16 at 21:27

2 Answers2

0

you are right. you need to use object oriented concepts of the language. VBA is not a complete object oriented language but it has some nice features. i cant write you an example because there is more then one way to do this. also i wanted to comment and not to write an answer but my low rating preventing me. read about object oriented concepts and working with classes, crating instances to classes overloading function with different stamps and enums. if you want to do it with more modern language (like visual basic.net for example) read about inheritance and polimorphizem - also very important concepts to implement tasks like you mentioned.

Jonathan Applebaum
  • 5,738
  • 4
  • 33
  • 52
0

I have been exploring this question of how to create class modules to use with shapes. I have a MakeShape() sub with many arguments defining the shape's properties. As you said, it becomes challenging to keep them orderly as I add new arguments.

I asked myself: what is the relationship between the class and the shape on the screen? My working theory is that the class should hold the properties of the shape on the screen (e.g. compressor.top, compressor.left, compressor.width, etc.). Then you could create methods (e.g., compressor.draw to create the shape on screen). I am less clear how you would manipulate a shape once it is drawn as a method of the shape class (in effect, how to keep a leash on that same shape object now that it is out in the wild). I suppose the object could have an object property, so compressor.shape would refer to a shape object on the screen. At that point I'm not sure if you would make adjustments with compressor.top, compressor.shape.top, or either. I will try to update as I learn.

Mark E.
  • 373
  • 2
  • 10