Assign a value to a variable

mchac

Well-known Member
Joined
Apr 15, 2013
Messages
531
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

rjwebgraphix

Well-known Member
Joined
May 25, 2010
Messages
588
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.
Code:
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

Well-known Member
Joined
Apr 15, 2013
Messages
531
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

Well-known Member
Joined
May 25, 2010
Messages
588
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

Well-known Member
Joined
Apr 15, 2013
Messages
531
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

Well-known Member
Joined
Apr 15, 2013
Messages
531
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,104
Messages
5,599,753
Members
414,335
Latest member
cleverExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top