How in the world does this thing run? Never seen variables defined outside the sub they are being used in.
Printable View
How in the world does this thing run? Never seen variables defined outside the sub they are being used in.
I donno but it does, if someone can tell me how to upload it I will so you guys can see for yourself.
PM me with your emai address and I will give you mine. You can send it to me.
Doing that makes them available to every subroutine in the module.Quote:
Originally Posted by austenr
Yeah they'd all be global variables then.
No, Global is available across the project, these are module level variable. If you changed DIM to GLOBAL then it would become a global variable.
Inside a sub is only available to that sub unless the variables are passed around like so:
Alternatively you could Dim the MyVar outside the routines then it would not need passing back and forth.Code:Sub MySubHost()
Dim MyVar As String
MyVar = "Hello World"
Call MySubSlave(MyVar)
End Sub
Sub MySubSlave(MyVar As String)
MsgBox MyVar
End Sub
Finally if you wish to have MySubSlave on a different module you would need to either pass back and forth (as above) OR Global the variable.
Historically I have been known to have a GLOBALS module in some of my larger VBA apps. Here is an example.
Hope that explains it :).Code:Option Explicit
'***********************************************************************************************
'* *
'* MODULE: GLOBALS *
'* *
'***********************************************************************************************
'***********************************************************************************************
' This page is simply used to set up the global variables and define functions *
'***********************************************************************************************
' Declare for call to mpr.dll.
Declare Function WNetGetUser Lib "mpr.dll" _
Alias "WNetGetUserA" (ByVal lpName As String, _
ByVal lpUserName As String, lpnLength As Long) As Long
Global Const NoError = 0 'The Function call was successful
Global Const RepVer As String = "1.7"
Global DatabaseUserName As String
Global DatabasePassword As String
Global ReportName As String
Global RunTimeReportID As Long
Global FuncName As String
Global Param1 As String
Global Param2 As String
Global Param3 As String
Global Param1Format As String
Global Param2Format As String
Global Param3Format As String
Global FuncReturnSTR As String
Global FuncReturn As String
Global CancelRoutine As Boolean
Global DirName As String
Global DSPListString As String
Global FormatListString As String
Global ClientModeString As String
Global SmartWarningsString As String
Global TimeInSeconds As Long
Global TimeInTime As String
Global Itemlist() As String
Global lItem As Long
Global Aggregator_Figures As Long
Global DBClient() As String
Global ConfigListVar() As String
Global ConfigListString As String
Global Debug_On As Boolean
Global DBClientList As String
Global NoArtist As Boolean
Global NoConfig As Boolean
Global CustList As Long
Global ProdList As String
Global Affiliate As String
Global ArtistString As String
Global SelectedCountry As String
Global Summary(7) As String
Global CompleteProdList() As String
Global CurrentUser As String
Global AdminReportName() As String
Global AdminProjectName() As String
Global AdminProductName() As String
Global EditAllowed As String
Global ReportOwner As String
Global MaxArtRow As Long
Global LogNum As Long
Global ProdForProj() As String
Global ProdForProjArtist() As String
Global ProdForProjTitle() As String
Global ProdForProjConfig() As String
Global ProjForProj As String
Global UnmatchedProjCode As String
Global ListBoxList() As String
Global ListBoxSQL As String
Global SalesMonthValid As Boolean
Global NewProjCodeValidate As String
Global vch_project_code As String
Global vch_name As String
Global NewProject As Boolean
Global ActiveUserRealName As String
Global n_mgr_id As Long
Global vch_artist_name As String
Global n_division As Long
Global vch_format As String
Global n_units As Long
Global vch_label_type As String
Global vch_price_point As String
Global vch_marketing_comment As String
Global vch_source As String
Global vch_signing_country As String
Global n_start_month As Long
Global ProjCreated As Boolean
Global ReturnValue As String
Global ProjCode As String
Global CurrentRepVer As String
Global GetiTunesDaily As Boolean
Global SoftChange As Boolean
Global RAProdTitle As String
Global Const BIF_RETURNONLYFSDIRS As Long = &H1
Global Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Global Const BIF_RETURNFSANCESTORS As Long = &H8
Global Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Global Const BIF_BROWSEFORPRINTER As Long = &H2000
Global Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Global Const MAX_PATH As Long = 260
Type BrowseInfo
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszINSTRUCTIONS As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type
Type SHFILEOPSTRUCT
hwnd As Long
wFunc As Long
pFrom As String
pTo As String
fFlags As Integer
fAnyOperationsAborted As Boolean
hNameMappings As Long
lpszProgressTitle As String
End Type
Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _
ByVal pidl As Long, _
ByVal pszBuffer As String) As Long
Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _
lpBrowseInfo As BrowseInfo) As Long
Hi Goallie,
What you had orinally was almost working; at least it was producing a result, and I guided you as to how to correct the syntax errors. You have now rewritten the code to do something different, but still doesn't work.
This is your assignment and I have no interest in a right/wrong result. I don't have time to waste though, on your moving goal posts.
Good luck.
MD
Please don't abandon me, I am trying, but when you don't have much experience doing this it takes a while. And this is what the code is supposed to do, it should have had those functions in the first place but I was just trying to debug it first.
If you can just tell me what type of declaration to use I believe that will solve it, but I've tried making just about everything a "single, double, integer or long" and it doesn't correct it. I've gone back to the original program to try making it work perfectly before putting the functions in.
OK GREAT! I've gotten it to work! Now I just need to make some charts from it.
Thanks so much for all the help guys, I can't tell you how much I appreciate it.
Can you post your finished code?
Sure, basically what I did was go back to the original after reading your post, move the sub to below the declarations, add a "+1" to the cells that read nDips, add the functions in, and change some of the declarations to double.
[VBA]Dim Rs As Double
Dim iRow As Double
Dim iCol As Integer
Dim nDips As Double
Dim Cuph As Double
Dim Rc As Integer
Dim Volc As Double
Dim Vols As Double
Dim Hn As Integer
Dim Pi As Double
Private Sub CommandButton1_Click()
Pi = 3.141592654
'Initial Straw Radius and column number
Rs = 0.2
iCol = 1
'Clear work area
Range("A1:N2000").Clear
'iterate through straw sizes
Do While Rs <= 0.5
'initialize data
nDips = 0
iRow = 2
Cuph = 10
Cells(1, iCol) = "Rs=" & Rs
'Column Reader
Cells(iRow, iCol) = "nDips"
Cells(iRow, iCol + 1) = "Cup Height"
iRow = iRow + 1
'write initial values
Cells(iRow, iCol) = nDips
Cells(iRow, iCol + 1) = Cuph
iRow = iRow + 1
'iterate til final cup height is established
Do While Cuph >= 0.5
'Calculate cup volume using cup radius and water level
CupVolume
'Calculate straw volume using straw radius and water level
StrawVolume
'Get new cup volume after removing the water in the straw
NewCup
'Calculate new water level
NewWater
nDips = nDips + 1
Cells(iRow, iCol) = nDips
Cells(iRow, iCol + 1) = Cuph
iRow = iRow + 1
Loop
iCol = iCol + 2
Rs = Rs + 0.05
Loop
End Sub
Function CupVolume()
Volc = Pi * 16 * Cuph
End Function
Function StrawVolume()
Vols = Rs * Cuph
End Function
Function NewCup()
Volc = Volc - Vols
End Function
Function NewWater()
Cuph = Volc / (Pi * 16)
End Function
[/VBA]
Well it look a sensible solution, unfortunately it's wrong (based on your comments)
Re your variables, it's neater and more checkable to group the types.
There is no advantage in Interger over Long, so always use Long
Single/Double may have accuracy issues. If you don't need it, go with Single. You can test the difference.
Use a Constant declaration for the likes of Pi.
You might also want to make your cup diameter a constant. It's better to get sizes etc. at the head of your code, so if you need a different calculation there is only one change. You don't need to go looking for 16 (in this case) in multiple places, if the problem changes.
[vba]
Option Explicit
Dim iRow As Long
Dim iCol As Long
Dim nDips As Long
Dim Rs As Single
Dim Cuph As Single
Dim Volc As Single
Dim Vols As Single
Const pi = 3.141592654
Private Sub CommandButton1_Click()
[/vba]
I copied your code and put it in place of mine and it works but it only goes to Rs = 0.45 for me, where it needs to go until Rs = 0.5
I don't think it should do that as there is still the While Rs <=0.5 loop in there. What would cause it to stop there instead of continuing?
Use Double, it fixes the problem. It must be some rounding error
Dim Rs As Double
Trying both produces different results for RS. Interesting.
'Single
0.2
0.25
0.25
0.3
0.3
0.35
0.35
0.4
0.4
0.45
0.45
0.5000001
'Double
0.2
0.25
0.25
0.3
0.3
0.35
0.35
0.4
0.4
0.45
0.45
0.5
0.5
0.55
Odd... I'll change it to double later today and see how that works.
Thanks again for the help.
Have you found your error?
Yeah just now changed it and it works now, goes all the way to 0.5
Yet again, I really appreciate all the help everyone.
Hi Goalle11,
I realize that this is for school, so do not want to hint too broadly, but I think the mistake(s) Malcom referred to are not at the changing value of rs.
Maybe this will help. What does 16 represent?
Mark
By the way, I am guessing here, but if the workbook doesn't have a bunch of other stuff, I'm betting that the 2.82mb size is after running the code and the sheet is full of values. If that is the case, clear the sheet, Save and close. The wb should be down to an attachable size.Quote:
Originally Posted by Goallie11
You can also zip the wb and attach a zip.
Mark
There are two overflow possibilities here:
1. Somewhere in the code you have a '+' where you should have a '-'. This is because rather than removing water from the cup you are adding water to the cup. This will inveitably lead to the situation you describe.
2. The vessel which you're emptying the straw into is smaller than the volume you need to remove from the cup.
Quote:
Originally Posted by Goallie11