Re: Objects as properties of a class - "Object variable not set" error...
Cheers.
Re: Objects as properties of a class - "Object variable not set" error...
Cheers.
Re: Objects as properties of a class - "Object variable not set" error...
Okay! Right - got it. Class_Initialize is called automatically... Phew - I *am* learning something other than how to copy code ;o)
Thanks and regds.
Re: Objects as properties of a class - "Object variable not set" error...
Thanks, I like it...
<edited to say> The subs need to be Public though, don't they? I can't initialise from my main code otherwise...?
Never having been taught OOP - just entering the margins through VBA - is this the normal way of doing things? Am I being naive in hoping that initialising the outermost object will sort out the inner objects?
<edited - yet again - to say>...and is it considered good-form to terminate the inner object, or can I just set the outer SQLClassMod object to Nothing when I've finished with it?
Re: Objects as properties of a class - "Object variable not set" error...
OK, I have one solution, but is this the only way? It seems laborious - and I can see it being a real pain in the neck if there is any more complexity to the classes, objects and properties:
Dim objSQL As SQLClassMod
Dim objInner as SQLQuery
Set objSQL = New SQLClassMod
Set objInner = new SQLQuery
objInner.SelectString ="XYZ"
Set obj.SQL = objInner
...
This can't be the only way - what if I didn't know the type of the SQLQuery object inside SQLClassMod and just knew that there was a SQLClassMod.objInner.SelectString property that I wanted to set????
Another simple one I can't work out...
I have created a simple class (SQLQuery):
Private strSelect As String
Public Property Let SelectString(ByVal str As String)
strSelect = str
End Property
Public Property Get SelectString() As String
SelectString = strSelect
End Property
...
Display More
I now want to use a SQLQuery object in another class (SQLClassMod):
Private objSQLQuery As SQLQuery
...
'Store and retrieve the SQL Query-string
Public Property Set Query(ByVal objQuery As SQLQuery)
Set objSQLQuery = objQuery
End Property
Public Property Get Query() As SQLQuery
Set Query = objSQLQuery
End Property
...
Display More
I then want to create a SQLClassMod object and set properties like this:
However, when I run the code, I get the dreaded 91 error - Object variable not set...
...presumable because the SQLQuery object hasn't been created... (?)
I've searched the forum and the web, and I'm none-the-wiser as to how to do this. I thought that the Set objSQL= New SQLClassMod would automatically create the (daughter?) object SQLQuery???
All help is appreciated, as usual... Thanks.
Re: Remove formulae from range leaving values
QuoteIf you were doing this 'manually' rather...
Doh! I've obviously been getting too-close to the problem...
QuoteHave you tried going to the Options menu..
Nope, I must confess not. I've tended to shy-away from Excels 'cosmetic' functions (other than a daliance with conditional-formatting).
Thanks - and thanks to Filo, but that solution still uses a loop, which is what I was trying to avoid.
<edited to say>OK, in practical-terms, Filo's solution actually works for me best so far - I don't want to hide zeros everywhere - in the context of this report they *do* have meaning in the data areas - it's just in the 'highlight' section where they crowd the important information.
I'm looking for a 'better way' of solving this problem - my code works at the moment, but is almost certainly inefficient due to my relative lack of experience...
For a dull reason (explained at the end), I need to fill a range with calculated values - or leave cells completely empty if the calculated values meet certain criteria. In the example below, I simply find the difference between the values in two columns - and if it is zero, remove the contents of the cell.
Sub subCompare()
Dim rngComp As Range
Dim aCell As Range
' create the range where my formulae (and values) will live
Set rngComp = Range("M6:S62")
' fill the range with a relative-reference formula
rngComp.FormulaR1C1 = "=RC[-7]-RC[7]"
' loop through the range, nuking zero-value cells and
' converting the remainder from formulae to values
For Each aCell In rngComp
If aCell.Value = 0 Then
aCell.Formula = ""
Else
aCell.Formula = aCell.Value
End If
Next aCell
End Sub
Display More
Is there a more-efficient way to perform the function of the loop?
Why do I want to do this? Well, it's to get around a 'feature' in Office 2003 - my pre-VBA versions of a certain report process used conditional formatting to set the font-colour to the same as the cell background colour if the cell value was zero (say) and highlight the rest - but if you copy a section of a spreadsheet and paste-special it into Word (as a picture), the conditional-formatting of the font colour fails - and you can see zeros all over the place.
Re: array property of a class object
Excellent! Thanks!
Now I can devote a little of the time I'd have wasted banging my head against the wall to understanding where I was going wrong and why this version works.
Marvelous.
Re: array property of a class object
I'm struggling. Again.
I'm trying to use the suggestions in this thread to solve something I'm trying to do - create a class object that can include an array.
My class code looks like:
Option Explicit
Private str() As Variant
Private intHMax As Integer
Public Sub loadheadings(headingArray() As Variant)
Dim intIndex As Integer
For intIndex = LBound(headingArray) To UBound(headingArray)
str(intIndex) = headingArray(intIndex)
Next
intHMax = intIndex
End Sub
Public Property Get NumHeads() As Integer
NumHeads = intHMax
End Property
Display More
And I am trying to use it thus:
Function testStats() As SQLClassMod
Dim testobj As SQLClassMod
Dim headArray() As Variant
Set testobj = New SQLClassMod
headArray = Array("Ref", "Ext.Ref", "Date Raised")
testobj.loadheadings headArray
'return the loaded object
testStats = testobj
End Function
Display More
I've tried all kinds of variations, but the best I can achieve is a
"Run time error :9 Subscript out of range" error on the
...line.
Can anybody spot where I'm going wrong??? I can't see how Svuille could have got his/her code to work.
Thanks and regds,
Re: Refresh pivottable where source data range has changed?
OK, I think I've solved it - and yes, it is simple - and rather too subtle for me:
Set tblPNLog = ActiveCell.CurrentRegion
tblPNLog.Select
tblPNLog.Name = "Fish"
checkCreateSheet ("Summary")
If Worksheets("Summary").PivotTables.Count = 0 Then
MsgBox ("creating")
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Fish").CreatePivotTable TableDestination:=Worksheets("Summary").Range("A1"), TableName:= _
"StatsSummary", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("StatsSummary").AddFields RowFields:="Status", _
ColumnFields:="Priority"
ActiveSheet.PivotTables("StatsSummary").PivotFields("Priority").Orientation = _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = False
Else
MsgBox ("Refreshing")
Set pvtCache = ActiveWorkbook.PivotCaches(1)
pvtCache.Refresh
End If
Display More
The change is to assign a name to the tblPNLog range and use that as the sourcedata item in the pivottable.
Other than being aware that named-ranges are a convenient way of referring to ranges that can change size, I fail to see how assigning a name has solved this problem - I was already giving the pivottable a dynamic range (tblPNlLog) to play with rather than a static range (e.g. "A1:C20")...
Heh-ho.
Re: Refresh pivottable where source data range has changed?
No-one?
If I'm being spectacularly-dense and missing the obvious, please put me out of my misery... ;o)
Re: Refresh pivottable where source data range has changed?
AAArgh!
I've made no progress with this, this afternoon.
I want to do one of three things (in decreasing order of desirability):
i) Change the source data range of an existing pivottable and refresh the table
ii) Delete an existing pivottable (and any caches etc.) and recreate it
iii) Delete the entire worksheet the pivottable is on *without* the user being prompted to confirm the deletion so that I can recreate the pivottable from scratch again...
Can anybody help?
Thanks and regds,
Myles
I have created a spreadsheet that queries an SQL database, creates a worksheet with the data returned and then creates a pivottable on another sheet to summarise the data.
If the sub/macro is fired off again, the SQL query is re-run and the code checks whether the pivottable still exists (it could have been deleted by the user) - if so, it is recreated. If not, I want to refresh the table.
I have been using:
...which is fine as long as the same number of records were returned by the SQL query.
What I want to know is - if the source data-range has changed (i.e. a different number of records were returned by the query) - how do I refresh the pivottable accordingly - or do I need to destroy and recreate it?
The full code at the moment is:
Dim pvtCache As PivotCache
Dim tblPNLog As Range
Set tblPNLog = ActiveCell.CurrentRegion
tblPNLog.Select
checkCreateSheet ("Summary")
If Worksheets("Summary").PivotTables.Count = 0 Then
MsgBox ("creating")
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
tblPNLog).CreatePivotTable TableDestination:=Worksheets("Summary").Range("A1"), TableName:= _
"StatsSummary", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("StatsSummary").AddFields RowFields:="Status", _
ColumnFields:="Priority"
ActiveSheet.PivotTables("StatsSummary").PivotFields("Priority").Orientation = _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = False
Else
MsgBox ("Refreshing")
Set pvtCache = ActiveWorkbook.PivotCaches(1)
pvtCache.Refresh
End If
Display More
checkCreateSheet just finds out if the Summary sheet still exists, and if not - recreates it.
tblPNLog will contain the current data range on the Detail worksheet.
Re: Using addins to package forms/code -newbie Q
Thanks, I'll give it a go.
I'm dipping in and out of VBA for Excel and have updated one of my projects to include a userform.
Until now, I've been importing the VBA module into a new workbook each time I use it (it processes some downloaded HTML tables) - but I could see that as the project grows, it would become a pain to import each form/module one at a time.
So - I stumbled across addins and 'succesfully' created one which included my current form and module - the question is:- how do I use it?
The addin loads into Excel on startup, and I can see the code and form in the VB editor - but the subs aren't visible in the main Excel Macro menu which is where I usually run the project from...
How do I make my code available? Do I need to make a button/menu or add it to the normal template?
I'm getting towards the end of a working day - so pre-paid apologies if I'm missing something obvious...
Re: Hiding subs - make them into functions?
Doh!
Answer staring me in the face... Make it Private... :thanx:
Back to school... I'm learning VBA for Excel mainly from web-resources and haven't found an answer to this simple problem...
I'm trying to tidy up my code - I want to hide certain subs from the list that appears in the standard Macro dialogue.
Other than turn them into functions, how do I do this?
Thanks, Myles
Re: User forms - Public vars or hidden controls to pass values
Aha!
Great - I'll give it a go.
Another active forum to keep an eye on, thanks.
Hi,
After building a basic knowledge of VBA in Excel '03, I'm moving on to incorporate some user forms.
I'm happy with extracting values of things like checkboxes, text boxes etc. back into my main code module - but I've hit a snag trying to pass other information back to my main code.
An example situation:
Suppose I have two command buttons which return control from the form to the main VBA code using a Hide command. How do I tell from the main code which was clicked?
The two ways I've found so far are:
i) To declare a public variable in the main code module and change that in the click events for the relevant buttons
ii) To create a hidden control in the form (e.g. a textbox or checkbox) and set a value for that in the form code (e.g. in a click event).
I don't particularly like option i) on the basis that declaring loads of public variables feels akin to relying on GoTo's in Basic programming of yore.
Option ii) seems like a bit of a cheat.
What is the recommended way? Being as I am early in the development of my VBA knowledge - it seems like a good time to get into a good style of writing code rather than to bodge everything together...
Thanks,
Myles