Assign a value to a variable

[mchac]

Above the following statements, BAL_SHT_TAB_NAME has been set and I can see in the watch window as I step through one loop that
the second test in the If Then statement InStr(1, Worksheets(BAL_SHT_TAB_NAME).Range("A2").Value, "Millions", vbTextCompare) = 4. So I would think the Then statement would be executed and set BAL_SHT_UNITS to a value of 1000000

My problem is that even though I can see VB execute the Then statement, the BAL_SHT_UNITS value is still 0 after execution.

If anyone sees an obvious error I would appreciate some help.

Alternatively, if anyone can suggest how I can track down the problem I would appreciate it.



Dim BAL_SHT_UNITS As Long

BAL_SHT_UNITS = 0

If InStr(1, Worksheets(BAL_SHT_TAB_NAME).Range("A2").Value, "Thousands", vbTextCompare) > 0 Then
BAL_SHT_UNITS = 1000

ElseIf InStr(1, Worksheets(BAL_SHT_TAB_NAME).Range("A2").Value, "Millions", vbTextCompare) > 0 Then
BAL_SHT_UNITS = 1000000

Else: BAL_SHT_UNITS = 1
End If

 

[rjwebgraphix]

I can't tell you why it isn't working right for you. I'd double check the sheet names. This is what I used and it worked just fine.


Of course, "Log" is just the sheet name that I used.

Dim BAL_SHT_UNITS As Long
Dim BAL_SHT_TAB_NAME As String

BAL_SHT_TAB_NAME = "Log"
BAL_SHT_UNITS = 0

If InStr(1, Worksheets(BAL_SHT_TAB_NAME).Range("A2").Value, "Thousands", vbTextCompare) > 0 Then
BAL_SHT_UNITS = 1000

ElseIf InStr(1, Worksheets(BAL_SHT_TAB_NAME).Range("A2").Value, "Millions", vbTextCompare) > 0 Then
BAL_SHT_UNITS = 1000000

Else: BAL_SHT_UNITS = 1

End If

MsgBox BAL_SHT_UNITS

 

[mchac]

Thanks RJ.
It was really odd. I ran through some loops where the InStr search string was Thousands and it set BAL_SHT_UNITS to 1000 but the next loop where the search string was Millions didn't work.

Then I was finding that when I multiplied the BAL_SHT_UNITS (Dim as Long) by ACCTS_PAYABLE_VALUE (Dim as Long) I was getting "" but when I changed them both to Double from Long the multiplication worked ok. The statement is: ActiveCell.Offset(COUNTER, 9) = ACCTS_PAYABLE_VALUE * BAL_SHT_UNITS. I thought it was interesting that the inputs had to be sized to handle the result, at least in the case where the result isn't put in a variable.


In any case, I had interrupted execution a number of times decided to close the sheets then reopened and everything is running ok.

I'm going through and pulling the line items from the financial reports I downloaded a couple of days ago. Most of the niggles are sorted out so I hope to have this part done by tomorrow morning.

 

[rjwebgraphix]

Sounds like you're on your way to getting things sorted. While you're at it, if you have some free moments I have a suggestion for you.

The following links cover various variable naming conventions. I think, once you get used to it, using some form of naming convention will help you in the long run.

A little light reading (giggle)
Naming convention (programming) - Wikipedia, the free encyclopedia
Hungarian notation - Wikipedia, the free encyclopedia
Leszynski naming convention - Wikipedia, the free encyclopedia
CamelCase - Wikipedia, the free encyclopedia

Most of the time I'll use CamelCase or CamelCaseIdentifier. If I use an array I'll always use it something like MyVarArr. MyVar being the Variable name with Arr tacked on to the end of it so I can see its an array just by seeing the name. A lot of people use things like MyStringStr as well.

One plus side of using CamelCase, or any of the other naming methods is that you can quickly and easily identify a misspelled variable in the editor.

For instance, lets say I have a variable named NuPeopleLng, short for Number of People and its type is Long. If I type that in the editor as nupeoplelng, as long as it has been declared (Dim NuPeopleLng as Long), it will automatically make the N, P and L in uppercase. however, if I mispelled it and typed nupoeplelng, it would not. So you can quickly identify that you misspelled it.

I always stick to CamelCase for that reason. I don't always throw an identifier in, it just depends on what it is and how recognizable it is for me. Simple things like a lone "i" are obvious counters and don't need to specifically say iLng. Although I have also used Cnt for a counter instead of long.

For instance, In my project I have a routine that adds existing stores to an array to compare to the incoming data to check for new stores. When I build the array, I have to use a counter to keep track of how many was entered into the array, so the counter for the array is Dim ExistStrmCnt as Long. Where it is Long and I could use Lng, it also has a specific purpose of counting and nothing more, so I use Cnt instead of Lng.

Now I'm typing just as much as what's in the articles, so I'll shut up now. It's something to think about as you continue to build your VBA skills.

RJ

 

[mchac]

Thanks for these. I will read. I guess Tom Clancey has been preempted.

This project has been quite a crash course in VBA. A number of years ago I used it quite a bit never learned it formally so there are a lot of holes in my knowledge.

 

[mchac]

RJ, just an aside, the list of entities I used to pull the financials from the SEC website had number of duplicates due to the source of the data (I would have saved myself some processing time if I had thought to remove them first) but in trying to eliminate them I happened accross the .RemoveDuplicates functionality. Holy &%$# that's fast.