6 Replies - 517 Views - Last Post: 05 April 2013 - 11:38 PM Rate Topic: -----

#1 amture106  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 154
  • Joined: 08-September 11

Function in VBA

Posted 05 April 2013 - 07:10 AM

Hello all I've written this excel function for adding value to the existing value
Public Function Increase(x As Integer, y As Integer) As Integer
Increase = (y + x)
End Function

and when x = 1 y should be 1 for the first time then when x = 4 in the second time y should = 5, but when x = 1 y = 100 for the first time then when x = 4 in the second time y = 500, so how may I fix that?
Is This A Good Question/Topic? 0
  • +

Replies To: Function in VBA

#2 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3608
  • View blog
  • Posts: 12,397
  • Joined: 12-December 12

Re: Function in VBA

Posted 05 April 2013 - 07:20 AM

I really don't understand what you are trying to achieve, but if you want a function to retain a value between calls then you would need a Static variable.

Public Function DemoStatic() As Integer
    Static iValue As Integer

   iValue = iValue + 1
   DemoStatic = iValue
End Function

Static variables initially have their default value, which for an integer is 0.

This post has been edited by andrewsw: 05 April 2013 - 07:21 AM

Was This Post Helpful? 0
  • +
  • -

#3 amture106  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 154
  • Joined: 08-September 11

Re: Function in VBA

Posted 05 April 2013 - 08:03 AM

What I'm trying to do is I have two fields. Lets call the first one X and the second one Y. X will vary depending on the user input. X can be 1, 2, 3 or any number that the user input. Y will start as ZERO, but will keep increasing depending on the value of X. for example the first run of the function will be like this:
X = 0
Y = 0
---------
X = 5
Y = 5
---------
X = 3
Y = 8
---------
X = 2
Y = 10

and so on, but what's happening in my code is like this.

X = 0
Y = 0
---------
X = 5
Y = 500
---------
X = 3
Y = 800
---------
X = 2
Y = 1000

and so on, so how to fix that
Was This Post Helpful? 0
  • +
  • -

#4 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 300
  • View blog
  • Posts: 1,781
  • Joined: 26-March 09

Re: Function in VBA

Posted 05 April 2013 - 09:47 AM

I think you need to show how you're using the function (i.e.: some code how you call it), as it's a little confusing.

The function basically returns the sum of the two inputs, so we need to see what you're doing with the return value in order to help.
Was This Post Helpful? 0
  • +
  • -

#5 amture106  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 154
  • Joined: 08-September 11

Re: Function in VBA

Posted 05 April 2013 - 09:51 AM

The implementation code is
 =Increase(B2, B8)

Was This Post Helpful? 0
  • +
  • -

#6 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Function in VBA

Posted 05 April 2013 - 01:54 PM

What's on the other side of that = ?

In order to increment B8 by B2, B8 would have to = Increase(B2, B8)

B8 = 5
B2 = 2
B8 = Increase(B2, B8)

inside the function
Increase = 2+5

That would return 7
So B8 should now = 7
Was This Post Helpful? 0
  • +
  • -

#7 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 300
  • View blog
  • Posts: 1,781
  • Joined: 26-March 09

Re: Function in VBA

Posted 05 April 2013 - 11:38 PM

As CharlieMay has asked (and basically, the same as I was asking), we need to see a little more code on how you're using it, not just the function call itself, but what happens with B8 and B2 and the returned result (i.e. all the code related to the use of this function and the variables around it).
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1