Circle equation in VB

[David]

Hi,
This is a VB question.
I created a matrix(or table)using certain dimensions from user.
diameter, xindex(cell height), yindex(cell width).
(diameter/xindex) gives me the number of cells in xaxis,
(diameter/yindex) gives me the number of cells in yaxis

Then I try to color cells that would fit in a circle.
The following equation works when xindex and yindex are equal.
When Xindex and Yindex are different I keep getting an ellipse shape.
The reason is that this circle equation assigns the same number of cells for
xaxis and yaxis and since the height and width are not same I get an ellipse.
This is the code I use.

Dim m As Long, n As Long
For m = 0 To rw - 1 ' rw is number of rows
For n = 0 To cl - 1 ' cl is number of columns

d = Sqr(((m - (rw / 2)) ^ 2) + ((n - (cl / 2)) ^ 2))
If d < (radiusInCells) Then
grille1.row = m ' row location
grille1.col = n ' col location
grille1.CellBackColor = vbRed ' color cell
End If
Next n
Next m

I got a solution from K. dales that worked for excel (excel uses points or
pixels for dimensions:
AspectRatio = Cells(1, 1).Width / Cells(1, 1).Height
For i = 1 To 900
For j = 1 To 250
d = Sqr((i - 100) ^ 2 + (j - 65) ^ 2)
If d < 60 Then
Cells(i, Int(j / AspectRatio)).Interior.ColorIndex = 45


I tried using this AspectRation variable in my code but did not work:
AspectRatio= xindex(cell height)/yindex(cell width).
Then used it as follows:

Dim m As Long, n As Long
For m = 0 To rw - 1 ' rw is number of rows
For n = 0 To cl - 1 ' cl is number of columns

d = Sqr(((m - (rw / 2)) ^ 2) + ((n - (cl / 2)) ^ 2))
If d < (RadiusIncells) Then
grille1.row = m ' row location
grille1.col = (Int(n / AspectRatio)) 'Col location
grille1.CellBackColor = vbRed ' color cell
End If
Next n
Next m

Any input on this is greatly appreciated

 

[K Dales]

I think perhaps you reversed the order - note that my original AspectRatio is
Width over Height; you are using height/width.

So try AspectRatio = yindex/xindex

 

[David]

I thought that it would not matter.
Could you please explain how is this fraction helps the selection of circle
cells. I do not fully undertand it.
The way you suggested gives me an error which I am looking into.

Thanks for your help

 

[Dana DeLouis]

Hi. Don't know if anything here would be of help, so I'll just throw this
out.

Sub Demo()
Const Rc As Long = 30 'Row Center of Circle
Const Cc As Long = 30 'Column Center of Circle
Const d As Long = 25 ' Radius Distance

Dim c As Long 'Column
Dim k As Double
Dim RngStart As Range
Dim RngEnd As Range

With WorksheetFunction
For c = Cc - d To Cc + d
k = Sqr(d ^ 2 - (c - Cc) ^ 2)
Set RngStart = Cells(.RoundDown(Rc - k, 0), c)
Set RngEnd = Cells(.RoundUp(Rc + k, 0), c)

Range(RngStart, RngEnd).Interior.Color = vbRed
Next c
End With
Cells.RowHeight = 42
End Sub

 

[David]

Thanks Dana. I have a different issue that this code does not resolve.
Appreciate you posting this code.

 

[K Dales]

Aspect ratio is the mathematical name for the ratio of width to height. When
you have a square the aspect ratio is equal to 1. a "tall, skinny" rectangle
has a low aspect ratio, but a "short, wide" one has a high aspect ratio. So
it does make a difference - a big difference.

Your excel cells have an aspect ratio, and that is why the "circle" was not
coming out circular (except when cells were square, where aspect ratio = 1).
To account for it, I put the aspect ratio into the equation that chooses the
cells to color in a way that reverses the effect of the cells non-squareness.
In other words, if the cells are wider than tall, the equation has to
"compress" the calculation used to choose which column is selected to color,
and I did that by dividing by the aspect ratio. Another way of thinking
about it is that it balances the effect of the wide cells when I divide by
the "amount" of their wideness.

The bottom line is that h/w does not equal w/h, so you have to get it
straight - if not you will get the opposite effect - an ellipse that is even
more stretched out than the original one.

Hope you get the other problems with the code figured out.