Need help in modifying an Excel VB script

Need help in finding a user entered value closest to the average of al

Page 1 of 1

6 Replies - 5546 Views - Last Post: 28 February 2007 - 01:07 PM Rate Topic: -----

#1 sannuest  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 26-February 07

Need help in modifying an Excel VB script

Posted 26 February 2007 - 02:17 PM

Hello,
I have gotten an assignment at school. The assignment is to create an Input box, into which the user enters as many values as they desire. Then the program sums all of the values and finds the total average. I have managed to code this. But the program also has to find, from the entered values, the closest one to the total average.
In the code below the element I am not able to make the program find is marked with C.

Sub k1() 'Find the sum of all the entered values and the entered value closest to the average.
	Dim k#, S#, C#
	k = 0: S = 0
	Do
		x = Val(InputBox("Please enter a number", "Assignment 1"))
		If x = 0 Then Exit Do
		k = k + 1: S = S + x
	Loop
		Sk = S / k
	MsgBox "The averag sum is: " & Sk
	MsgBox "The value closest to the average is: " & C
	MsgBox "Total number on entered values: " & k
End Sub


Sorry for the buggy grammar.
I thank all of you for your assistance! :)

This post has been edited by sannuest: 26 February 2007 - 02:43 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Need help in modifying an Excel VB script

#2 tody4me  Icon User is offline

  • Banned
  • member icon

Reputation: 13
  • View blog
  • Posts: 1,398
  • Joined: 12-April 06

Re: Need help in modifying an Excel VB script

Posted 26 February 2007 - 03:16 PM

First, if option explicit were turned on it wouldn't compile - SK is not defined.
Second, C is never used, therefore C would never come up with anything.
Do you want C to actually be the integer part of the average? If so, you would do CInt(SK) to get C.
Was This Post Helpful? 0
  • +
  • -

#3 NickDMax  Icon User is offline

  • Can grep dead trees!
  • member icon

Reputation: 2255
  • View blog
  • Posts: 9,245
  • Joined: 18-February 07

Re: Need help in modifying an Excel VB script

Posted 26 February 2007 - 08:47 PM

Using Option Explicit at the begining of all of your modules helps detect a lot of dumb errors.

I see two problems with your code. #1 It never stores the integers so there is no real way to tell what number entered would be closest to the average. #2 it does not use excel at all, you could paste this code into a VBS file, why not use Excel for excel. As the user enters in integers, put them into a cell in a given column (lets say A). Have another cell (say B:1) contain a formula to sum column A. You can then search the column A for values closest to the average.

I mean if the purpose of the class is to learn about Excel, then use excel to do your work for you.
Was This Post Helpful? 0
  • +
  • -

#4 sannuest  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 26-February 07

Re: Need help in modifying an Excel VB script

Posted 27 February 2007 - 12:05 AM

View PostNickDMax, on 26 Feb, 2007 - 08:47 PM, said:

I see two problems with your code. #1 It never stores the integers so there is no real way to tell what number entered would be closest to the average.
I mean if the purpose of the class is to learn about Excel, then use excel to do your work for you.


How would I store the integers?
The purpose of this assignment is to learn VB, not Excel. That is the reason why I use InputBox. We have only had a few lessons so I really have no idea how to go on with this. If someone would be kind enough as to show me a certain code then I hope I might manage. I am good in learning from examples.

Thank You!

e: I just figured out what you meant by using Excel. I will try this as soon as I get back home from work. Thanks again :)

This post has been edited by sannuest: 27 February 2007 - 12:15 AM

Was This Post Helpful? 0
  • +
  • -

#5 NickDMax  Icon User is offline

  • Can grep dead trees!
  • member icon

Reputation: 2255
  • View blog
  • Posts: 9,245
  • Joined: 18-February 07

Re: Need help in modifying an Excel VB script

Posted 27 February 2007 - 01:33 AM

If we store the numbers in an array then we can look for the minimum distance from the average. We can't do this on the fly, since the average changed with each sample added.

Here I have updated your code. I renamed your variables to something a little easier to read.

Option Explicit

Sub k1() 'Find the sum of all the entered values and the entered value closest to the average.
	Dim Number As Long, Average As Long, Total As Long
	Dim Count As Long
	Dim NumArray(256) As Long, i As Long
	Dim MinDist As Long, Closest As Long
	
	Count = 0: Total = 0
	Do
		Number = Val(InputBox("Please enter a number", "Assignment 1"))
		If Number = 0 Then Exit Do
		Count = Count + 1: Total = Total + Number
		NumAry(Count) = Number
	Loop
	Average = Total / Count
	MinDist = Distance(Average, NumAry(0))
	For i = 1 To Count
		If Distance(Average, NumAry(i)) < MinDist Then
			Closest = NumArt(i)
			MinDist = Distance(Average, NumAry(i))
			End If
	Next i
	MsgBox "The averag sum is: " & Average
	MsgBox "The value closest to the average is: " & Closest
	MsgBox "Total number on entered values: " & Count
End Sub

Function Distance(x As Long, y As Long)
	Distance = Abs(y - x)
End Function

This post has been edited by NickDMax: 27 February 2007 - 01:45 AM

Was This Post Helpful? 0
  • +
  • -

#6 m2s87  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 21
  • View blog
  • Posts: 390
  • Joined: 28-November 06

Re: Need help in modifying an Excel VB script

Posted 27 February 2007 - 08:51 AM

If it is an assignments, then just do what it says. But do notice that maybe teacher would like you to make an OOP styled solution. It would probably be something like:

In module:
Option Explicit
dim andmepank as new hoidla

Private Sub Main()
	dim teade1 as string
	dim teade2 as string
	dim teade3 as string
	dim i as integer
	dim j as integer
	dim k as integer
	dim keskmine as integer

	Do
		andmepank.lisa(Val(InputBox("Sisesta number", "Assignment 1")))
	Loop Until andmepank.ViimaneKirjeOliTyhi

  if andmepank.kirjeid=0 then 
			teade1="Sisendid puuduvad"
			teade2="Sisendid puuduvad"
  else
	select case andmepank.kirjeid
		keskmine=andmepank.Kokku/andmepank.kirjeid

		case 1
			teade1="Keskmine on: " & str(keskmine)
			teade2="Lähim keskmisele on: " & str(keskmine)
		case else
			k=1
			l2him=andmepank.kirje(k)

			for j=2 to andmepank.kirjeid
				if lahim>abs(andmepank.kirje(j)-keskmine) then k=j
			Loop

			teade1="Keskmine on: " & str(keskmine)
			teade2="Lähim keskmisele on: " & str(andmepank.kirje(k))
	end select

	teade3="Sisendeid on:" & i

	call v2ljasta(teade1,teade2,teade3)
end sub

private sub v2ljasta(byval x1 as string,byval x2 as string,byval x3 as string)
	MsgBox x1 & chr(13) & x2 & chr(13) & x3 & chr(13)
end sub


And now make a class called hoidla
Option Explicit
Dim palju as integer
Dim massiiv() As integer

public sub Lisa(byval v22rtus as integer)
	rem Add new value
	rem add your code here
end sub

public function Kirjeid() as integer
	rem Retrieve specific value from array
	rem add your code here
end sub

public function Kokku() as integer
	rem Retrieve the sum of array
	rem add your code here
end sub

public function ViimaneKirjeOliTyhi() as boolean
	rem Retrieve True if last entry was 0 
	rem add your code here
end sub

private sub Suurendamassiivi()
	rem Increase the size of array
	rem add your code here
end sub


Was This Post Helpful? 0
  • +
  • -

#7 sannuest  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 26-February 07

Re: Need help in modifying an Excel VB script

Posted 28 February 2007 - 01:07 PM

Thank you all for your help!
I studied NickDMax's code, and understood it fully :^:
I also looked at m2s87's code and it took me some handling before I understood :P. But this kind of a solution is too complicated for my class to understand yet and besides I have to explain what the code does perfectly. Very good script to learn from though.
Thanks again.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1