Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Circle equation in VB
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Circle equation in VB
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 -- - K Dales "David" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Circle equation in VB
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 "K Dales" wrote: 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 -- - K Dales "David" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Circle equation in VB
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 -- HTH. :) Dana DeLouis Windows XP, Office 2003 "David" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Circle equation in VB
Thanks Dana. I have a different issue that this code does not resolve.
Appreciate you posting this code. "Dana DeLouis" wrote: 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 -- HTH. :) Dana DeLouis Windows XP, Office 2003 "David" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Circle equation in VB
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. -- - K Dales "David" wrote: 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 "K Dales" wrote: 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 -- - K Dales "David" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Green Circle | Excel Discussion (Misc queries) | |||
How do I make circle within circle graphs in Excel 2003? | Charts and Charting in Excel | |||
VALUE WITH A CIRCLE | Excel Discussion (Misc queries) | |||
How do I set up a circle within a circle chart? | Charts and Charting in Excel | |||
Equation Editor- problem when editing an equation | Excel Discussion (Misc queries) |