For Loop and array allocation problem VBA

[Sun11]

Hello members,
I am re coding a code from one C based language to VBA. Can you please help me to sort the problem or rectify any mistakes?

Here is original code:

 PlanetDeg[1] = DMoon;
  PlanetDeg[2] = DSun;
  PlanetDeg[3] = DMars;
  PlanetDeg[4] = DMercury;
  PlanetDeg[5] = DJupiter;
  PlanetDeg[6] = DSaturn;
  PlanetDeg[7] = DNeptune;
  PlanetDeg[8] = DUranus;
  PlanetDeg[9] = DPluto;
  PlanetDeg[10] = DVenus; 
  
  PlanetName[1] = "Moon";
  PlanetName[2] = "Sun";
  PlanetName[3] = "Mars";
  PlanetName[4] = "Mercury";
  PlanetName[5] = "Jupiter";
  PlanetName[6] = "Saturn";
  PlanetName[7] = "Neptune";
  PlanetName[8] = "Uranus";
  PlanetName[9] = "Pluto";
  PlanetName[10] = "Venus";
   
   int count = 0;
   
   for(int g=1 ; g <=10 ; g++)
    { 
       if(MathAbs(O_D - PlanetDeg[g]) <=2)
       {
       count++;
       if(count==1) e_P = PlanetName[g];
       else 
       e_P = StringConcatenate(e_P,",",PlanetName[g]); 
       }
    }

Now here is the VBA code:

Public Function Planet(O_D As Double)
 Dim PlanetName(1 To 10) As String
 PlanetName(1) = "Moon"
  PlanetName(2) = "Sun"
  PlanetName(3) = "Mars"
  PlanetName(4) = "Mercury"
  PlanetName(5) = "Jupiter"
  PlanetName(6) = "Saturn"
  PlanetName(7) = "Neptune"
  PlanetName(8) = "Uranus"
  PlanetName(9) = "Pluto"
  PlanetName(10) = "Venus"
  
  Dim EffectivePlanet As String
  
  
 Dim PlanetDeg(1 To 10) As Double
 PlanetDeg(1) = Cells(2, 4)
 PlanetDeg(2) = Cells(2, 5)
 PlanetDeg(3) = Cells(2, 6)
 PlanetDeg(4) = Cells(2, 7)
 PlanetDeg(5) = Cells(2, 8)
 PlanetDeg(6) = Cells(2, 9)
 PlanetDeg(7) = Cells(2, 10)
 PlanetDeg(8) = Cells(2, 11)
 PlanetDeg(9) = Cells(2, 12)
 PlanetDeg(10) = Cells(2, 13)
 
 
 Dim Count As Integer
 Count = 0
 Dim i As Integer
 For i = 1 To 10
 If Abs(O_D- PlanetDeg(i)) <= 2 Then
 Count = Count + 1
 If Count = 1 Then e_p = PlanetName(i)
 Else
 e_p = e_p & "," & PlanetName(i)
 End If
 
 Next i
 
 Planet = e_p
 
  
End Function

Now I know at the PlanetDeg array allocation i have fixed cells formula which is wrong, cause rows will be changed & Columns will be fixed on those number in order to do that, do I have to run another loop for dynamic array? or there is any short cut way?

Plus For loop which are already in there for VBA code is counting wrongly. It showing all planets name, not satisfying any conditions.

Please help
Thank you in advance.

 

[HotRhodium]

Give this a try. Removed Count(Can be confused with vba commands) Closed open If statement (Check for intention)

Public Function Planet(O_D As Double)
 Dim PlanetName(1 To 10) As String, EffectivePlanet As String
 Dim PlanetDeg(1 To 10) As Double
 Dim i As Integer
 
 PlanetName(1) = "Moon"
 PlanetName(2) = "Sun"
 PlanetName(3) = "Mars"
 PlanetName(4) = "Mercury"
 PlanetName(5) = "Jupiter"
 PlanetName(6) = "Saturn"
 PlanetName(7) = "Neptune"
 PlanetName(8) = "Uranus"
 PlanetName(9) = "Pluto"
 PlanetName(10) = "Venus"
  
 PlanetDeg(1) = Cells(2, 4)
 PlanetDeg(2) = Cells(2, 5)
 PlanetDeg(3) = Cells(2, 6)
 PlanetDeg(4) = Cells(2, 7)
 PlanetDeg(5) = Cells(2, 8)
 PlanetDeg(6) = Cells(2, 9)
 PlanetDeg(7) = Cells(2, 10)
 PlanetDeg(8) = Cells(2, 11)
 PlanetDeg(9) = Cells(2, 12)
 PlanetDeg(10) = Cells(2, 13)
 
 For i = 1 To 10
  If Abs(O_D - PlanetDeg(i)) <= 2 Then
   If i = 1 Then
    e_p = PlanetName(i)
   Else
    e_p = e_p & "," & PlanetName(i)
   End If
  End If
 Next i
 Planet = e_p
End Function

 

[Sun11]

Give this a try. Removed Count(Can be confused with vba commands) Closed open If statement (Check for intention)

Public Function Planet(O_D As Double)
 Dim PlanetName(1 To 10) As String, e_p As String
 Dim PlanetDeg(1 To 10) As Double
 Dim i As Integer
 
 PlanetName(1) = "Moon"
 PlanetName(2) = "Sun"
 PlanetName(3) = "Mars"
 PlanetName(4) = "Mercury"
 PlanetName(5) = "Jupiter"
 PlanetName(6) = "Saturn"
 PlanetName(7) = "Neptune"
 PlanetName(8) = "Uranus"
 PlanetName(9) = "Pluto"
 PlanetName(10) = "Venus"
  
 PlanetDeg(1) = Cells(2, 4)
 PlanetDeg(2) = Cells(2, 5)
 PlanetDeg(3) = Cells(2, 6)
 PlanetDeg(4) = Cells(2, 7)
 PlanetDeg(5) = Cells(2, 8)
 PlanetDeg(6) = Cells(2, 9)
 PlanetDeg(7) = Cells(2, 10)
 PlanetDeg(8) = Cells(2, 11)
 PlanetDeg(9) = Cells(2, 12)
 PlanetDeg(10) = Cells(2, 13)
 
 For i = 1 To 10
  If Abs(O_D - PlanetDeg(i)) <= 2 Then
   If i = 1 Then
    e_p = PlanetName(i)
   Else
    e_p = e_p & "," & PlanetName(i)
   End If
  End If
 Next i
 Planet = e_p
End Function

Hi thank you for your post. But it didn't worked.
It shows #NAME error.

 

[HotRhodium]

Sorry that really isn't enough information. Is there a code error?, if so what line?, are you putting this function in a cell? are you sure it is calling correctly? It sounds like your using this as a UDF and the function is either not in the rite place (not in a standard module probably should also put Public in front of it as well) or is misspelled. Hope that helps

 

[Sun11]

Sorry that really isn't enough information. Is there a code error?, if so what line?, are you putting this function in a cell? are you sure it is calling correctly? It sounds like your using this as a UDF and the function is either not in the rite place (not in a standard module probably should also put Public in front of it as well) or is misspelled. Hope that helps

No I am sorry, here is the more details:

No its not a Code error.
Yes I am putting this function in a Cell.
Hopefully I am calling it correctly. In spreadsheet data are stored in following manner:

O_D	Moon	Sun	Mars	Formula Checker
Data	Data	Data	Data	=Planet(A2)

<tbody>
</tbody>

what is UDF?

I removed Public from the front of the code, so now it shows "Blank" when I press Enter.

Hope now its helpful to understand.
Thank you again.

 

[Sun11]

Yes I got the meaning of UDF, yes it is User Defined Function.