| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA - passing Variables to subroutines
HI I'm having a problem passing variable values around ..
I've done this Option Explicit Public Gemloc As Integer Public Gemnumloc As Integer Private Sub Gemwon2_Click() Sheets("Rift_raid").Visible = True Gemloc = 13 'Column M Gemnumloc = 4 ' Row 4 Gemlist.Show End Sub Gemlist is a form that has a list box where you can select an item and on submit uses this code Sub SubmitButton1_Click() Sheets("Rift_raid").Select Gemid ' calls a subroutine in the form that gives an item a value. Cells(Gemloc, Gemnumloc).Value = Gemname.Value Range("o1") = "" Gemname.Value = "" Gemname.Clear Gemlist.Hide End Sub What I want it to do is to place the Item name in Cell (Gemloc, Gemnumloc) and the item value in the next cell.. but for some reason both Gemloc and Gemnumloc are not getting passed from the Sheet to the form's subrountine.. Does this make sence? Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA - passing Variables to subroutines
hi
in gemwon2_click, you have....... Gemloc = 13 'Column M Gemnumloc = 4 ' Row 4 in submitbutton_click,you have...... Cells(Gemloc, Gemnumloc).Value = Gemname.Value this is backwards. if gemloc is the column and gemnumloc is the row it should be cell(gemnumloc,gemloc).value = gemname.value so the value is goint to d13 not m4. what is gemname. are you sure gemid is returning a value. i can't test that. regards FSt1 "Madduck" wrote: HI I'm having a problem passing variable values around .. I've done this Option Explicit Public Gemloc As Integer Public Gemnumloc As Integer Private Sub Gemwon2_Click() Sheets("Rift_raid").Visible = True Gemloc = 13 'Column M Gemnumloc = 4 ' Row 4 Gemlist.Show End Sub Gemlist is a form that has a list box where you can select an item and on submit uses this code Sub SubmitButton1_Click() Sheets("Rift_raid").Select Gemid ' calls a subroutine in the form that gives an item a value. Cells(Gemloc, Gemnumloc).Value = Gemname.Value Range("o1") = "" Gemname.Value = "" Gemname.Clear Gemlist.Hide End Sub What I want it to do is to place the Item name in Cell (Gemloc, Gemnumloc) and the item value in the next cell.. but for some reason both Gemloc and Gemnumloc are not getting passed from the Sheet to the form's subrountine.. Does this make sence? Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA - passing Variables to subroutines
Move those Public declarations to a General module.
And just because... Change the names to something meaningful and declare them as long's. Option Explicit Public GemRowLoc as long Public GemColLoc as long I would think it would make your code a bit easier to understand. And since integers can only go up to 32k (about), there's never a reason to use them in any dim statement. Madduck wrote: HI I'm having a problem passing variable values around .. I've done this Option Explicit Public Gemloc As Integer Public Gemnumloc As Integer Private Sub Gemwon2_Click() Sheets("Rift_raid").Visible = True Gemloc = 13 'Column M Gemnumloc = 4 ' Row 4 Gemlist.Show End Sub Gemlist is a form that has a list box where you can select an item and on submit uses this code Sub SubmitButton1_Click() Sheets("Rift_raid").Select Gemid ' calls a subroutine in the form that gives an item a value. Cells(Gemloc, Gemnumloc).Value = Gemname.Value Range("o1") = "" Gemname.Value = "" Gemname.Clear Gemlist.Hide End Sub What I want it to do is to place the Item name in Cell (Gemloc, Gemnumloc) and the item value in the next cell.. but for some reason both Gemloc and Gemnumloc are not getting passed from the Sheet to the form's subrountine.. Does this make sence? Thanks -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA - passing Variables to subroutines
Thanks Guys, I have changed the varible names as suggested... This script is far from complete, as I'm learning as I go.... as such I have a few "test outputs" from the form "Gemlist" I can output Gemname to a cell. What I can't seem to do is pass the value back to the main code... I did not want to paste the whole code , and clog up the forums, but here is the entire code behind the form "gemlist" Sub SubmitButton1_Click() Sheets("Rift_raid").Select Gemid Cells(GemRowloc, GemColloc).Value = Gemname.Value Range("o1") = "" Gemname.Value = "" Gemname.Clear Gemlist.Hide End Sub Sub Gemid() If Gemname.Value = "Boots" Then Gemvalue = 1 ElseIf Gemname.Value = "Gloves" Then Gemvalue = 2 ElseIf Gemname.Value = "Leggings" Then Gemvalue = 3 ElseIf Gemname.Value = "Chest" Then Gemvalue = 4 ElseIf Gemname.Value = "Helm" Then Gemvalue = 5 Else Gemvalue = 6 End If Range("o1") = Gemvalue ' for transporting the value out of the subroutine End Sub Private Sub userform_Activate() Gemname.AddItem ("Boots") Gemname.AddItem ("Gloves") Gemname.AddItem ("Leggins") Gemname.AddItem ("Chest") Gemname.AddItem ("Helm") Gemname.AddItem ("Shoulders") Gemname.AddItem ("Weapon") End Sub |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA - passing Variables to subroutines
Ohh... and the error I get is Object not defined in this row Cells(GemRowloc, GemColloc).Value = Gemname.Value both GemRowloc and GemColloc are empty, so the code can't reconcile it.... |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA - passing Variables to subroutines
Double check to make sure you've put those public variables in a General module.
They don't go behind a worksheet or behind ThisWorkbook or behind either userform. Madduck wrote: Ohh... and the error I get is Object not defined in this row Cells(GemRowloc, GemColloc).Value = Gemname.Value both GemRowloc and GemColloc are empty, so the code can't reconcile it.... -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA - passing Variables to subroutines
Scrap that... I think I get it .. you mean in the "modules" folder as opposed to "Microsoft Excel Objects" or "Forms" Thanks I'll move my option explicit... |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA - passing Variables to subroutines
How/where did you declare the GemValue variable?
Did you put it in that same general module along with GemRowLoc and GemColLoc? Option Explicit Public GemRowLoc As Long Public GemColLoc As Long Dim GemValue As Long Madduck wrote: Thanks Guys, I have changed the varible names as suggested... This script is far from complete, as I'm learning as I go.... as such I have a few "test outputs" from the form "Gemlist" I can output Gemname to a cell. What I can't seem to do is pass the value back to the main code... I did not want to paste the whole code , and clog up the forums, but here is the entire code behind the form "gemlist" Sub SubmitButton1_Click() Sheets("Rift_raid").Select Gemid Cells(GemRowloc, GemColloc).Value = Gemname.Value Range("o1") = "" Gemname.Value = "" Gemname.Clear Gemlist.Hide End Sub Sub Gemid() If Gemname.Value = "Boots" Then Gemvalue = 1 ElseIf Gemname.Value = "Gloves" Then Gemvalue = 2 ElseIf Gemname.Value = "Leggings" Then Gemvalue = 3 ElseIf Gemname.Value = "Chest" Then Gemvalue = 4 ElseIf Gemname.Value = "Helm" Then Gemvalue = 5 Else Gemvalue = 6 End If Range("o1") = Gemvalue ' for transporting the value out of the subroutine End Sub Private Sub userform_Activate() Gemname.AddItem ("Boots") Gemname.AddItem ("Gloves") Gemname.AddItem ("Leggins") Gemname.AddItem ("Chest") Gemname.AddItem ("Helm") Gemname.AddItem ("Shoulders") Gemname.AddItem ("Weapon") End Sub -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA - passing Variables to subroutines
Hi Dave,
thanks for your help on this.... yes this is the definitions so far.. Option Explicit Public strName As String Public strLogic As String Public IntNameset As Long Public strPaste As String Public Gemvalue As Long Public Raidnumber As String Public GemRowloc As Long Public GemColloc As Long I am sorry for being a noob, but when you say a General module... I'm not sure what you mean, if they don't go behind any of the sheets or the workbook, how does one set up a general module? that will follow the file if I was to send it home etc... "Dave Peterson" wrote: How/where did you declare the GemValue variable? Did you put it in that same general module along with GemRowLoc and GemColLoc? Option Explicit Public GemRowLoc As Long Public GemColLoc As Long Dim GemValue As Long Madduck wrote: Thanks Guys, I have changed the varible names as suggested... This script is far from complete, as I'm learning as I go.... as such I have a few "test outputs" from the form "Gemlist" I can output Gemname to a cell. What I can't seem to do is pass the value back to the main code... I did not want to paste the whole code , and clog up the forums, but here is the entire code behind the form "gemlist" Sub SubmitButton1_Click() Sheets("Rift_raid").Select Gemid Cells(GemRowloc, GemColloc).Value = Gemname.Value Range("o1") = "" Gemname.Value = "" Gemname.Clear Gemlist.Hide End Sub Sub Gemid() If Gemname.Value = "Boots" Then Gemvalue = 1 ElseIf Gemname.Value = "Gloves" Then Gemvalue = 2 ElseIf Gemname.Value = "Leggings" Then Gemvalue = 3 ElseIf Gemname.Value = "Chest" Then Gemvalue = 4 ElseIf Gemname.Value = "Helm" Then Gemvalue = 5 Else Gemvalue = 6 End If Range("o1") = Gemvalue ' for transporting the value out of the subroutine End Sub Private Sub userform_Activate() Gemname.AddItem ("Boots") Gemname.AddItem ("Gloves") Gemname.AddItem ("Leggins") Gemname.AddItem ("Chest") Gemname.AddItem ("Helm") Gemname.AddItem ("Shoulders") Gemname.AddItem ("Weapon") End Sub -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA - passing Variables to subroutines
Select your workbook's project in the VBE.
Then click on Insert (on the menu bar), then Module (plain old module). Check he http://www.contextures.com/xlVideos04.html#CopyCode http://www.contextures.com/xlvba01.html#Regular Move (not copy!) the declarations into that module. Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Madduck wrote: Hi Dave, thanks for your help on this.... yes this is the definitions so far.. Option Explicit Public strName As String Public strLogic As String Public IntNameset As Long Public strPaste As String Public Gemvalue As Long Public Raidnumber As String Public GemRowloc As Long Public GemColloc As Long I am sorry for being a noob, but when you say a General module... I'm not sure what you mean, if they don't go behind any of the sheets or the workbook, how does one set up a general module? that will follow the file if I was to send it home etc... "Dave Peterson" wrote: How/where did you declare the GemValue variable? Did you put it in that same general module along with GemRowLoc and GemColLoc? Option Explicit Public GemRowLoc As Long Public GemColLoc As Long Dim GemValue As Long Madduck wrote: Thanks Guys, I have changed the varible names as suggested... This script is far from complete, as I'm learning as I go.... as such I have a few "test outputs" from the form "Gemlist" I can output Gemname to a cell. What I can't seem to do is pass the value back to the main code... I did not want to paste the whole code , and clog up the forums, but here is the entire code behind the form "gemlist" Sub SubmitButton1_Click() Sheets("Rift_raid").Select Gemid Cells(GemRowloc, GemColloc).Value = Gemname.Value Range("o1") = "" Gemname.Value = "" Gemname.Clear Gemlist.Hide End Sub Sub Gemid() If Gemname.Value = "Boots" Then Gemvalue = 1 ElseIf Gemname.Value = "Gloves" Then Gemvalue = 2 ElseIf Gemname.Value = "Leggings" Then Gemvalue = 3 ElseIf Gemname.Value = "Chest" Then Gemvalue = 4 ElseIf Gemname.Value = "Helm" Then Gemvalue = 5 Else Gemvalue = 6 End If Range("o1") = Gemvalue ' for transporting the value out of the subroutine End Sub Private Sub userform_Activate() Gemname.AddItem ("Boots") Gemname.AddItem ("Gloves") Gemname.AddItem ("Leggins") Gemname.AddItem ("Chest") Gemname.AddItem ("Helm") Gemname.AddItem ("Shoulders") Gemname.AddItem ("Weapon") End Sub -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA - passing Variables to subroutines
It worked...
its all about the location of my explicit :) Thanks heaps for the help... "Dave Peterson" wrote: How/where did you declare the GemValue variable? Did you put it in that same general module along with GemRowLoc and GemColLoc? Option Explicit Public GemRowLoc As Long Public GemColLoc As Long Dim GemValue As Long Madduck wrote: Thanks Guys, I have changed the varible names as suggested... This script is far from complete, as I'm learning as I go.... as such I have a few "test outputs" from the form "Gemlist" I can output Gemname to a cell. What I can't seem to do is pass the value back to the main code... I did not want to paste the whole code , and clog up the forums, but here is the entire code behind the form "gemlist" Sub SubmitButton1_Click() Sheets("Rift_raid").Select Gemid Cells(GemRowloc, GemColloc).Value = Gemname.Value Range("o1") = "" Gemname.Value = "" Gemname.Clear Gemlist.Hide End Sub Sub Gemid() If Gemname.Value = "Boots" Then Gemvalue = 1 ElseIf Gemname.Value = "Gloves" Then Gemvalue = 2 ElseIf Gemname.Value = "Leggings" Then Gemvalue = 3 ElseIf Gemname.Value = "Chest" Then Gemvalue = 4 ElseIf Gemname.Value = "Helm" Then Gemvalue = 5 Else Gemvalue = 6 End If Range("o1") = Gemvalue ' for transporting the value out of the subroutine End Sub Private Sub userform_Activate() Gemname.AddItem ("Boots") Gemname.AddItem ("Gloves") Gemname.AddItem ("Leggins") Gemname.AddItem ("Chest") Gemname.AddItem ("Helm") Gemname.AddItem ("Shoulders") Gemname.AddItem ("Weapon") End Sub -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA - passing Variables to subroutines
It's not really the "option explicit" that's causing the trouble. It's the
location of the Public declarations. That line is a directive to VBA that you want it to yell at you if you don't declare any of the variables you use in that module. It's a very good idea to have "Option Explicit" at the top of each and every module--the userform modules, the worksheet modules, the ThisWorkbook module, and the plain old general modules. Madduck wrote: It worked... its all about the location of my explicit :) Thanks heaps for the help... "Dave Peterson" wrote: How/where did you declare the GemValue variable? Did you put it in that same general module along with GemRowLoc and GemColLoc? Option Explicit Public GemRowLoc As Long Public GemColLoc As Long Dim GemValue As Long Madduck wrote: Thanks Guys, I have changed the varible names as suggested... This script is far from complete, as I'm learning as I go.... as such I have a few "test outputs" from the form "Gemlist" I can output Gemname to a cell. What I can't seem to do is pass the value back to the main code... I did not want to paste the whole code , and clog up the forums, but here is the entire code behind the form "gemlist" Sub SubmitButton1_Click() Sheets("Rift_raid").Select Gemid Cells(GemRowloc, GemColloc).Value = Gemname.Value Range("o1") = "" Gemname.Value = "" Gemname.Clear Gemlist.Hide End Sub Sub Gemid() If Gemname.Value = "Boots" Then Gemvalue = 1 ElseIf Gemname.Value = "Gloves" Then Gemvalue = 2 ElseIf Gemname.Value = "Leggings" Then Gemvalue = 3 ElseIf Gemname.Value = "Chest" Then Gemvalue = 4 ElseIf Gemname.Value = "Helm" Then Gemvalue = 5 Else Gemvalue = 6 End If Range("o1") = Gemvalue ' for transporting the value out of the subroutine End Sub Private Sub userform_Activate() Gemname.AddItem ("Boots") Gemname.AddItem ("Gloves") Gemname.AddItem ("Leggins") Gemname.AddItem ("Chest") Gemname.AddItem ("Helm") Gemname.AddItem ("Shoulders") Gemname.AddItem ("Weapon") End Sub -- Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA - passing Variables to subroutines
Then from there this is the code in the main section Private Sub Gemwon2_Click() Sheets("Rift_raid").Visible = True GemRowloc = 13 'Column M GemColloc = 4 ' Row 4 Gemlist.Show End Sub So what I want to happen is : I click a control button located in a cell next to a name of a user. that then brings up a form where I select the "gem" the script then returns then selceted Gem name and its underlying value in the cells next to the username. THe user name list is 30 long, therefore I'm trying to do this in subroutines .... ( i.e. I don't really want to replicate 30 forms that do the same thing :P ) Thanks heaps as usual , you guys rock. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Not at all clear on use of variables and/or object variables | Excel Discussion (Misc queries) | |||
| Passing Variables | Excel Discussion (Misc queries) | |||
| VBA passing variables through a function | Excel Discussion (Misc queries) | |||
| Passing variables between a form and macro | New Users to Excel | |||
| Passing variables between a form and macro | New Users to Excel | |||
Hybrid Mode
