Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.

Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

# Excel: Creating formulas with Visual Basic

4 replies to this topic

### #1 petocities

petocities

• Members
• 102 posts
• OFFLINE
•
• Location:Santiago Chile
• Local time:02:45 AM

Posted 16 February 2011 - 11:05 AM

I'm trying to make a formula that checks a cell with list options, and then calculates a value. I'm not well versed in Visual Basic, so I don't fully understand why is not working.
My code is this:
```Function calculoVR(datos As Range, tipos As Range, dolar As Double, uf As Double)
Dim y As Integer
y = 0

For Each x In tipos
If x.Value = "Ch\$ Real ('000)" Then
calculoVR = calculoVR + datos(0, y) * 1000 / dolar
y = y + 1
ElseIf x.Value = "UF" Then
calculoVR = calculoVR + datos(0, y) * uf / dolar
y = y + 1
ElseIf x.Value = "US" Then
calculoVR = calculoVR + datos(0, y)
y = y + 1
End If
Next
End Function
```

As far as I've been able to check, the "If" statements aren't checking correctly the value, hence not calculating the result.
Any help? The problem is a bit more complicated than this, but I don't wanna ramble too much :/

### #2 groovicus

groovicus

• Security Colleague
• 9,963 posts
• OFFLINE
•
• Gender:Male
• Location:Centerville, SD
• Local time:11:45 PM

Posted 16 February 2011 - 11:55 AM

I don't know what "why is not working" means, other than something is happening that you didn't expect. Are you getting an error message, an incorrect result, etc?

I do not use VB, but one thing I noticed is that you are not declaring a type for 'x'. That might be causing a problem.
http://msdn.microsoft.com/en-us/library/5ebk1751%28v=vs.80%29.aspx

Other than that, verify that the values being passed to the function are valid.

### #3 petocities

petocities
• Topic Starter

• Members
• 102 posts
• OFFLINE
•
• Location:Santiago Chile
• Local time:02:45 AM

Posted 16 February 2011 - 03:05 PM

I assume the values are ok (since i'm passing them myself as tester), although I do not know if I am handling them correctly. Also, I tried to declare x in the same "for each" statement, but excel didn't let me (it was expecting "In" instead of a declaration).
Since I don't know VB a lot, I think I might be missing something pretty basic, or using something incorrectly

### #4 groovicus

groovicus

• Security Colleague
• 9,963 posts
• OFFLINE
•
• Gender:Male
• Location:Centerville, SD
• Local time:11:45 PM

Posted 16 February 2011 - 03:28 PM

Never assume. Verify.

### #5 JosiahK

JosiahK

• Members
• 269 posts
• OFFLINE
•
• Gender:Male
• Local time:06:45 AM

Posted 16 February 2011 - 03:45 PM

As an example, could you say what the test data you're using is? That alone would help explain what it is you're trying to do.
And as Groovicus points out, an explanation of what is going wrong would help too.

An experienced programmer can often follow the code to find out what it does, but it much harder to find out what it was meant to do when it isn't working. Comments (inserted after an apostrophe ' on a line to distinguish them from the code) help to explain your logic and putting them in often actually helps you to find your own solution.

Your "for each X in tipos" loop is correct for VBA, as the counter variable is implicitly declared when using For.
The one thing that jumps out at me as odd, and I can't quite say why, is datos(0, y). But until I really know what you're doing I can't say for sure.
Quod non mortiferum, fortiorem me facit.
I don't read minds. Please help everyone by answering any questions and reporting on the results of any instructions. Query any concerns and explain problems or complications.

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users