I have code that executes to stop updating workbook to increase the speed but occasionally when there is a crash or I have to end the code and this needs to be reverted back
Posts by browncoat
-
-
Try:
=[@Mean]-(SUM(SUMIFS([Mean],[35S],[@35S],[v.Con],"NSB",[D1],IF([@D1]="","",[@D1]))))
Awesome that works, thanks!
-
I have a table that needs to automatically make calculations based on matching values in different columns and have attached the table. In the last column labeled Spec Mean, I want to subtract the mean value from the rows labeled NSB based on matched values of other columns (e.g. v.Con column = "NSB", value in 35S column matches the value in the current row). So if in the D1 column the value is -4.5, then it should subtract the value in the mean column from the row with NSB in the v.Con column and the value of -4.5 in the D1 column. This works unless the D1 column is blank. It doesn't subtract the value from the row with D1 blank even though they are both blank. How can I get it to match a blank value for these?
This is the value in the column I am using to calculate
=[@Mean]-(SUM(SUMIFS([Mean],[35S],[@35S],[v.Con],"NSB",[D1],[@D1])))
Thanks!
-
That works thanks! Think I got hung up on finding a specific function rather than thinking outside the box.
-
It's an Excel table. I want to be able to refer to other cells in that cells row in the table using table.range.cells(x,y)
-
Seems like this should be a simple thing, and maybe it is, but I can't seem to find out how to do this. I want to keep track of which rows within the table certain cells are but I can't find a way to get the table row number rather than the worksheet row number.
-
YES!!! That works!!!! Thank you so much, you're awesome!!
-
OK, you can't just replace every Long with LongPtr. Only pointers and handles need to be LongPtr. I'll have a detailed look when I have time (or failing that I'll just ask Andy when I see him next week :))
Ah ok hopefully that's the problem. Thanks so much for your help! Crazy (or maybe not?) that you know the author! Small world??
Also, I'd tried it like this. It also doesn't give me errors but it didn't work which is why I tried changing all of them.
Code
Display MoreOption Explicit Private Type GUID Data1 As Long Data2 As Integer Data3 As Integer Data4(0 To 7) As Byte End Type Private Type PICTDESC Size As Long Type As Long hPic As Long hPal As Long End Type Private Type GdiplusStartupInput GdiplusVersion As Long DebugEventCallback As Long SuppressBackgroundThread As Long SuppressExternalCodecs As Long End Type Private Declare PtrSafe Function GdiplusStartup Lib "GDIPlus" (token As Long, _ inputbuf As GdiplusStartupInput, Optional ByVal outputbuf As Long = 0) As Long Private Declare PtrSafe Function GdipCreateBitmapFromFile Lib "GDIPlus" (ByVal filename As LongPtr, bitmap As Long) As Long Private Declare PtrSafe Function GdipCreateHBITMAPFromBitmap Lib "GDIPlus" (ByVal bitmap As Long, _ hbmReturn As Long, ByVal background As Long) As Long Private Declare PtrSafe Function GdipDisposeImage Lib "GDIPlus" (ByVal image As Long) As Long Private Declare PtrSafe Function GdiplusShutdown Lib "GDIPlus" (ByVal token As Long) As Long Private Declare PtrSafe Function OleCreatePictureIndirect Lib "olepro32.dll" (PicDesc As PICTDESC, _ RefIID As GUID, ByVal fPictureOwnsHandle As Long, IPic As IPicture) As Long Public Function LoadImage(ByVal strFName As String) As IPicture Dim uGdiInput As GdiplusStartupInput Dim hGdiPlus As Long Dim hGdiImage As Long Dim hBitmap As Long uGdiInput.GdiplusVersion = 1 If GdiplusStartup(hGdiPlus, uGdiInput) = 0 Then If GdipCreateBitmapFromFile(StrPtr(strFName), hGdiImage) = 0 Then GdipCreateHBITMAPFromBitmap hGdiImage, hBitmap, 0 Set LoadImage = ConvertToIPicture(hBitmap) GdipDisposeImage hGdiImage End If GdiplusShutdown hGdiPlus End If End Function Public Function ConvertToIPicture(ByVal hPic As Long) As IPicture Dim uPicInfo As PICTDESC Dim IID_IDispatch As GUID Dim IPic As IPicture Const PICTYPE_BITMAP = 1 With IID_IDispatch .Data1 = &H7BF80980 .Data2 = &HBF32 .Data3 = &H101A .Data4(0) = &H8B .Data4(1) = &HBB .Data4(2) = &H0 .Data4(3) = &HAA .Data4(4) = &H0 .Data4(5) = &H30 .Data4(6) = &HC .Data4(7) = &HAB End With With uPicInfo .Size = Len(uPicInfo) .Type = PICTYPE_BITMAP .hPic = hPic .hPal = 0 End With OleCreatePictureIndirect uPicInfo, IID_IDispatch, True, IPic Set ConvertToIPicture = IPic End Function -
UserForm1
Code
Display MoreOption Explicit Private Sub CommandButton1_Click() Dim vntFilename As Variant vntFilename = Application.GetOpenFilename("Images (*.png),*.png") If vntFilename = "False" Then Exit Sub Image1.Picture = LoadImage(vntFilename) End SubbasGDIPlus
Code
Display MoreOption Explicit Private Type GUID Data1 As LongPtr Data2 As Integer Data3 As Integer Data4(0 To 7) As Byte End Type Private Type PICTDESC Size As LongPtr Type As LongPtr hPic As LongPtr hPal As LongPtr End Type Private Type GdiplusStartupInput GdiplusVersion As LongPtr DebugEventCallback As LongPtr SuppressBackgroundThread As LongPtr SuppressExternalCodecs As LongPtr End Type Private Declare PtrSafe Function GdiplusStartup Lib "GDIPlus" (token As LongPtr, _ inputbuf As GdiplusStartupInput, Optional ByVal outputbuf As LongPtr = 0) As LongPtr Private Declare PtrSafe Function GdipCreateBitmapFromFile Lib "GDIPlus" (ByVal filename As LongPtr, bitmap As LongPtr) As LongPtr Private Declare PtrSafe Function GdipCreateHBITMAPFromBitmap Lib "GDIPlus" (ByVal bitmap As LongPtr, _ hbmReturn As LongPtr, ByVal background As LongPtr) As LongPtr Private Declare PtrSafe Function GdipDisposeImage Lib "GDIPlus" (ByVal image As LongPtr) As LongPtr Private Declare PtrSafe Function GdiplusShutdown Lib "GDIPlus" (ByVal token As LongPtr) As LongPtr Private Declare PtrSafe Function OleCreatePictureIndirect Lib "oleaut32.dll" (PicDesc As PICTDESC, _ RefIID As GUID, ByVal fPictureOwnsHandle As LongPtr, IPic As IPicture) As LongPtr Public Function LoadImage(ByVal strFName As String) As IPicture Dim uGdiInput As GdiplusStartupInput Dim hGdiPlus As LongPtr Dim hGdiImage As LongPtr Dim hBitmap As LongPtr uGdiInput.GdiplusVersion = 1 If GdiplusStartup(hGdiPlus, uGdiInput) = 0 Then If GdipCreateBitmapFromFile(StrPtr(strFName), hGdiImage) = 0 Then GdipCreateHBITMAPFromBitmap hGdiImage, hBitmap, 0 Set LoadImage = ConvertToIPicture(hBitmap) GdipDisposeImage hGdiImage End If GdiplusShutdown hGdiPlus End If End Function Public Function ConvertToIPicture(ByVal hPic As LongPtr) As IPicture Dim uPicInfo As PICTDESC Dim IID_IDispatch As GUID Dim IPic As IPicture Const PICTYPE_BITMAP = 1 With IID_IDispatch .Data1 = &H7BF80980 .Data2 = &HBF32 .Data3 = &H101A .Data4(0) = &H8B .Data4(1) = &HBB .Data4(2) = &H0 .Data4(3) = &HAA .Data4(4) = &H0 .Data4(5) = &H30 .Data4(6) = &HC .Data4(7) = &HAB End With With uPicInfo .Size = Len(uPicInfo) .Type = PICTYPE_BITMAP .hPic = hPic .hPal = 0 End With OleCreatePictureIndirect uPicInfo, IID_IDispatch, True, IPic Set ConvertToIPicture = IPic End FunctionModule1
CodeOption Explicit Sub Button1_Click() UserForm1.Show End Sub -
You're passing pointers/handles around, so they will need to be LongPtr to work on 64bit Office, not Long.
Hi Rory, thanks for responding. I have done that. As I mentioned in the first post, I replaced all to LongPtr and that resulted in a runtime error 54, file not found: olepro32.dll. A forum online said that for 64 bit I need replace that code and point to oleaut32.dll which I did. That resulted in no longer getting an error but the image still does not load.
-
No I have not gotten it to work. Adding PtrSafe just prevents me from getting errors when it runs. See my very first post about the changes I've made attempting to get it to work. It looks like GdiplusStartup is returning a bad value or no value at all (I don't see any value) as when I run it the part following is skipped:
Code
Display MorePublic Function LoadImage(ByVal strFName As String) As IPicture Dim uGdiInput As GdiplusStartupInput Dim hGdiPlus As Long Dim hGdiImage As Long Dim hBitmap As Long uGdiInput.GdiplusVersion = 1 If GdiplusStartup(hGdiPlus, uGdiInput) = 0 Then ' this = false so function ends If GdipCreateBitmapFromFile(StrPtr(strFName), hGdiImage) = 0 Then GdipCreateHBITMAPFromBitmap hGdiImage, hBitmap, 0 Set LoadImage = ConvertToIPicture(hBitmap) GdipDisposeImage hGdiImage End If GdiplusShutdown hGdiPlus End If End Function -
When you run the example workbook from that thread, do you not get errors from the declared functions? It will not run without me changing those to PtrSafe
-
Yes it downloads fine and other png files do not work. I'm wondering if it has to do with GDPplus. I've also posted a thread here:
-
This is the image. I have a function that automatically downloads from the API to a temp folder on my harddrive:
-
That's the thread I linked to. It does not work for me
-
Been scouring for hours and have only come across a few threads discussing converting PNG files so they are compatible in userforms but none of them work, possibly because I'm in 64 bit Excel. This one I've gotten to actually not give errors but it still doesn't work: https://www.excelforum.com/exc…-image-into-userform.html . I converted Long to LongPtr and replaced olepro32.dll with oleaut32.dll which was suggested for 64 bit Excel but still no luck despite no longer getting errors. No images will load.
Wondering if this is a lost cause at this point. Unfortunately it has to be PNG because the API only uses PNG files. All help appreciated.
-
Used accelerator parameter of a hidden control button to accomplish it..
-
I'd like to be able to have input from keyboard be utilized when the userform is open, regardless of what is active. I've tried the UserForm_KeyDown event but it's not getting picked up when I press a key. Is there a way to do this?
-
Nevermind I'm an idiot
-
I have the bound column and text column set correctly and it works fine when clicking the box and selecting a value, however I have a textbox where the user can manually enter the record ID and I'd like for the combobox to list that selected record. I am setting the .value to the bound column value, and this works, but the combobox is showing the number, not displaying the text column.