Advanced Excel VBA - Expert Only Please - Code?

Question

1) All the Excel cells referenced in the code below contain numbers
2) The existing code shown will run fine

The actual question is:
How can I change the line below from

A) debt = (Range("G" & column).Value * 1)
to
B) debt = debt + (Range("G" & column).Value * 1)

without causing the code to fail.

How do I add "debt" to itself plus the value in the cell specified. I tried Casting both inputs...making a String variable for the Range input, but nothing worked. This is completely perplexing to me.


Function CALLED(ParamArray list() As Variant) As Integer

Dim code As Variant
Dim column As Integer
Dim debt As Integer

column = 1
debt = 3

For Each code In list
Do While (Range("A" & column).Value <> (code * 1))
column = column + 1
Loop
If (Range("E" & column).Value <> "CALLED") Then
debt = (Range("G" & column).Value * 1)
End If
column = 1
Next code

CALLED = debt

End Function

The first proposed answer did not work.

debtHolder = 0
debtHolder = Range("G" & column)
debt = debt + debtHolder

The answer turns out to be making the variables Doubles. But I have no idea why. The values in the cells were all integers and casting them as Integers also worked on a standalone basis. Why??

Answer 1

I agree with the preceding answer, as long as the cells in 'list' and 'G' all contain integers

Answer 2

You have several flaws in your code. According to your note, I see the reason you are getting an answer that is Double and not an Integer. Here's the reason why:

debtHolder = 0
debtHolder = Range("G" & column)
debt = debt + debtHolder

Notice that you are setting debtHolder equal to a range's value. Excel's default data type for numbers is Double. When you add an Integer (debt) to a Double (debtHolder), the answer it should give you is a Double. You really should specifiy debtHolder as an Integer data type.

Also you have nothing in place to handle any possible errors that might come up like if one of the numbers isn't a number or an integer. You also are using too many parenthesis. Range doesn't really need surrounded with parenthesis. And I'd recommend replacing column with row. It makes more sense, because you are moving down to the next row and not moving over to the next column.

Also your function arguments are a little confusing. I'm not sure why you have:

Function CALLED(ParamArray list() As Variant) As Integer

Did you leave a comma out?

Should it be?

Function CALLED(ParamArray, list() As Variant) As Integer

I'm thinking you only need one of the following:

Function CALLED(list() As Variant) As Integer

or

Function CALLED(ByVal list() As Variant) As Integer

Although I would probably used the first one (ByRef) since the list is the Variant data type and would take up more space.

Another thing I see that could be a problem with the code is that you don't specify an initial value for CALLED. Right after your declare your variables with your Dim statements you really should declare what the CALLED value is set to in case nothing is returned.

There are also some ways you could speed up your macro big time by transferring Range A and Range E to arrays.

You also have to be careful about using just the plain Value property for evaluation. A cell could possibly be formatted as text and have a number in it. If you use the value property, it will not evaluate that number as a number, but rather it will evaluate it as text because Excel adds an apostrophe to the front of it. It's a good idea to use IsNumeric to determine if the value is a number, then use the Val function to evaluate the value as a number. Then since you are looking at integers you probably should use the Mod operator to determine if a remainder other than zero is returned and the number isn't an Integer.

You have several ways that your Function could result in an Error and the user should never have to deal with VBA errors.

If you need some assistance feel free to email me. I'd be glad to help if I can be of assistance.

PS - A good way to go about fixing your code is to print your variable values to the Immediate Window. The Immediate Window can be brought up in the VBE window by pressing (Ctrl + G). So if at some point in your code you want to find out what the debt variable equals, then type in:

Debug.Print debt

You might want to use an Exit Sub here or there to figure out where and why the variable is being set to the wrong value, so you can stop it at that point and evaluate the variable value.

And yes you can do additions such as:

column = column + 1

That is common practice in VBA coding.

One last thing, use the Long data type instead of the Integer data type to specify the row number. It's not a good idea to use the integer type when referring to rows. Excel has over 65,000 rows and the Integer data type goes up to only 30,000 or so I think. The basic point is that the Integer data type won't go through all of the rows on the worksheet if you need it to. The Long data type is the data type that takes up the least amount of memory and will accomplish that task. For columns, you can use the Integer data type since there are only 256 columns in Excel.

Answer 3

You have several flaws in your code. According to your note, I see the reason you are getting an answer that is Double and not an Integer. Here's the reason why: debtHolder = 0 debtHolder = Range("G" & column) debt = debt + debtHolder Notice that you are setting debtHolder equal to a range's value. Excel's default data type for numbers is Double. When you add an Integer (debt) to a Double (debtHolder), the answer it should give you is a Double. You really should specifiy debtHolder as an Integer data type. Also you have nothing in place to handle any possible errors that might come up like if one of the numbers isn't a number or an integer. You also are using too many parenthesis. Range doesn't really need surrounded with parenthesis. And I'd recommend replacing column with row. It makes more sense, because you are moving down to the next row and not moving over to the next column. Also your function arguments are a little confusing. I'm not sure why you have: Function CALLED(ParamArray list() As Variant) As Integer Did you leave a comma out? Should it be? Function CALLED(ParamArray, list() As Variant) As Integer I'm thinking you only need one of the following: Function CALLED(list() As Variant) As Integer or Function CALLED(ByVal list() As Variant) As Integer Although I would probably used the first one (ByRef) since the list is the Variant data type and would take up more space. Another thing I see that could be a problem with the code is that you don't specify an initial value for CALLED. Right after your declare your variables with your Dim statements you really should declare what the CALLED value is set to in case nothing is returned. There are also some ways you could speed up your macro big time by transferring Range A and Range E to arrays. You also have to be careful about using just the plain Value property for evaluation. A cell could possibly be formatted as text and have a number in it. If you use the value property, it will not evaluate that number as a number, but rather it will evaluate it as text because Excel adds an apostrophe to the front of it. It's a good idea to use IsNumeric to determine if the value is a number, then use the Val function to evaluate the value as a number. Then since you are looking at integers you probably should use the Mod operator to determine if a remainder other than zero is returned and the number isn't an Integer. You have several ways that your Function could result in an Error and the user should never have to deal with VBA errors. If you need some assistance feel free to email me. I'd be glad to help if I can be of assistance. PS - A good way to go about fixing your code is to print your variable values to the Immediate Window. The Immediate Window can be brought up in the VBE window by pressing (Ctrl + G). So if at some point in your code you want to find out what the debt variable equals, then type in: Debug.Print debt You might want to use an Exit Sub here or there to figure out where and why the variable is being set to the wrong value, so you can stop it at that point and evaluate the variable value. And yes you can do additions such as: column = column + 1 That is common practice in VBA coding. One last thing, use the Long data type instead of the Integer data type to specify the row number. It's not a good idea to use the integer type when referring to rows. Excel has over 65,000 rows and the Integer data type goes up to only 30,000 or so I think. The basic point is that the Integer data type won't go through all of the rows on the worksheet if you need it to. The Long data type is the data type that takes up the least amount of memory and will accomplish that task. For columns, you can use the Integer data type since there are only 256 columns in Excel.

Answer 4

Hmmmm.... it seems like what you are trying to do should work. I frequently use expressions like 'total = total + num' without problem. Are you positive it is that line that is causing the issue? If so, the only thing I could think to try would be to declare another int variable to hold the value from the cell, and add it to the accumulating 'debt' with each pass.