Public Function Increase(x As Integer, y As Integer) As Integer Increase = (y + x) End Functionand 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?

# Function in VBA

Page 1 of 1## 6 Replies - 768 Views - Last Post: 05 April 2013 - 11:38 PM

### #1

# 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

##
**Replies To:** Function in VBA

### #2

## 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.

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

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

### #3

## 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

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

### #4

## 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.

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.

### #5

## Re: Function in VBA

Posted 05 April 2013 - 09:51 AM

The implementation code is

=Increase(B2, B8)

### #6

## 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

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

### #7

## 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).

Page 1 of 1